Using Nested Models in SqlModelMapper (multi level cascading for dependent models)

Last Updated: September 2022

Introduction

This document describes how to use nested Models with SqlModelMapper. SnapObjects provides an object (SqlModelMapper) that simplifies the usage of nested models in multiple cascading dependent levels. SnapObjects provides APIs to manage the operations performed on these nested models.

Nested models can represent complex relationships, such as master-detail-detail. The master-detail relationship can be of cardinality types: one-to-one and one-to-many. The master-detail-detail relationship can be of cardinality types: one-to-one-to-one and one-to-one-to-many. However, under certain circumstances, you can also manage n-Level relationships for single SQL Insert, Update or Delete operations.

Note: The code blocks that appear in the current document are only used to demonstrate features, and the corresponding database is not provided, so it cannot be run directly.

The SQL CRUD of a model is generated based on various attributes. From these, there are a set of attributes that you can apply in order to set nested models in a master-detail or master-detail-detail relationship.

ModelEmbedded

The ModelEmbedded attribute specifies that a property, in a model class, will use another specified model to load and update data. This attribute will map a property to another model, thus nesting the models. You can add as many nested models as you need. However, only one model per property can be mapped.

ModelEmbedded Attribute Properties:

ModelType

// Set this property in order to specify the nested model
// [ModelEmbedded(Type modelType)]
// ModelType is the actual model class that will be mapped
[ModelEmbedded(typeof(SalesOrder))]

CascadeCreate

// Set this property as true in order to perform the insert operation for the current property 
// (using definitions of the detail model) when the current model is inserting data.
[ModelEmbedded(typeof(SalesOrder), CascadeCreate = true)]

CascadeDelete

// Set this property as true in order to perform the delete operation for the current property 
// (using definitions of the detail model) when the current model is deleting data.
[ModelEmbedded(typeof(SalesOrder), CascadeDelete = true)]

ParamValue

// Set this property to specify the value of the parameter(s) from the nested model
//
// In this example, "$Id" is a property of the master model and ":param1" is
// a SQL parameter of the master model set with [SqlParameter("param1", typeof(Int32))]
[ModelEmbedded(typeof(SalesOrder), ParamValue = "$Id, :param1")]

QueryExpression

// Set this property to specify a query different than the one set in the detail model
// [ModelEmbedded(typeof(ModelClass), QueryExpression = "Raw SQL goes here")]
[ModelEmbedded(typeof(SalesOrder), QueryExpression = "SELECT Id, Cust_Id, GetDate() as Order_Date, Fin_Code_Id, Region, Sales_Rep FROM sales_order ORDER BY Cust_Id")]

QuerySelector

// Set this property to specify the query method to be used when loading data for the
// current property. (QuerySelector.Select is the default value)
[ModelEmbedded(typeof(SalesOrder), QuerySelector = QuerySelector.SelectByKey)]

SetValue

This attribute maps a property from the master model to the nested model. It specifies that when there is an insert or update operation in the master model, it will set the value from the property of the master model to the associated property of the detail model, when working with the master-detail models. One nested model property is allowed to apply multiple SetValue attributes.

SetValue Attribute Properties:

Source

// Set this required property to specify the source value from the master model
// Use this format to make reference: $PropertyName, for example, $Id
[SetValue(source:"$Id", target:"$Id")]

Target

// Set this required property to specify the target value from the nested model
// Use this format to make reference: $PropertyName, for example, $Id
[SetValue(source:"$Id", target:"$Id")]

SetValueStrategy

// Set this property to specify the timing for the SetValue attribute to set values
// [SetValue(source, target, SetValueStrategy.member)]
// Members are: Always, AfterParentCreated and AfterParentUpdated
[SetValue("$Id", "$Id", SetValueStrategy.Always)]

ModelSelector

// Set this property to set the value by the data state in the embedded model
// [SetValue(source, target, SetValueStrategy.member, ModelSelector.member)]
// Members are: All, ChangedOnly, UnchangedOnly
[SetValue("$Id", "$Id", SetValueStrategy.Always, ModelSelector.All)]

SqlEmbedded

The SqlEmbedded attribute specifies that a property of the model class will use the specified raw SQL SELECT statement to load data.

SqlEmbedded Attribute Properties:

RawSql

// Use this property to set the raw SQL SELECT statement that will be used 
// to load data for the current property
// [SqlEmbedded(string rawSql)]
[SqlEmbedded("SELECT Id, Cust_Id, Order_Date, Fin_Code_Id, Region, Sales_Rep FROM sales_order ORDER BY Cust_Id")]

ParamValue

// Set this property to specify the value of the parameter(s) of the raw SQL SELECT
// [SqlEmbedded((string rawSql), ParamValue = (string paramList))]
// 
// In this example, "$Id" is a property of the master model and ":param1" is
// a SQL parameter of the master model set with [SqlParameter("param1", typeof(Int32))]
[SqlEmbedded("SELECT * FROM sales_order WHERE Id = @Id and Cust_Id = @param1", ParamValue = "$Id, :param1")]

Creating a master-detail nested model

There are 2 ways to create this type of relationship: using ModelEmbedded attribute or the SqlEmbedded attribute. If using the ModelEmbedded attribute, it enables you to have a better control of the updates done to the detail model. While using SqlEmbedded is used only to load data to the detail model.

Option 1: Create a master-detail nested model with ModelEmbedded attribute

Step 1: Create the master model (CustomerMaster)

Note that the model (CustomerMaster) currently being created depends on the model (SalesOrderDetail) that will be created in the next step. You can ignore the hint if SalesOrderDetail is not existing and go to the next step.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using Newtonsoft.Json;
using SnapObjects.Data;

namespace NestedModels.Models
{
    // Set the SQL Query applying attributes to the model
    [FromTable("Customer", Schema = "dbo")]
    [SqlOrderBy("Id")]
    public class CustomerMaster
    {
        // Set validation rules to the properties    
        [Key]
        public Int32 Id { get; set; }

        [MaxLength(15)]
        public String Fname { get; set; }

        [MaxLength(20)]
        public String Lname { get; set; }

        [MaxLength(35)]
        public String Company_Name { get; set; }

        [JsonIgnore]
        // Set the nested model relationship (one-to-many)
        [SetValue("$Id", "$Cust_Id", SetValueStrategy.Always)]
        [ModelEmbedded(typeof(SalesOrderDetail), ParamValue = "$Id", CascadeCreate = true, CascadeDelete = true)]
        public IList<SalesOrderDetail> SalesOrder { get; set; }
    }
}

Step 2: Create the detail model (SalesOrderDetail)

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using SnapObjects.Data;

namespace NestedModels.Models
{
    // Set the SQL Query applying attributes to the model
    [SqlParameter("Cust_Id", typeof(Int32))]
    [Table("Sales_Order", Schema = "dbo")]
    [SqlWhere("Cust_Id = :Cust_Id")]
    [SqlOrderBy("Id")]
    public class SalesOrderDetail
    {
        // Set validation rules to the properties
        [Key]
        public Int32 Id { get; set; }

        [Required]
        public Int32 Cust_Id { get; set; }

        public DateTime Order_Date { get; set; }

        [MaxLength(2)]
        public String Fin_Code_Id { get; set; }

        [MaxLength(7)]
        public String Region { get; set; }

        public Int32 Sales_Rep { get; set; }
    }
}

Option 2: Create a master-detail nested model with SqlEmbedded attribute

(For this example we will reuse the SalesOrderDetail model from Option 1)

Step 1: Create the master dodel (CustomerMasterSqlEmbedded)

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using Newtonsoft.Json;
using SnapObjects.Data;

namespace NestedModels.Models
{
    // Set the SQL Query applying attributes to the model
    [FromTable("Customer", Schema = "dbo")]
    [SqlOrderBy("Id")]
    public class CustomerMasterSqlEmbedded
    {
        // Set validation rules to the properties
        [Key]
        public Int32 Id { get; set; }

        [MaxLength(15)]
        public String Fname { get; set; }

        [MaxLength(20)]
        public String Lname { get; set; }

        [MaxLength(35)]
        public String Company_Name { get; set; }

        [JsonIgnore]
        // Set the nested model relationship (one-to-many)
        [SetValue("$Id", "$Cust_Id", SetValueStrategy.Always)]
        [SqlEmbedded("SELECT * FROM SALES_ORDER WHERE Cust_Id = @Id", ParamValue = "$Id")]
        public IList<SalesOrderDetail> SalesOrder { get; set; }
    }
}

Step 2: Reuse the detail model from option 1 (SalesOrderDetail)

Creating a master-detail-detail nested model

To create this type of relationship you use the ModelEmbedded attribute. The master-detail-detail nested model supports one-to-one-to-many data relationships for SQL Insert/Update/Delete operations. However, you can set any type of cardinality if you only want to load data into the models.

Step 1: Create the master model (CustomerMaster3Level)

Note that the model (CustomerMaster3Level) currently being created depends on the model (SalesOrderDetail3Level) that will be created in the next step. You can ignore the hint if SalesOrderDetail3Level is not existing and go to the next step.

using System;
using System.ComponentModel.DataAnnotations;
using Newtonsoft.Json;
using SnapObjects.Data;

namespace NestedModels.Models
{
    // Set the SQL Query applying attributes to the model
    [FromTable("Customer", Schema = "dbo")]
    [SqlOrderBy("Id")]
    public class CustomerMaster3Level
    {
        // Set validation rules to the properties
        [Key]
        public Int32 Id { get; set; }

        [MaxLength(15)]
        public String Fname { get; set; }

        [MaxLength(20)]
        public String Lname { get; set; }

        [MaxLength(35)]
        public String Company_Name { get; set; }

        [JsonIgnore]
        // Set the nested model relationship (one-to-one)
        [SetValue("$Id", "$Cust_Id", SetValueStrategy.Always)]
        [ModelEmbedded(typeof(SalesOrderDetail3Level), ParamValue = "$Id", CascadeCreate = true, CascadeDelete = true)]
        public SalesOrderDetail3Level SalesOrder { get; set; }
    }
}

Step 2: Create the detail model (SalesOrderDetail3Level)

Note that the model (SalesOrderDetail3Level) currently being created depends on the model (SalesOrderItemsGrandDetail) that will be created in the next step. You can ignore the hint if SalesOrderItemsGrandDetail is not existing and go to the next step to create it.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Newtonsoft.Json;
using SnapObjects.Data;

namespace NestedModels.Models
{
    // Set the SQL Query applying attributes to the model
    [SqlParameter("Cust_Id", typeof(Int32))]
    [Table("Sales_Order", Schema = "dbo")]
    [SqlWhere("Cust_Id = :Cust_Id")]
    [SqlOrderBy("Id")]
    public class SalesOrderDetail3Level
    {
        // Set validation rules to the properties
        [Key]
        public Int32 Id { get; set; }

        [Required]
        public Int32 Cust_Id { get; set; }

        public DateTime Order_Date { get; set; }

        [MaxLength(2)]
        public String Fin_Code_Id { get; set; }

        [MaxLength(7)]
        public String Region { get; set; }

        public Int32 Sales_Rep { get; set; }

        [JsonIgnore]
        // Set the nested model relationship (one-to-many)
        [SetValue("$Id", "$Id", SetValueStrategy.Always)]
        [ModelEmbedded(typeof(SalesOrderItemsGrandDetail), ParamValue = "$Id, $Cust_Id", 
            CascadeCreate = true, CascadeDelete = true)]
        public IList<SalesOrderItemsGrandDetail> SalesOrderItems { get; set; }
    }
}

Step 3: Create the detail-detail model (SalesOrderItemsGrandDetail)

using System;
using System.ComponentModel.DataAnnotations;
using SnapObjects.Data;

namespace NestedModels.Models
{
    // Set the SQL Query applying attributes to the model
    [SqlParameter("orderId", typeof(Int32))]
    [SqlParameter("custId", typeof(Int32))]
    [FromTable("Sales_Order_Items", Schema = "dbo")]
    [JoinTable("sales_order", JoinType = SqlJoinType.Join, OnRaw = "sales_order.id = sales_order_items.id", Schema = "dbo")]
    [JoinTable("customer", JoinType = SqlJoinType.Join, OnRaw = "sales_order.cust_id = customer.id", Schema = "dbo")]
    [SqlWhere("customer.Id = :custId")]
    [SqlAndWhere("sales_order.Id = :orderId")]
    [SqlOrderBy("Id ASC, Line_Id ASC")]
    public class SalesOrderItemsGrandDetail
    {
        // Set validation rules to the properties
        [Key]
        [SqlColumn("id")]
        public Int32 Id { get; set; }

        [Key]
        [SqlColumn("line_id")]
        public Int32 Line_Id { get; set; }

        [SqlColumn("prod_id")]
        public Int32 Prod_Id { get; set; }

        [SqlColumn("quantity")]
        public Int32 Quantity { get; set; }

        [SqlColumn("ship_date")]
        public DateTime Ship_Date { get; set; }
    }
}

Handle master-detail CRUD operations with nested model

Master-detail models can be used to load, track and cache data state changes in the models. This management can then be used to apply the changes into the DB.

How does it work?

Whenever you execute the SqlModelMapper's Load() methods, you can decide whether you want to load only the master model or also its nested models. If a master model has more than one nested model, you can decide to load all or a specific nested model that you select.

You can also track the insert, update or delete operations on the table which the master, detail or granddetail models are mapped to. In order to accomplish this, the ModelEmbedded attribute must have been applied to a property of the master model. To perform the tracking operations on the models, you use the TrackMaster(), TrackDetail(), TrackDetails(), TrackGrandDetail() and TrackGrandDetails() methods of the SqlModelMapper.

Load nested model

Basic examples on loading master-detail nested models (pseudo code):

Example 1:

public IList<CustomerMaster> GetMasterDetailList()
{
    // Load the CustomerMaster model and include all the detail nested models
    return _context.SqlModelMapper.Load<CustomerMaster>()
                                  .IncludeAll()
                                  .ToList();
}

Example 2:

public IList<CustomerMaster> GetCustomerOrdersList(int custId)
{
    // Load the CustomerMaster model by passing in a parameter and include 
    // only the nested model that is associated to the SalesOrder property
    return _context.SqlModelMapper.Load<CustomerMaster>(custId)
                                  .Include(o => o.SalesOrder)
                                  .ToList();
}

Example 3:

public CustomerMaster GetEmbedded(int custId)
{
    // Create a local variable for the SqlModelMapper object
    var mapper = _context.SqlModelMapper;

    // Load the model into a local variable
    var customer = mapper.LoadByKey<CustomerMaster>(custId).FirstOrDefault();

    /*** Do some processing here***/
    // ...
    // ...

    // Load the SalesOrder detail nested model
    mapper.LoadEmbedded<CustomerMaster>(customer, custId).Include(m => m.SalesOrder);

    // Return the CustomerMaster model (with nested models in it)
    return customer;
}

Basic examples on loading master-detail-detail nested models (pseudo code):

Example 1:

public IList<CustomerMaster3Level> GetAllList(int custId)
{
    // Load the CustomerMaster model by passing in a parameter and 
    // include the nested model (detail) as well as the specified 
    // nested model underneath it (granddetail)
    return _context.SqlModelMapper.LoadByKey<CustomerMaster3Level>(custId)
                                  .IncludeAll(true)
                                  .ToList();
}

Example 2:

public IList<CustomerMaster3Level> GetFullList(int custId)
{
    // Load the CustomerMaster model by passing in a parameter and 
    // include the nested model (detail) as well as the specified 
    // nested model underneath it (granddetail)
    return _context.SqlModelMapper.LoadByKey<CustomerMaster3Level>(custId)
                                  .Include(o => o.SalesOrder, true)
                                  .ToList();
}

Example 3:

public CustomerMaster3Level GetEmbeddedAll(int custId)
{
    // Create a local variable for the SqlModelMapper object
    var mapper = _context.SqlModelMapper;

    // Load the model into a local CustomerMaster3Level
    var customer = mapper.LoadByKey<CustomerMaster3Level>(custId).FirstOrDefault();

    /*** Do some processing here***/
    // ...
    // ...

    // Load the detail and granddetail nested models
    mapper.LoadEmbedded<CustomerMaster3Level>(customer, custId).IncludeAll(true);

    // Return the CustomerMaster model (with all the nested models within)
    return customer;
}

Tracking database table operations

Master-detail track

To track the changes of a master-detail model along with its nested model(s), you use the Track() methods of the SqlModelMapper. You can also use the Track() methods to track the changes on a master-detail-detail relationship. But, if data status is being managed on the models (IModelEntry), only 3 levels are supported (Master-Detail-Detail). ModelEntry objects require a specific JSON format. Except for the JSON format that we have extended in SnapObjects, users can also customize a class to implement the IModelEntry interface, using its own unique data format.

Master-detail:

// Receive Model Entries (rows with status) to be used on the TrackMaster()
// and the TrackDetail() methods
public int SaveOrders(IModelEntry<CustomerMaster> header, IEnumerable<IModelEntry<SalesOrderDetail>> details)
{
    // First track any changes on the master model using the data state.
    // Then track the changes on the detail model using the data state.
    // Save the changes in one single transaction and return the affected count.
    return _context.SqlModelMapper.TrackMaster<CustomerMaster>(header)
                                  .TrackDetails<SalesOrderDetail>(m => m.SalesOrder, details)
                                  .SaveChanges()
                                  .AffectedCount;
}

Master-detail-detail:

// Receive model entries (rows with status) to be used on the TrackMaster(),
// TrackDetail() and TrackGrandDetails() methods
public int SaveChanges(IModelEntry<CustomerMaster3Level> header,
                        IModelEntry<SalesOrderDetail3Level> detail,
                        IEnumerable<IModelEntry<SalesOrderItemsGrandDetail>> grandDetails)
{
    // First track any changes on the master model using the data state.
    // Then track the changes on the detail model using the data state.
    // Also track the changes on the GrandDetail model by mapping it to the master-detail.
    // Save the changes in one single transaction and return the affected count.
    return _context.SqlModelMapper.TrackMaster(header)
                                    .TrackDetail(m => m.SalesOrder, detail)
                                    .TrackGrandDetails<SalesOrderDetail3Level, SalesOrderItemsGrandDetail>
                                    (
                                        n => n.SalesOrder,
                                        o => o.SalesOrderItems,
                                        grandDetails
                                    )
                                    .SaveChanges()
                                    .AffectedCount;
}

Without using TrackMaster() or TrackDetail()

If you want to track more than 3 levels of nested models without using ModelEntries (data status), you can track the SQL Insert or Delete operations using the corresponding TrackCreate() or TrackDelete() methods of the SqlModelMapper. See Option 1 for the declaration of the master model used in the following examples:

TrackCreate() (Insert one row at a time of the master model and cascade down to the embedded model):

public int AddEmbedded()
{
    // Instantiate a new CustomerMaster model with its nested model
    CustomerMaster customer = new CustomerMaster()
    {
        Id = 1011,
        Fname = "Govinda",
        Lname = "Lopez",
        Company_Name = "Appeon Inc."
    };

    // Include the Nested Model
    customer.SalesOrder =
        new List<SalesOrderDetail>
        {
            new SalesOrderDetail()
            {
                Id = 3011,
                Cust_Id = 1011,
                Order_Date = DateTime.Now,
                Fin_Code_Id = "r1",
                Region = "Eastern",
                Sales_Rep = 299
            }
        };

    // Track the SQL Insert operations of the master and detail models.
    // Note that the passed in master model must include the embedded model 
    // (usually in JSON format).
    // Save the changes in one transaction and return the inserted count.
    return _context.SqlModelMapper.TrackCreate<CustomerMaster>(customer)
                                  .SaveChanges()
                                  .InsertedCount;
}

TrackCreateRange() (Insert multiple rows at a time of the master model and cascade down to the embedded model):

public int AddEmbeddedAll(IList<CustomerMaster> customers)
{
    // Track the SQL Insert operations of the master and detail models.
    // Note that the passed in master model must include the embedded model
    // (usually in JSON format).
    // Save the changes in one transaction and return the inserted count.
    return _context.SqlModelMapper.TrackCreateRange<CustomerMaster>(customers)
                                  .SaveChanges()
                                  .InsertedCount;
}

TrackDelete() (Delete one row at a time of the master model and cascade down to the embedded model):

public int DeleteEmbedded()
{
    // Instantiate a new CustomerMaster Model with its nested model.
    // The data in this model must match existing values.
    CustomerMaster customer = new CustomerMaster()
    {
        Id = 1010,
        Fname = "Govinda",
        Lname = "Lopez",
        Company_Name = "Appeon Inc."
    };

    customer.SalesOrder =
        new List<SalesOrderDetail>
        {
            new SalesOrderDetail()
            {
                Id = 3010,
                Cust_Id = 1010,
                Order_Date = System.DateTime.Now,
                Fin_Code_Id = "r1",
                Region = "Eastern",
                Sales_Rep = 299
            }
        };

    // Track the SQL Delete operations of the master and detail models.
    // Then save the changes in one transaction and return the deleted count.
    // Note that the master model declaration must include the CascadeDelete = true
    // property. 
    return _context.SqlModelMapper.TrackDelete<CustomerMaster>(customer)
                                  .SaveChanges()
                                  .DeletedCount;
}

TrackDeleteRange() (Delete multiple rows at a time of the master model and cascade down to the embedded model):

public int DeleteAllEmbedded(IList<CustomerMaster> customers)
{
    // Track the SQL Delete operations of the master and detail models.
    // Then save the changes in one transaction and return the deleted count.
    // Note that the master model declaration must include the CascadeDelete = true
    // property and that the passed in master model must include the embedded model
    // (usually in JSON format).
    return _context.SqlModelMapper.TrackDeleteRange<CustomerMaster>(customers)
                                  .SaveChanges()
                                  .DeletedCount;
}

TrackDeleteByKey() (Delete one row at a time of the master model and cascade down to the embedded model):

public int DeleteEmbeddedById(int custId)
{
    // Track the SQL Delete operations of the master and detail models by passing in the key.
    // Then save the changes in one transaction and return the deleted count.
    // Note that the master model declaration must include the CascadeDelete = true
    // property.
    return _context.SqlModelMapper.TrackDeleteByKey<CustomerMaster>(custId)
                                  .SaveChanges()
                                  .DeletedCount;
}

Handle master-detail without using nested models

You can also track more than 3 levels of cascading dependent levels without using nested models or model entries (data status). To do that, you can track the SQL Insert, Update or Delete operations using the corresponding TrackCreate(), TrackUpdate() or TrackDelete() methods of the SqlModelMapper.

This approach is best when used with the standard implementation of the REST Web API. In this type of approach you can either Insert, Update or Delete row-by-row or multiple rows at a time as long as only one type of SQL CUD operation is performend on the same data set.

TrackCreate() (Insert one row at a time):

public int InsertMasterDetail(CustomerMaster header, SalesOrderDetail detail)
{
    // Track the SQL Insert of the CustomerMaster model
    _context.SqlModelMapper.TrackCreate<CustomerMaster>(header);

    // Track the SQL Insert of the SalesOrderDetail model
    _context.SqlModelMapper.TrackCreate<SalesOrderDetail>(detail);

    /*****Add more tracked models as needed*****/
    // ...
    // ...

    // Save the changes in one transaction and return the inserted count
    return _context.SqlModelMapper.SaveChanges().InsertedCount;
}

TrackCreateRange() (Insert multiple rows at a time):

public int InsertMasterDetails(IList<CustomerMaster> headers, IList<SalesOrderDetail> details)
{
    // Track the SQL Insert of the rows from the CustomerMaster model
    _context.SqlModelMapper.TrackCreateRange<CustomerMaster>(headers);

    // Track the SQL Insert of the rows from the SalesOrderDetail model
    _context.SqlModelMapper.TrackCreateRange<SalesOrderDetail>(details);

    /*****Add more tracked models as needed*****/
    // ...
    // ...

    // Save the changes in one transaction and return the inserted count
    return _context.SqlModelMapper.SaveChanges().InsertedCount;
}

TrackUpdate() (Update one row at a time):

public int UpdateMasterDetail(CustomerMaster header, SalesOrderDetail detail)
{
    // Load the master and detail models to get the original status of the rows
    CustomerMaster originalHeader = _context.SqlModelMapper.LoadByKey<CustomerMaster>(header.Id).FirstOrDefault();
    SalesOrderDetail originalDetail = _context.SqlModelMapper.LoadByKey<SalesOrderDetail>(detail.Id).FirstOrDefault();

    // Track SQL Update operations of the CustomerMaster model 
    // by comparing the original values against the changed values
    _context.SqlModelMapper.TrackUpdate<CustomerMaster>(originalHeader, header);

    // Track SQL Update operations of the CustomerMaster model 
    // by comparing the original values against the changed values
    _context.SqlModelMapper.TrackUpdate<SalesOrderDetail>(originalDetail, detail);

    /*****Add more tracked models as needed*****/
    // ...
    // ...

    // Save the changes in one transaction and return the Modified count
    return _context.SqlModelMapper.SaveChanges().ModifiedCount;
}

TrackUpdateRange (Update multiple rows at a time):

public int UpdateMasterDetails(IList<CustomerMaster> header, IList<SalesOrderDetail> details)
{
    // Create an empty CustomerMaster model variable
    IList<CustomerMaster> customers = new List<CustomerMaster>();

    // Create an empty SalesOrderDetail model variable
    IList<SalesOrderDetail> orderDetails = new List<SalesOrderDetail>();

    // Load the master model with the original status of each row
    foreach (var item in header)
    {
        customers.Add(_context.SqlModelMapper.LoadByKey<CustomerMaster>(item.Id).FirstOrDefault());
    }

    // Load the details model with the original status of each row
    foreach (var item in details)
    {
        orderDetails.Add(_context.SqlModelMapper.LoadByKey<SalesOrderDetail>(item.Id).FirstOrDefault());
    }

    // Track the changes that will be applied to the master and detail models
    _context.SqlModelMapper.TrackUpdateRange<CustomerMaster>(customers)
                            .TrackUpdateRange<SalesOrderDetail>(orderDetails);

    // Create a counter variable
    var counter = 0;

    // Apply the changes to the master model
    foreach (var item in header)
    {
        customers[counter].Id = item.Id;
        customers[counter].Fname = item.Fname;
        customers[counter].Lname = item.Lname;
        customers[counter].Company_Name = item.Company_Name;

        counter++;
    }

    // Reset the counter variable
    counter = 0;

    // Apply the changes to the master model
    foreach (var item in details)
    {
        orderDetails[counter].Id = item.Id;
        orderDetails[counter].Cust_Id = item.Cust_Id;
        orderDetails[counter].Sales_Rep = item.Sales_Rep;
        orderDetails[counter].Fin_Code_Id = item.Fin_Code_Id;
        orderDetails[counter].Order_Date = item.Order_Date;
        orderDetails[counter].Region = item.Region;

        counter++;
    }

    // Save the changes in one transaction and return the modified count
    return _context.SqlModelMapper.SaveChanges().ModifiedCount;
}

TrackDelete() (Delete one row at a time)

public int DeleteMasterDetail(CustomerMaster header, SalesOrderDetail detail)
{
    // Track the deletion of the models, save the changes in one transaction
    // and return the deleted count
    return _context.SqlModelMapper.TrackDelete<CustomerMaster>(header)
                                  .TrackDelete<SalesOrderDetail>(detail)
                                  .SaveChanges()
                                  .DeletedCount;
}

TrackDeleteRange() (Delete multiple rows at a time)

public int DeleteMasterDetails(IList<CustomerMaster> header, IList<SalesOrderDetail> details)
{
    // Track the deletion of the models, save the changes in one transaction
    // and return the deleted count
    return _context.SqlModelMapper.TrackDeleteRange<CustomerMaster>(header)
                                  .TrackDeleteRange<SalesOrderDetail>(details)
                                  .SaveChanges()
                                  .DeletedCount;
}

Using Nested Models in SqlModelMapper (multi level cascading for dependent models)

Introduction

Nested model related attributes

ModelEmbedded

ModelType

CascadeCreate

CascadeDelete

ParamValue

QueryExpression

QuerySelector

SetValue

Source

Target

SetValueStrategy

ModelSelector

SqlEmbedded

RawSql

ParamValue

Creating a master-detail nested model

Option 1: Create a master-detail nested model with ModelEmbedded attribute

Option 2: Create a master-detail nested model with SqlEmbedded attribute

Creating a master-detail-detail nested model

Step 1: Create the master model (CustomerMaster3Level)

Step 2: Create the detail model (SalesOrderDetail3Level)

Step 3: Create the detail-detail model (SalesOrderItemsGrandDetail)

Handle master-detail CRUD operations with nested model

How does it work?

Load nested model

Tracking database table operations

Master-detail track

Without using TrackMaster() or TrackDetail()

Handle master-detail without using nested models