CRUD Operations on Model with Child Records

This scenario demonstrates performing Create, Read, Update, and Delete operations on an object that includes a collection of child records.

Scenario Prototype

public interface IProductLine<TProduct>
    where TProduct : IProduct
{
    int ProductLineKey { get; set; }

    string? ProductLineName { get; set; }

    ICollection<TProduct> Products { get; }
}

public interface IModelWithChildrenScenario<TProductLine, TProduct>
   where TProductLine : class, IProductLine<TProduct>, new()
   where TProduct : class, IProduct, new()
{
    /// <summary>
    /// Create a new ProductLine row, returning the new primary key.
    /// </summary>
    /// <remarks>This MUST save any attached Product records.</remarks>
    int Create(TProductLine productLine);

    /// <summary>
    /// Delete a ProductLine row using an object.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined. This MUST delete any orphaned Product records.</remarks>
    void Delete(TProductLine productLine);

    /// <summary>
    /// Delete a ProductLine row using a key.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined. This MUST delete any orphaned Product records.</remarks>
    void DeleteByKey(int productLineKey);

    /// <summary>
    /// Get a list of product lines by name.
    /// </summary>
    /// <param name="productLineName">Name of the product line. This is not unique.</param>
    /// <param name="includeProducts">if set to <c>true</c> include Product records.</param>
    IList<TProductLine> FindByName(string productLineName, bool includeProducts);

    /// <summary>
    /// Gets all product lines.
    /// </summary>
    IList<TProductLine> GetAll(bool includeProducts);

    /// <summary>
    /// Gets an TProductLine row by its primary key.
    /// </summary>
    /// <param name="employeeKey">The employee key.</param>
    /// <param name="includeChildern">if set to <c>true</c> include Product records.</param>
    TProductLine? GetByKey(int productLineKey, bool includeProducts);

    /// <summary>
    /// Update a ProductLine row only.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined. This MUST not save any attached Product records.</remarks>
    void Update(TProductLine productLine);

    /// <summary>
    /// Update a ProductLine row and all of its children rows. If any product rows were removed from the Products collection, they should be ignored.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined. This MUST save any attached Product records. It MUST NOT delete any Product records that were removed from the collection.</remarks>
    void UpdateGraph(TProductLine productLine);

    /// <summary>
    /// Update a ProductLine row and all of its children rows. If any product rows were removed from the Products collection, they should be deleted.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined. This MUST save any attached Product records. It MUST delete any Product records that were removed from the collection.</remarks>
    void UpdateGraphWithChildDeletes(TProductLine productLine);

    /// <summary>
    /// Update a ProductLine row and all of its children rows. If any product rows were removed from the Products collection, they should be ignored. Delete any product rows in the productKeysToRemove list.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined. Behavior when a row in productKeysToRemove wasn't part of the original ProductLine is not defined.</remarks>
    void UpdateGraphWithDeletes(TProductLine productLine, IList<int> productKeysToRemove);

    /// <summary>
    /// Update a Product row.
    /// </summary>
    /// <param name="product">The product.</param>
    /// <remarks>Behavior when row doesn't exist is not defined.</remarks>
    void Update(TProduct product);
}

ADO.NET

    public class ModelWithChildrenScenario : SqlServerScenarioBase, IModelWithChildrenScenario<ProductLine, Product>
    {
        public ModelWithChildrenScenario(string connectionString) : base(connectionString)
        { }

        public int Create(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

            const string sql = "INSERT INTO Production.ProductLine ( ProductLineName ) OUTPUT Inserted.ProductLineKey VALUES (@ProductLineName);";

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                using (var cmd = new SqlCommand(sql, con, trans))
                {
                    cmd.Parameters.AddWithValue("@ProductLineName", productLine.ProductLineName);
                    productLine.ProductLineKey = (int)cmd.ExecuteScalar();
                    productLine.ApplyKeys();
                }

                foreach (var item in productLine.Products)
                    InsertProduct(con, trans, item);

                trans.Commit();
            }
            return productLine.ProductLineKey;
        }

        public void Delete(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

            const string sql = @"DELETE FROM Production.Product WHERE ProductLineKey = @ProductLineKey;
DELETE FROM Production.ProductLine WHERE ProductLineKey = @ProductLineKey";

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@ProductLineKey", productLine.ProductLineKey);
                cmd.ExecuteNonQuery();
            }
        }

        public void DeleteByKey(int productLineKey)
        {
            const string sql = @"DELETE FROM Production.Product WHERE ProductLineKey = @ProductLineKey;
DELETE FROM Production.ProductLine WHERE ProductLineKey = @ProductLineKey";

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@ProductLineKey", productLineKey);
                cmd.ExecuteNonQuery();
            }
        }

        public IList<ProductLine> FindByName(string productLineName, bool includeProducts)
        {
            const string sqlA = @"SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineName = @ProductLineName;
SELECT p.ProductKey, p.ProductName, p.ProductLineKey, p.ShippingWeight, p.ProductWeight FROM Production.Product p INNER JOIN Production.ProductLine pl ON p.ProductLineKey = pl.ProductLineKey WHERE pl.ProductLineName = @ProductLineName;";

            const string sqlB = @"SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineName = @ProductLineName;";

            var sql = includeProducts ? sqlA : sqlB;
            var results = new List<ProductLine>();

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@ProductLineName", productLineName);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        results.Add(new ProductLine(reader));
                    }

                    if (includeProducts)
                    {
                        var lookup = results.ToDictionary(x => x.ProductLineKey);
                        reader.NextResult();
                        while (reader.Read())
                        {
                            var product = new Product(reader);
                            lookup[product.ProductLineKey].Products.Add(product);
                        }
                    }
                }
            }

            return results;
        }

        public IList<ProductLine> GetAll(bool includeProducts)
        {
            const string sqlA = @"SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl;
SELECT p.ProductKey, p.ProductName, p.ProductLineKey, p.ShippingWeight, p.ProductWeight FROM Production.Product p INNER JOIN Production.ProductLine pl ON p.ProductLineKey = pl.ProductLineKey;";

            const string sqlB = @"SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl;";

            var sql = includeProducts ? sqlA : sqlB;
            var results = new List<ProductLine>();

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                    results.Add(new ProductLine(reader));

                if (includeProducts)
                {
                    var lookup = results.ToDictionary(x => x.ProductLineKey);
                    reader.NextResult();
                    while (reader.Read())
                    {
                        var product = new Product(reader);
                        lookup[product.ProductLineKey].Products.Add(product);
                    }
                }
            }

            return results;
        }

        public ProductLine? GetByKey(int productLineKey, bool includeProducts)
        {
            const string sqlA = @"SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineKey = @ProductLineKey;
SELECT p.ProductKey, p.ProductName, p.ProductLineKey, p.ShippingWeight, p.ProductWeight FROM Production.Product p WHERE p.ProductLineKey = @ProductLineKey;";

            const string sqlB = @"SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineKey = @ProductLineKey;";

            var sql = includeProducts ? sqlA : sqlB;
            ProductLine result;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@ProductLineKey", productLineKey);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                        result = new ProductLine(reader);
                    else
                        return null;

                    if (includeProducts)
                    {
                        reader.NextResult();
                        while (reader.Read())
                            result.Products.Add(new Product(reader));
                    }
                }
            }

            return result;
        }

        public void Update(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

            using (var con = OpenConnection())
                UpdateProductLine(con, null, productLine);
        }

        public void Update(Product product)
        {
            if (product == null)
                throw new ArgumentNullException(nameof(product), $"{nameof(product)} is null.");

            using (var con = OpenConnection())
                UpdateProduct(con, null, product);
        }

        public void UpdateGraph(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

            productLine.ApplyKeys();

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                UpdateProductLine(con, trans, productLine);
                foreach (var item in productLine.Products)
                {
                    if (item.ProductKey == 0)
                        InsertProduct(con, trans, item);
                    else
                        UpdateProduct(con, trans, item);
                }

                trans.Commit();
            }
        }

        public void UpdateGraphWithChildDeletes(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

            productLine.ApplyKeys();

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                //Find products to remove
                var originalProductKeys = GetProductKeys(con, trans, productLine.ProductLineKey);
                foreach (var item in productLine.Products)
                    originalProductKeys.Remove(item.ProductKey);

                UpdateProductLine(con, trans, productLine);
                foreach (var item in productLine.Products)
                {
                    if (item.ProductKey == 0)
                        InsertProduct(con, trans, item);
                    else
                        UpdateProduct(con, trans, item);
                }

                //Remove products
                foreach (var key in originalProductKeys)
                    DeleteProduct(con, trans, key);

                trans.Commit();
            }
        }

        public void UpdateGraphWithDeletes(ProductLine productLine, IList<int> productKeysToRemove)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

            productLine.ApplyKeys();

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                UpdateProductLine(con, trans, productLine);

                foreach (var item in productLine.Products)
                {
                    if (item.ProductKey == 0)
                        InsertProduct(con, trans, item);
                    else
                        UpdateProduct(con, trans, item);
                }

                if (productKeysToRemove != null)
                    foreach (var key in productKeysToRemove)
                        DeleteProduct(con, trans, key);

                trans.Commit();
            }
        }

        static void DeleteProduct(SqlConnection con, SqlTransaction trans, int productKey)
        {
            const string sql = "DELETE Production.Product WHERE ProductKey = @ProductKey;";

            using (var cmd = new SqlCommand(sql, con, trans))
            {
                cmd.Parameters.AddWithValue("@ProductKey", productKey);
                cmd.ExecuteNonQuery();
            }
        }

        static HashSet<int> GetProductKeys(SqlConnection con, SqlTransaction trans, int productLineKey)
        {
            const string sql = "SELECT p.ProductKey FROM Production.Product p WHERE p.ProductLineKey = @ProductLineKey";

            var results = new HashSet<int>();
            using (var cmd = new SqlCommand(sql, con, trans))
            {
                cmd.Parameters.AddWithValue("@ProductLineKey", productLineKey);
                using (var reader = cmd.ExecuteReader())
                    while (reader.Read())
                        results.Add(reader.GetInt32(0));
            }
            return results;
        }

        static void InsertProduct(SqlConnection con, SqlTransaction trans, Product product)
        {
            const string sql = "INSERT INTO Production.Product ( ProductName, ProductLineKey, ShippingWeight, ProductWeight ) OUTPUT Inserted.ProductKey VALUES ( @ProductName, @ProductLineKey, @ShippingWeight, @ProductWeight )";

            using (var cmd = new SqlCommand(sql, con, trans))
            {
                cmd.Parameters.AddWithValue("@ProductName", product.ProductName);
                cmd.Parameters.AddWithValue("@ProductLineKey", product.ProductLineKey);
                cmd.Parameters.AddWithValue("@ShippingWeight", product.ShippingWeight);
                cmd.Parameters.AddWithValue("@ProductWeight", product.ProductWeight);
                product.ProductKey = (int)cmd.ExecuteScalar();
            }
        }

        static void UpdateProduct(SqlConnection con, SqlTransaction? trans, Product product)
        {
            const string sql = "UPDATE Production.Product SET ProductName = @ProductName, ProductLineKey = @ProductLineKey, ShippingWeight = @ShippingWeight, ProductWeight = @ProductWeight WHERE ProductKey = @ProductKey;";

            using (var cmd = new SqlCommand(sql, con, trans))
            {
                cmd.Parameters.AddWithValue("@ProductKey", product.ProductKey);
                cmd.Parameters.AddWithValue("@ProductName", product.ProductName);
                cmd.Parameters.AddWithValue("@ProductLineKey", product.ProductLineKey);
                cmd.Parameters.AddWithValue("@ShippingWeight", product.ShippingWeight);
                cmd.Parameters.AddWithValue("@ProductWeight", product.ProductWeight);

                cmd.ExecuteNonQuery();
            }
        }

        static void UpdateProductLine(SqlConnection con, SqlTransaction? trans, ProductLine productLine)
        {
            const string sql = "UPDATE Production.ProductLine SET ProductLineName = @ProductLineName WHERE ProductLineKey = @ProductLineKey;";

            using (var cmd = new SqlCommand(sql, con, trans))
            {
                cmd.Parameters.AddWithValue("@ProductLineKey", productLine.ProductLineKey);
                cmd.Parameters.AddWithValue("@ProductLineName", productLine.ProductLineName);
                cmd.ExecuteNonQuery();
            }
        }
    }

Chain

Chain requires operating on the parent and child objects separately.

public class ModelWithChildrenScenario : IModelWithChildrenScenario<ProductLine, Product>
{
    readonly SqlServerDataSource m_DataSource;
    readonly string ProductTable = "Production.Product";

    public ModelWithChildrenScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public int Create(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        using (var trans = m_DataSource.BeginTransaction())
        {
            productLine.ProductLineKey = trans.Insert(productLine).ToInt32().Execute();
            productLine.ApplyKeys();
            trans.InsertBatch(productLine.Products).Execute();
            trans.Commit();
        }

        return productLine.ProductLineKey;
    }

    public void Delete(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        using (var trans = m_DataSource.BeginTransaction())
        {
            trans.DeleteSet<Product>(new { productLine.ProductLineKey }).Execute();
            trans.Delete(productLine).Execute();
            trans.Commit();
        }
    }

    public void DeleteByKey(int productLineKey)
    {
        using (var trans = m_DataSource.BeginTransaction())
        {
            trans.DeleteSet<Product>(new { productLineKey }).Execute();
            trans.DeleteByKey<ProductLine>(productLineKey).Execute();
            trans.Commit();
        }
    }

    public IList<ProductLine> FindByName(string productLineName, bool includeProducts)
    {
        var results = m_DataSource.From<ProductLine>(new { productLineName }).ToCollection().Execute();
        if (results.Count > 0 && includeProducts)
        {
            var children = m_DataSource.GetByColumnList<Product>("ProductLineKey",
                results.Select(pl => pl.ProductLineKey)).ToCollection().Execute();
            foreach (var line in results)
                line.Products.AddRange(children.Where(x => x.ProductLineKey == line.ProductLineKey));
        }
        return results;
    }

    public IList<ProductLine> GetAll(bool includeProducts)
    {
        var results = m_DataSource.From<ProductLine>().ToCollection().Execute();
        if (includeProducts)
        {
            var children = m_DataSource.From<Product>().ToCollection().Execute();
            foreach (var line in results)
                line.Products.AddRange(children.Where(x => x.ProductLineKey == line.ProductLineKey));
        }
        return results;
    }

    public ProductLine? GetByKey(int productLineKey, bool includeProducts)
    {
        var result = m_DataSource.GetByKey<ProductLine>(productLineKey).ToObjectOrNull().Execute();
        if (result != null && includeProducts)
        {
            var children = m_DataSource.From<Product>(new { result.ProductLineKey }).ToCollection().Execute();
            result.Products.AddRange(children);
        }
        return result;
    }

    public void Update(Product product)
    {
        if (product == null)
            throw new ArgumentNullException(nameof(product), $"{nameof(product)} is null.");

        m_DataSource.Update(product).Execute();
    }

    public void Update(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        m_DataSource.Update(productLine).Execute();
    }

    public void UpdateGraph(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        using (var trans = m_DataSource.BeginTransaction())
        {
            //Update parent row
            trans.Update(productLine).Execute();

            //Ensure new child rows have their parent's key
            productLine.ApplyKeys();

            //Insert/update the remaining child rows
            foreach (var row in productLine.Products)
                trans.Upsert(row).Execute();

            trans.Commit();
        }
    }

    public void UpdateGraphWithChildDeletes(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        using (var trans = m_DataSource.BeginTransaction())
        {
            //Update parent row
            trans.Update(productLine).Execute();

            //Find the list of child keys to remove
            var oldKeys = trans.From<Product>(new { productLine.ProductLineKey }).ToInt32List("ProductKey")
                .Execute().ToHashSet();

            foreach (var key in productLine.Products.Select(x => x.ProductKey))
                oldKeys.Remove(key);

            //Remove the old records
            foreach (var key in oldKeys)
                trans.DeleteByKey<Product>(key).Execute();

            //Ensure new child rows have their parent's key
            productLine.ApplyKeys();

            //Insert/update the child rows
            foreach (var row in productLine.Products)
                trans.Upsert(row).Execute();

            trans.Commit();
        }
    }

    public void UpdateGraphWithDeletes(ProductLine productLine, IList<int> productKeysToRemove)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        using (var trans = m_DataSource.BeginTransaction())
        {
            //Update parent row
            trans.Update(productLine).Execute();

            //Ensure new child rows have their parent's key
            productLine.ApplyKeys();

            //Insert/update the child rows
            foreach (var row in productLine.Products)
                trans.Upsert(row).Execute();

            if (productKeysToRemove?.Count > 0)
                trans.DeleteByKeyList(ProductTable, productKeysToRemove).Execute();

            trans.Commit();
        }
    }
}

Dapper

    public class ModelWithChildrenScenario : ScenarioBase, IModelWithChildrenScenario<ProductLine, Product>
    {
        public ModelWithChildrenScenario(string connectionString) : base(connectionString)
        {
        }

        public int Create(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

            const string sql = "INSERT INTO Production.ProductLine ( ProductLineName ) OUTPUT Inserted.ProductLineKey VALUES (@ProductLineName);";

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                productLine.ProductLineKey = (int)con.ExecuteScalar(sql, productLine, transaction: trans);
                productLine.ApplyKeys();

                foreach (var item in productLine.Products)
                    InsertProduct(con, trans, item);

                trans.Commit();
            }
            return productLine.ProductLineKey;
        }

        public void Delete(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

            const string sql = @"DELETE FROM Production.Product WHERE ProductLineKey = @ProductLineKey;
DELETE FROM Production.ProductLine WHERE ProductLineKey = @ProductLineKey";

            using (var con = OpenConnection())
                con.Execute(sql, productLine);
        }

        public void DeleteByKey(int productLineKey)
        {
            const string sql = @"DELETE FROM Production.Product WHERE ProductLineKey = @ProductLineKey;
DELETE FROM Production.ProductLine WHERE ProductLineKey = @ProductLineKey";

            using (var con = OpenConnection())
                con.Execute(sql, new { productLineKey });
        }

        public IList<ProductLine> FindByName(string productLineName, bool includeProducts)
        {
            const string sqlA = @"SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineName = @ProductLineName;
SELECT p.ProductKey, p.ProductName, p.ProductLineKey, p.ShippingWeight, p.ProductWeight FROM Production.Product p INNER JOIN Production.ProductLine pl ON p.ProductLineKey = pl.ProductLineKey WHERE pl.ProductLineName = @ProductLineName;";

            const string sqlB = @"SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineName = @ProductLineName;";

            using (var con = OpenConnection())
            {
                var sql = includeProducts ? sqlA : sqlB;
                var results = con.QueryMultiple(sql, new { productLineName });
                var productLines = results.Read<ProductLine>().ToList();

                if (includeProducts)
                {
                    var lookup = productLines.ToDictionary(x => x.ProductLineKey);
                    foreach (var product in results.Read<Product>())
                        lookup[product.ProductLineKey].Products.Add(product);
                }

                return productLines;
            }
        }

        public IList<ProductLine> GetAll(bool includeProducts)
        {
            const string sqlA = @"SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl;
SELECT p.ProductKey, p.ProductName, p.ProductLineKey, p.ShippingWeight, p.ProductWeight FROM Production.Product p INNER JOIN Production.ProductLine pl ON p.ProductLineKey = pl.ProductLineKey;";

            const string sqlB = @"SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl;";

            using (var con = OpenConnection())
            {
                var sql = includeProducts ? sqlA : sqlB;
                var results = con.QueryMultiple(sql);
                var productLines = results.Read<ProductLine>().ToList();

                if (includeProducts)
                {
                    var lookup = productLines.ToDictionary(x => x.ProductLineKey);
                    foreach (var product in results.Read<Product>())
                        lookup[product.ProductLineKey].Products.Add(product);
                }

                return productLines;
            }
        }

        public ProductLine? GetByKey(int productLineKey, bool includeProducts)
        {
            const string sqlA = @"SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineKey = @ProductLineKey;
SELECT p.ProductKey, p.ProductName, p.ProductLineKey, p.ShippingWeight, p.ProductWeight FROM Production.Product p WHERE p.ProductLineKey = @ProductLineKey;";

            const string sqlB = @"SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineKey = @ProductLineKey;";

            using (var con = OpenConnection())
            {
                var sql = includeProducts ? sqlA : sqlB;
                var results = con.QueryMultiple(sql, new { productLineKey });
                var productLine = results.ReadSingleOrDefault<ProductLine>();

                if (productLine == null)
                    return null;

                if (includeProducts)
                    foreach (var product in results.Read<Product>())
                        productLine.Products.Add(product);

                return productLine;
            }
        }

        public void Update(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

            using (var con = OpenConnection())
                UpdateProductLine(con, null, productLine);
        }

        public void Update(Product product)
        {
            if (product == null)
                throw new ArgumentNullException(nameof(product), $"{nameof(product)} is null.");

            using (var con = OpenConnection())
                UpdateProduct(con, null, product);
        }

        public void UpdateGraph(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

            productLine.ApplyKeys();

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                UpdateProductLine(con, trans, productLine);
                foreach (var item in productLine.Products)
                {
                    if (item.ProductKey == 0)
                        InsertProduct(con, trans, item);
                    else
                        UpdateProduct(con, trans, item);
                }
                trans.Commit();
            }
        }

        public void UpdateGraphWithChildDeletes(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

            productLine.ApplyKeys();

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                //Find products to remove
                var originalProductKeys = GetProductKeys(con, trans, productLine.ProductLineKey);
                foreach (var item in productLine.Products)
                    originalProductKeys.Remove(item.ProductKey);

                UpdateProductLine(con, trans, productLine);
                foreach (var item in productLine.Products)
                {
                    if (item.ProductKey == 0)
                        InsertProduct(con, trans, item);
                    else
                        UpdateProduct(con, trans, item);
                }

                //Remove products
                foreach (var key in originalProductKeys)
                    DeleteProduct(con, trans, key);

                trans.Commit();
            }
        }

        public void UpdateGraphWithDeletes(ProductLine productLine, IList<int> productKeysToRemove)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

            productLine.ApplyKeys();

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                UpdateProductLine(con, trans, productLine);

                foreach (var item in productLine.Products)
                {
                    if (item.ProductKey == 0)
                        InsertProduct(con, trans, item);
                    else
                        UpdateProduct(con, trans, item);
                }

                if (productKeysToRemove != null)
                    foreach (var key in productKeysToRemove)
                        DeleteProduct(con, trans, key);

                trans.Commit();
            }
        }

        static void DeleteProduct(SqlConnection con, SqlTransaction trans, int productKey)
        {
            const string sql = "DELETE Production.Product WHERE ProductKey = @ProductKey;";

            con.Execute(sql, new { productKey }, transaction: trans);
        }

        static HashSet<int> GetProductKeys(SqlConnection con, SqlTransaction trans, int productLineKey)
        {
            const string sql = "SELECT p.ProductKey FROM Production.Product p WHERE p.ProductLineKey = @ProductLineKey";

            return con.Query<int>(sql, new { productLineKey }, transaction: trans).ToHashSet();
        }

        static void InsertProduct(SqlConnection con, SqlTransaction trans, Product product)
        {
            const string sql = "INSERT INTO Production.Product ( ProductName, ProductLineKey, ShippingWeight, ProductWeight ) OUTPUT Inserted.ProductKey VALUES ( @ProductName, @ProductLineKey, @ShippingWeight, @ProductWeight )";

            product.ProductKey = con.ExecuteScalar<int>(sql, product, transaction: trans);
        }

        static void UpdateProduct(SqlConnection con, SqlTransaction? trans, Product product)
        {
            const string sql = "UPDATE Production.Product SET ProductName = @ProductName, ProductLineKey = @ProductLineKey, ShippingWeight = @ShippingWeight, ProductWeight = @ProductWeight WHERE ProductKey = @ProductKey;";

            con.Execute(sql, product, transaction: trans);
        }

        static void UpdateProductLine(SqlConnection con, SqlTransaction? trans, ProductLine productLine)
        {
            const string sql = "UPDATE Production.ProductLine SET ProductLineName = @ProductLineName WHERE ProductLineKey = @ProductLineKey;";

            con.Execute(sql, productLine, transaction: trans);
        }
    }

DbConnector

public class ModelWithChildrenScenario : ScenarioBase, IModelWithChildrenScenario<ProductLine, Product>
{
    public ModelWithChildrenScenario(string connectionString) : base(connectionString)
    {
    }

    public int Create(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        const string sql = "INSERT INTO Production.ProductLine ( ProductLineName ) OUTPUT Inserted.ProductLineKey VALUES (@ProductLineName);";

        //Build the main IDbJob.
        IDbJob<int> jobProductLine = DbConnector
            .Scalar<int>(sql, productLine)
            .OnExecuted((int result, IDbExecutedModel im) =>
            {
                productLine.ProductLineKey = result;
                productLine.ApplyKeys();
                return result;
            });

        if (productLine.Products.Count == 0)
        {
            return jobProductLine.Execute();
        }
        else
        {
            //Leverage DbConnector's Job Batching feature
            DbJob.ExecuteAll(jobProductLine, BuildInsertOrUpdateProducts(productLine.Products));

            return productLine.ProductLineKey;
        }
    }

    public void Delete(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        const string sql = @"DELETE FROM Production.Product WHERE ProductLineKey = @ProductLineKey;
        DELETE FROM Production.ProductLine WHERE ProductLineKey = @ProductLineKey";

        DbConnector.NonQuery(sql, productLine).Execute();
    }

    public void DeleteByKey(int productLineKey)
    {
        const string sql = @"DELETE FROM Production.Product WHERE ProductLineKey = @ProductLineKey;
        DELETE FROM Production.ProductLine WHERE ProductLineKey = @ProductLineKey";

        DbConnector.NonQuery(sql, new { productLineKey }).Execute();
    }

    public IList<ProductLine> FindByName(string productLineName, bool includeProducts)
    {
        if (includeProducts)
        {
            const string sqlA = @"
            SELECT 
                pl.ProductLineKey, 
                pl.ProductLineName 
            FROM Production.ProductLine pl 
            WHERE pl.ProductLineName = @ProductLineName;
            SELECT 
                p.ProductKey, 
                p.ProductName, 
                p.ProductLineKey, 
                p.ShippingWeight, 
                p.ProductWeight 
            FROM Production.Product p 
            INNER JOIN Production.ProductLine pl ON p.ProductLineKey = pl.ProductLineKey 
            WHERE pl.ProductLineName = @ProductLineName;";

            var tupleResult = DbConnector.ReadToList<ProductLine, Product>(sqlA, new { productLineName }).Execute();

            List<ProductLine> productLines = tupleResult.Item1;
            List<Product> products = tupleResult.Item2;

            productLines.ForEach(pl => pl.Products.AddRange(products.Where(p => p.ProductLineKey == pl.ProductLineKey)));

            return productLines;
        }
        else
        {
            const string sqlB = @"
            SELECT 
                pl.ProductLineKey, 
                pl.ProductLineName 
            FROM Production.ProductLine pl 
            WHERE pl.ProductLineName = @ProductLineName;";

            return DbConnector.ReadToList<ProductLine>(sqlB, new { productLineName }).Execute();
        }
    }

    public IList<ProductLine> GetAll(bool includeProducts)
    {
        if (includeProducts)
        {
            const string sqlA = @"
            SELECT 
                pl.ProductLineKey, 
                pl.ProductLineName 
            FROM Production.ProductLine pl;
            SELECT 
                p.ProductKey, 
                p.ProductName, 
                p.ProductLineKey, 
                p.ShippingWeight, 
                p.ProductWeight 
            FROM Production.Product p 
            INNER JOIN Production.ProductLine pl ON p.ProductLineKey = pl.ProductLineKey;";

            var tupleResult = DbConnector.ReadToList<ProductLine, Product>(sqlA).Execute();

            List<ProductLine> productLines = tupleResult.Item1;
            List<Product> products = tupleResult.Item2;

            productLines.ForEach(pl => pl.Products.AddRange(products.Where(p => p.ProductLineKey == pl.ProductLineKey)));

            return productLines;
        }
        else
        {
            const string sqlB = @"
            SELECT 
                pl.ProductLineKey, 
                pl.ProductLineName 
            FROM Production.ProductLine pl;";

            return DbConnector.ReadToList<ProductLine>(sqlB).Execute();
        }
    }

    public ProductLine? GetByKey(int productLineKey, bool includeProducts)
    {
        if (includeProducts)
        {
            const string sqlA = @"
            SELECT 
                pl.ProductLineKey, 
                pl.ProductLineName 
            FROM Production.ProductLine pl
            WHERE pl.ProductLineKey = @ProductLineKey;
            SELECT 
                p.ProductKey, 
                p.ProductName, 
                p.ProductLineKey, 
                p.ShippingWeight, 
                p.ProductWeight 
            FROM Production.Product p 
            INNER JOIN Production.ProductLine pl ON p.ProductLineKey = pl.ProductLineKey
            WHERE p.ProductLineKey = @productLineKey;";

            var tupleResult = DbConnector.ReadToList<ProductLine, Product>(sqlA, new { productLineKey }).Execute();

            ProductLine productLine = tupleResult.Item1.FirstOrDefault();

            if (productLine != null)
            {
                productLine.Products.AddRange(tupleResult.Item2);
            }

            return productLine;
        }
        else
        {
            const string sqlB = @"
            SELECT 
                pl.ProductLineKey, 
                pl.ProductLineName 
            FROM Production.ProductLine pl
            WHERE pl.ProductLineKey = @productLineKey;";

            return DbConnector.ReadFirstOrDefault<ProductLine>(sqlB, new { productLineKey }).Execute();
        }
    }

    public void Update(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        BuildUpdateProductLine(productLine).Execute();
    }

    public void Update(Product product)
    {
        if (product == null)
            throw new ArgumentNullException(nameof(product), $"{nameof(product)} is null.");

        BuildUpdateProduct(product).Execute();
    }

    public void UpdateGraph(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        productLine.ApplyKeys();

        //Leverage DbConnector's Job Batching feature
        DbJob.ExecuteAll(BuildUpdateProductLine(productLine), BuildInsertOrUpdateProducts(productLine.Products));
    }

    public void UpdateGraphWithChildDeletes(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        productLine.ApplyKeys();

        //Note: This could all be done in a single query transaction leveraging the MERGE feature
        using (var con = OpenConnection())
        using (var trans = con.BeginTransaction())
        {
            //Find products
            var originalProductKeys = BuildGetProductKeys(productLine.ProductLineKey).Execute(trans);

            foreach (var item in productLine.Products)
                originalProductKeys.Remove(item.ProductKey);

            //Leverage DbConnector's Job Batching feature
            DbJob.ExecuteAll(trans, BuildUpdateProductLine(productLine), BuildInsertOrUpdateProducts(productLine.Products));

            //Remove products
            if (originalProductKeys.Count > 0)
                BuildDeleteProducts(originalProductKeys).Execute(trans);

            trans.Commit();
        }
    }

    public void UpdateGraphWithDeletes(ProductLine productLine, IList<int> productKeysToRemove)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        productLine.ApplyKeys();

        if (productKeysToRemove == null || productKeysToRemove.Count == 0)
        {
            DbJob.ExecuteAll(BuildUpdateProductLine(productLine), BuildInsertOrUpdateProducts(productLine.Products));
        }
        else
        {
            DbJob.ExecuteAll(BuildUpdateProductLine(productLine), BuildInsertOrUpdateProducts(productLine.Products), BuildDeleteProducts(productKeysToRemove));
        }
    }

    protected IDbJob<int?> BuildDeleteProduct(int productKey)
    {
        const string sql = "DELETE Production.Product WHERE ProductKey = @productKey;";

        return DbConnector.NonQuery(sql, new { productKey });
    }

    protected IDbJob<int?> BuildDeleteProducts(IEnumerable<int> productKeys)
    {
        if (productKeys == null || !productKeys.Any())
            throw new ArgumentException($"{nameof(productKeys)} is null or empty.", nameof(productKeys));

        var keyList = string.Join(", ", productKeys);
        var sql = $"DELETE Production.Product WHERE ProductKey IN ({keyList});";

        return DbConnector.NonQuery(sql);
    }

    protected IDbJob<HashSet<int>> BuildGetProductKeys(int productLineKey)
    {
        const string sql = "SELECT p.ProductKey FROM Production.Product p WHERE p.ProductLineKey = @productLineKey";

        return DbConnector.ReadToHashSet<int>(sql, new { productLineKey });
    }

    protected IDbJob<int> BuildInsertProduct(Product product)
    {
        const string sql = "INSERT INTO Production.Product ( ProductName, ProductLineKey, ShippingWeight, ProductWeight ) OUTPUT Inserted.ProductKey VALUES ( @ProductName, @ProductLineKey, @ShippingWeight, @ProductWeight )";

        return DbConnector.Scalar<int>(sql, product).OnCompleted(result => { product.ProductKey = result; return result; });
    }

    protected IDbJob<int?> BuildInsertOrUpdateProducts(IEnumerable<Product> products)
    {
        if (products == null || !products.Any())
            throw new ArgumentException($"{nameof(products)} is null or empty.", nameof(products));

        string sql = $@"
            MERGE INTO {Product.TableName} target
            USING
            (
                VALUES (
                    @{nameof(Product.ProductKey)},
                    @{nameof(Product.ProductName)},
                    @{nameof(Product.ProductLineKey)},
                    @{nameof(Product.ShippingWeight)},
                    @{nameof(Product.ProductWeight)}
                )
            ) source (
                        {nameof(Product.ProductKey)},
                        {nameof(Product.ProductName)},
                        {nameof(Product.ProductLineKey)},
                        {nameof(Product.ShippingWeight)},
                        {nameof(Product.ProductWeight)}
                     )
            ON target.ProductKey = source.{nameof(Product.ProductKey)}
            WHEN MATCHED THEN
                UPDATE SET ProductName = source.{nameof(Product.ProductName)},
                           ProductLineKey = source.{nameof(Product.ProductLineKey)},
                           ShippingWeight = source.{nameof(Product.ShippingWeight)},
                           ProductWeight = source.{nameof(Product.ProductWeight)}
            WHEN NOT MATCHED THEN
                INSERT
                (
                    ProductName,
                    ProductLineKey,
                    ShippingWeight,
                    ProductWeight
                )
                VALUES
                (
                    source.{nameof(Product.ProductName)},
                    source.{nameof(Product.ProductLineKey)},
                    source.{nameof(Product.ShippingWeight)},
                    source.{nameof(Product.ProductWeight)}
                )
            OUTPUT Inserted.ProductKey;";

        Product firstProd = products.First();

        //Best approach for unlimited inserts since SQL server has parameter amount restrictions
        //https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15
        return DbConnector.Build<int?>(
                sql: sql,
                param: firstProd,
                onExecute: (int? result, IDbExecutionModel em) =>
                {
                    //Set the command
                    DbCommand command = em.Command;

                    //Execute first row.
                    firstProd.ProductKey = (int)command.ExecuteScalar()!;
                    em.NumberOfRowsAffected = 1;

                    //Set and execute remaining rows.
                    foreach (var prod in products.Skip(1))
                    {
                        command.Parameters[nameof(Product.ProductKey)].Value = prod.ProductKey;
                        command.Parameters[nameof(Product.ProductName)].Value = prod.ProductName ?? (object)DBNull.Value;
                        command.Parameters[nameof(Product.ProductLineKey)].Value = prod.ProductLineKey;
                        command.Parameters[nameof(Product.ShippingWeight)].Value = prod.ShippingWeight ?? (object)DBNull.Value;
                        command.Parameters[nameof(Product.ProductWeight)].Value = prod.ProductWeight ?? (object)DBNull.Value;

                        prod.ProductKey = (int)command.ExecuteScalar()!;
                        em.NumberOfRowsAffected += 1;
                    }

                    return em.NumberOfRowsAffected;
                }
            )
            .WithIsolationLevel(IsolationLevel.ReadCommitted);//Use a transaction
    }

    protected IDbJob<int?> BuildUpdateProduct(Product product)
    {
        const string sql = "UPDATE Production.Product SET ProductName = @ProductName, ProductLineKey = @ProductLineKey, ShippingWeight = @ShippingWeight, ProductWeight = @ProductWeight WHERE ProductKey = @ProductKey;";

        return DbConnector.NonQuery(sql, product);
    }

    protected IDbJob<int?> BuildUpdateProductLine(ProductLine productLine)
    {
        const string sql = "UPDATE Production.ProductLine SET ProductLineName = @ProductLineName WHERE ProductLineKey = @ProductLineKey;";

        return DbConnector.NonQuery(sql, productLine);
    }
}

Entity Framework 6

TODO

Entity Framework Core

When performing an update, ensure that the EntityState is correctly set for each child record based on whether it is an insert or update.

For partial deletes, you have to explicitly find and delete the child rows that are no longer needed.

For deletes, ensure the child record in DBContext is setup with .OnDelete(DeleteBehavior.Cascade)

public class ModelWithChildrenScenario : IModelWithChildrenScenario<ProductLine, Product>
{
    private Func<OrmCookbookContext> CreateDbContext;

    public ModelWithChildrenScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public int Create(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        //A transaction is automatically created when `SaveChanges()` is called.
        using (var context = CreateDbContext())
        {
            context.ProductLines.Add(productLine);
            context.SaveChanges();
            return productLine.ProductLineKey;
        }
    }

    public void Delete(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        using (var context = CreateDbContext())
        {
            context.ProductLines.Remove(productLine);
            context.SaveChanges();
        }
    }

    public void DeleteByKey(int productLineKey)
    {
        using (var context = CreateDbContext())
        {
            //Need to explicitly fetch child records in order to delete them.
            var temp = context.ProductLines.Where(x => x.ProductLineKey == productLineKey).Include(x => x.Products).Single();
            context.ProductLines.Remove(temp);
            context.SaveChanges();
        }
    }

    public IList<ProductLine> FindByName(string productLineName, bool includeProducts)
    {
        using (var context = CreateDbContext())
        {
            if (includeProducts)
                return context.ProductLines.Where(x => x.ProductLineName == productLineName).Include(x => x.Products).ToList();
            else
                return context.ProductLines.Where(x => x.ProductLineName == productLineName).ToList();
        }
    }

    public IList<ProductLine> GetAll(bool includeProducts)
    {
        using (var context = CreateDbContext())
        {
            if (includeProducts)
                return context.ProductLines.Include(x => x.Products).ToList();
            else
                return context.ProductLines.ToList();
        }
    }

    public ProductLine? GetByKey(int productLineKey, bool includeProducts)
    {
        using (var context = CreateDbContext())
        {
            if (includeProducts)
                return context.ProductLines.Where(x => x.ProductLineKey == productLineKey).Include(x => x.Products).SingleOrDefault();
            else
                return context.ProductLines.Find(productLineKey);
        }
    }

    public void Update(Product product)
    {
        if (product == null)
            throw new ArgumentNullException(nameof(product), $"{nameof(product)} is null.");

        using (var context = CreateDbContext())
        {
            context.Entry(product).State = EntityState.Modified;
            context.SaveChanges();
        }
    }

    public void Update(ProductLine productLine)
    {
        using (var context = CreateDbContext())
        {
            context.Entry(productLine).State = EntityState.Modified;
            context.SaveChanges();
        }
    }

    public void UpdateGraph(ProductLine productLine)
    {
        //A transaction is automatically created when `SaveChanges()` is called.
        using (var context = CreateDbContext())
        {
            context.Entry(productLine).State = EntityState.Modified;

            foreach (var item in productLine.Products)
                if (item.ProductKey == 0)
                    context.Entry(item).State = EntityState.Added;
                else
                    context.Entry(item).State = EntityState.Modified;

            context.SaveChanges();
        }
    }

    public void UpdateGraphWithChildDeletes(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        //An explicit transaction is needed reading the rows to delete happens outside of the `SaveChanges` call.
        using (var context = CreateDbContext())
        using (var transaction = context.Database.BeginTransaction())
        {
            var validKeys = productLine.Products.Select(x => x.ProductKey).ToList();

            //get rows to delete
            var oldRows = context.Products.Where(x => x.ProductLineKey == productLine.ProductLineKey && !validKeys.Contains(x.ProductKey)).ToList();

            //Remove the old records
            foreach (var row in oldRows)
                context.Products.Remove(row);

            context.Entry(productLine).State = EntityState.Modified;
            foreach (var item in productLine.Products)
                if (item.ProductKey == 0)
                    context.Entry(item).State = EntityState.Added;
                else
                    context.Entry(item).State = EntityState.Modified;
            context.SaveChanges();

            transaction.Commit();
        }
    }

    public void UpdateGraphWithDeletes(ProductLine productLine, IList<int> productKeysToRemove)
    {
        //A transaction is automatically created when `SaveChanges()` is called.
        using (var context = CreateDbContext())
        {
            context.Entry(productLine).State = EntityState.Modified;
            foreach (var item in productLine.Products)
                if (item.ProductKey == 0)
                    context.Entry(item).State = EntityState.Added;
                else
                    context.Entry(item).State = EntityState.Modified;

            if (productKeysToRemove != null)
                foreach (var key in productKeysToRemove)
                    context.Entry(new Product() { ProductKey = key }).State = EntityState.Deleted;

            context.SaveChanges();
        }
    }
}

LINQ to DB

TODO

LLBLGen Pro

With LLBLGen Pro we can utilize straightforward ORM functionality with this, utilizing the stand-alone, database agnostic Unit of Work class for easy transaction management and order-of-operation control. For the partial deletes two variants are implemented, one with a removal tracker, which tracks which entities are removed and which can be deleted in one go (the Update method in the alt repository), and one with a direct delete on the table using a not-in predicate, available in the regular Update method.

Nested deletes are implemented explicitly, as cascading deletes aren't supported at the ORM level; to have cascading deletes you have to set up the deletes as cascading on the foreign key constraint.

 public class ModelWithChildrenScenario : IModelWithChildrenScenario<ProductLineEntity, ProductEntity>
 {
     public int Create(ProductLineEntity productLine)
     {
         if (productLine == null)
             throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

         using (var adapter = new DataAccessAdapter())
         {
             adapter.SaveEntity(productLine);
             return productLine.ProductLineKey;
         }
     }

     public void Delete(ProductLineEntity productLine)
     {
         if (productLine == null)
             throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

         // We'll have to delete the whole graph so first the related entities, then the main entity.
         // Let's use a Unit of work here for that.
         var uow = new UnitOfWork2();
         uow.AddCollectionForDelete(productLine.Products);
         uow.AddForDelete(productLine);
         using (var adapter = new DataAccessAdapter())
         {
             uow.Commit(adapter);
         }
     }

     public void DeleteByKey(int productLineKey)
     {
         // let's directly delete the entities, without fetching them. Use a unit of work for this
         // to wrap everything neatly in a transaction when it's committed. A Unit of work is a
         // persistence agnostic object you can pass on freely to add work and then have all the work
         // performed in a single transaction.
         var uow = new UnitOfWork2();
         uow.AddDeleteEntitiesDirectlyCall(typeof(ProductEntity), 
                                  new RelationPredicateBucket(ProductFields.ProductLineKey.Equal(productLineKey)));
         uow.AddDeleteEntitiesDirectlyCall(typeof(ProductLineEntity), 
                                  new RelationPredicateBucket(ProductLineFields.ProductLineKey.Equal(productLineKey)));
         using (var adapter = new DataAccessAdapter())
         {
             uow.Commit(adapter);
         }
     }

     public IList<ProductLineEntity> FindByName(string productLineName, bool includeProducts)
     {
         using (var adapter = new DataAccessAdapter())
         {
             var metaData = new LinqMetaData(adapter);
             if (includeProducts)
             {
                 return metaData.ProductLine.Where(x => x.ProductLineName == productLineName)
                       .WithPath(p => p.Prefetch(pl => pl.Products)).ToList();
             }
             return metaData.ProductLine.Where(x => x.ProductLineName == productLineName).ToList();
         }
     }

     public IList<ProductLineEntity> GetAll(bool includeProducts)
     {
         using (var adapter = new DataAccessAdapter())
         {
             var metaData = new LinqMetaData(adapter);
             if (includeProducts)
             {
                 return metaData.ProductLine.WithPath(p => p.Prefetch(pl => pl.Products)).ToList();
             }
             return metaData.ProductLine.ToList();
         }
     }

     public ProductLineEntity? GetByKey(int productLineKey, bool includeProducts)
     {
         using (var adapter = new DataAccessAdapter())
         {
             var metaData = new LinqMetaData(adapter);
             if (includeProducts)
             {
                 var toReturn = metaData.ProductLine.Where(x => x.ProductLineKey == productLineKey)
                               .WithPath(p => p.Prefetch(pl => pl.Products)).SingleOrDefault();
                 if (toReturn != null)
                 {
                     // insert removal tracker for tracking removed entities.
                     toReturn.Products.RemovedEntitiesTracker = new EntityCollection<ProductEntity>();
                 }
                 return toReturn;
             }
             return metaData.ProductLine.SingleOrDefault(x => x.ProductLineKey == productLineKey);
         }
     }

     public void Update(ProductEntity product)
     {
         if (product == null)
             throw new ArgumentNullException(nameof(product), $"{nameof(product)} is null.");

         using (var adapter = new DataAccessAdapter())
         {
             adapter.SaveEntity(product);
         }
     }

     public void Update(ProductLineEntity productLine)
     {
         if (productLine == null)
             throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

// Specify the order of operations for the unit of work, so it will first perform delete operations
// directly on the database and then do inserts followed by updates. We have to specify the order
// here as it's different from the default, where DeletesPerformedDirectly are done last. 
var uow = new UnitOfWork2(new List<UnitOfWorkBlockType>()
                          {
                              UnitOfWorkBlockType.DeletesPerformedDirectly,
                              UnitOfWorkBlockType.Inserts, 
                              UnitOfWorkBlockType.Updates
                          });
         uow.AddForSave(productLine, null, refetch: true, recurse: false);
         using (var adapter = new DataAccessAdapter())
         {
             uow.Commit(adapter);
         }
     }

     public void UpdateGraph(ProductLineEntity productLine)
     {
         if (productLine == null)
             throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

var uow = new UnitOfWork2(new List<UnitOfWorkBlockType>()
                          {
                              UnitOfWorkBlockType.DeletesPerformedDirectly,
                              UnitOfWorkBlockType.Inserts,
                              UnitOfWorkBlockType.Updates
                          });
         uow.AddForSave(productLine);
         using (var adapter = new DataAccessAdapter())
         {
             uow.Commit(adapter);
         }
     }

     public virtual void UpdateGraphWithChildDeletes(ProductLineEntity productLine)
     {
         if (productLine == null)
             throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

         // this update method will update the related products. Any removed product has to be removed as it's orphaned.
         // We have to remove all products which key isn't in the set of products currently related to the passed in productline.
         // To do that we'll do a delete directly using a where clause where all entities with a key not in the set of
         // keys of the current related product entities are removed. We'll wrap it all in a unit of work for easy transaction handling.
         // In the unit of work, we have to schedule the direct deletes before the insert of the new row, otherwise it's removed,
         // as it doesn't have a PK yet, so the IN clause we're using won't match it.
         var currentKeys = productLine.Products.Select(p => p.ProductKey).ToList();
var uow = new UnitOfWork2(new List<UnitOfWorkBlockType>()
                          {
                              UnitOfWorkBlockType.DeletesPerformedDirectly,
                              UnitOfWorkBlockType.Inserts, 
                              UnitOfWorkBlockType.Updates
                          });
         uow.AddDeleteEntitiesDirectlyCall(typeof(ProductEntity), new RelationPredicateBucket(ProductFields.ProductKey.NotIn(currentKeys)));
         uow.AddForSave(productLine);
         using (var adapter = new DataAccessAdapter())
         {
             uow.Commit(adapter);
         }
     }

     public void UpdateGraphWithDeletes(ProductLineEntity productLine, IList<int> productKeysToRemove)
     {
         if (productLine == null)
             throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

var uow = new UnitOfWork2(new List<UnitOfWorkBlockType>()
                          {
                              UnitOfWorkBlockType.DeletesPerformedDirectly,
                              UnitOfWorkBlockType.Inserts,
                              UnitOfWorkBlockType.Updates
                          });
         if (productKeysToRemove?.Count > 0)
             uow.AddDeleteEntitiesDirectlyCall(typeof(ProductEntity), new RelationPredicateBucket(ProductFields.ProductKey.In(productKeysToRemove)));
         uow.AddForSave(productLine);
         using (var adapter = new DataAccessAdapter())
         {
             uow.Commit(adapter);
         }
     }
 }

Alternative Update method using removal tracker functionality

public class ModelWithChildrenScenarioAlt : ModelWithChildrenScenario
{
    public override void UpdateGraphWithChildDeletes(ProductLineEntity productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        // this update method will update the related products. Any removed product has to be removed as it's orphaned.
        // we inserted a removal tracker in the productline entity to track these, so we can just delete them from
        // this collection. We also have to update the entity and related entities. We'll use a unit of work object
        // for this to have easy transaction management.
        var uow = new UnitOfWork2();
        uow.AddForSave(productLine);
        uow.AddCollectionForDelete(productLine.Products.RemovedEntitiesTracker);
        using (var adapter = new DataAccessAdapter())
        {
            uow.Commit(adapter);
        }
    }
}

NHibernate

By default, NHibernate does not support a clean separation between the data access layer and the rest of the application. This is due to the way the lazy-loading works, which requires an active ISession even when lazy-loading isn't desired.

The work-around is to explicitly trigger lazy-loading when the child rows are desired. When the child rows are not desired, block lazy-loading by setting the collection property to an empty list.

For partial deletes, ensure that you are using cascade="all-delete-orphan". Otherwise it will ignore the missing child rows. (Alternately, you can pass in a separate list of rows to delete.)

public class ModelWithChildrenScenario : IModelWithChildrenScenario<ProductLine, Product>
{
    readonly ISessionFactory m_SessionFactory;

    public ModelWithChildrenScenario(ISessionFactory sessionFactory)
    {
        m_SessionFactory = sessionFactory;
    }

    public int Create(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        productLine.ApplyKeys();

        using (var session = m_SessionFactory.OpenSession())
        {
            session.Save(productLine);
            session.Flush();
            return productLine.ProductLineKey;
        }
    }

    public void Delete(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        using (var session = m_SessionFactory.OpenSession())
        {
            session.Delete(productLine);
            session.Flush();
        }
    }

    public void DeleteByKey(int productLineKey)
    {
        using (var session = m_SessionFactory.OpenSession())
        {
            var temp = session.Get<ProductLine>(productLineKey);

            session.Delete(temp);
            session.Flush();
        }
    }

    public IList<ProductLine> FindByName(string productLineName, bool includeProducts)
    {
        using (var session = m_SessionFactory.OpenSession())
        {
            var result = session.QueryOver<ProductLine>().Where(pl => pl.ProductLineName == productLineName).List();

            if (includeProducts)
                result.SelectMany(x => x.Products).All(x => true); //force lazy-load
            else
                foreach (var line in result)
                    line.Products = new List<Product>();//disable lazy-loading

            return result;
        }
    }

    public IList<ProductLine> GetAll(bool includeProducts)
    {
        using (var session = m_SessionFactory.OpenSession())
        {
            var result = session.QueryOver<ProductLine>().List();

            if (includeProducts)
                result.SelectMany(x => x.Products).All(x => true); //force lazy-load
            else
                foreach (var line in result)
                    line.Products = new List<Product>();//disable lazy-loading

            return result;
        }
    }

    public ProductLine? GetByKey(int productLineKey, bool includeProducts)
    {
        using (var session = m_SessionFactory.OpenSession())
        {
            var result = session.Get<ProductLine>(productLineKey);
            if (result != null)
            {
                if (includeProducts)
                    result.Products.All(x => true); //force lazy-load
                else
                    result.Products = new List<Product>(); //disable lazy-loading
            }
            return result;
        }
    }

    public void UpdateGraphWithChildDeletes(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        productLine.ApplyKeys();

        using (var session = m_SessionFactory.OpenSession())
        using (var trans = session.BeginTransaction())
        {
            session.Update(productLine);
            session.Flush();
            trans.Commit();
        }
    }

    public void Update(Product product)
    {
        if (product == null)
            throw new ArgumentNullException(nameof(product), $"{nameof(product)} is null.");

        using (var session = m_SessionFactory.OpenSession())
        using (var trans = session.BeginTransaction())
        {
            session.Merge(product);
            session.Flush();
            trans.Commit();
        }
    }

    public void Update(ProductLine productLine)
    {
        throw new AssertInconclusiveException();
    }

    public void UpdateGraph(ProductLine productLine)
    {
        throw new AssertInconclusiveException();
    }

    public void UpdateGraphWithDeletes(ProductLine productLine, IList<int> productKeysToRemove)
    {
        throw new AssertInconclusiveException();
    }
}

RepoDb

public class ModelWithChildrenScenario : DbRepository<SqlConnection>,
    IModelWithChildrenScenario<ProductLine, Product>
{
    public ModelWithChildrenScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public int Create(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        var key = Insert<ProductLine, int>(productLine);
        productLine.ApplyKeys();
        InsertAll(productLine.Products);
        return key;
    }

    private void ExecuteDelete(int productLineKey)
    {
        var sql = @"DELETE FROM Production.Product WHERE ProductLineKey = @ProductLineKey;
            DELETE FROM Production.ProductLine WHERE ProductLineKey = @ProductLineKey;";

        using (var con = CreateConnection(true))
        {
            con.ExecuteNonQuery(sql, new { productLineKey });
        }
    }

    public void Delete(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        //base.Delete(productLine);
        ExecuteDelete(productLine.ProductLineKey);
    }

    public void DeleteByKey(int productLineKey)
    {
        //base.Delete<ProductLine>(productLineKey);
        ExecuteDelete(productLineKey);
    }

    private void FetchProducts(IEnumerable<ProductLine> productLines)
    {
        var keys = productLines.Select(e => e.ProductLineKey).AsList();
        Query<Product>(e => keys.Contains(e.ProductLineKey))
            .AsList()
            .ForEach(p =>
                productLines.First(e => e.ProductLineKey == p.ProductLineKey).Products.Add(p));
    }

    public IList<ProductLine> FindByName(string productLineName, bool includeProducts)
    {
        var lines = Query<ProductLine>(e => e.ProductLineName == productLineName);
        if (includeProducts)
            FetchProducts(lines);
        return lines.AsList();
    }

    public IList<ProductLine> GetAll(bool includeProducts)
    {
        var lines = QueryAll<ProductLine>();
        if (includeProducts)
            FetchProducts(lines);
        return lines.AsList();
    }

    public ProductLine? GetByKey(int productLineKey, bool includeProducts)
    {
        var line = Query<ProductLine>(productLineKey).FirstOrDefault();
        if (includeProducts && null != line)
            line.Products.AddRange(
                Query<Product>(e => e.ProductLineKey == line.ProductLineKey));
        return line;
    }

    public void Update(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        base.Update(productLine);
    }

    public void Update(Product product)
    {
        if (product == null)
            throw new ArgumentNullException(nameof(product), $"{nameof(product)} is null.");

        base.Update(product);
    }

    public void UpdateGraph(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        productLine.ApplyKeys();

        Update(productLine);
        MergeAll(productLine.Products);
    }

    public void UpdateGraphWithChildDeletes(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        productLine.ApplyKeys();

        var products = Query<Product>(p => p.ProductLineKey == productLine.ProductLineKey);
        var originalProductKeys = products
            .Select(p => p.ProductKey);
        var currentProductKeys = productLine
            .Products
            .Select(e => e.ProductKey);
        var productKeysToRemove = originalProductKeys
            .Except(currentProductKeys)
            .AsList();

        UpdateGraphWithDeletes(productLine, productKeysToRemove);
    }

    public void UpdateGraphWithDeletes(ProductLine productLine, IList<int> productKeysToRemove)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        productLine.ApplyKeys();

        Update(productLine);

        if (productKeysToRemove?.Any() == true)
            Delete<Product>(e => productKeysToRemove.Contains(e.ProductKey));

        if (productLine.Products?.Any() == true)
            MergeAll<Product>(productLine.Products);
    }
}

ServiceStack

public class ModelWithChildrenScenario : IModelWithChildrenScenario<ProductLine, Product>
{
    private readonly IDbConnectionFactory _dbConnectionFactory;
    public ModelWithChildrenScenario(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public int Create(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");

        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.Save(productLine, true);

        return productLine.Id;
    }

    public void Delete(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Delete<Product>(r => r.ProductLineId == productLine.Id);
            db.Delete(productLine);
        }
    }

    public void DeleteByKey(int productLineKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Delete<Product>(r => r.ProductLineId == productLineKey);
            db.DeleteById<ProductLine>(productLineKey);
        }
    }

    public IList<ProductLine> FindByName(string productLineName, bool includeProducts)
    {
        if (string.IsNullOrWhiteSpace(productLineName))
            throw new ArgumentNullException(nameof(productLineName), $"{nameof(productLineName)} is null or empty.");
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return includeProducts
                ? db.LoadSelect<ProductLine>(e => e.ProductLineName == productLineName)
                : db.Select<ProductLine>(e => e.ProductLineName == productLineName);
        }
    }

    public IList<ProductLine> GetAll(bool includeProducts)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return includeProducts
                ? db.LoadSelect<ProductLine>()
                : db.Select<ProductLine>();
        }
    }

    public ProductLine? GetByKey(int productLineKey, bool includeProducts)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            var products = includeProducts
                ? db.LoadSingleById<ProductLine>(productLineKey)
                : db.SingleById<ProductLine>(productLineKey);
            return products;
        }
    }

    public void Update(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.Update(productLine);
    }

    public void UpdateGraph(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.Save(productLine, true);
    }

    public void UpdateGraphWithChildDeletes(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Save(productLine, true);

            var productIdsToKeep = productLine.Products.Select(p => p.Id).ToList();

            db.Delete<Product>(p =>
                p.ProductLineId == productLine.Id && !Sql.In(p.Id, productIdsToKeep));
        }
    }

    public void UpdateGraphWithDeletes(ProductLine productLine, IList<int> productKeysToRemove)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $"{nameof(productLine)} is null.");
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Save(productLine, true);

            db.Delete<Product>(p =>
                p.ProductLineId == productLine.Id && Sql.In(p.Id, productKeysToRemove));
        }
    }

    public void Update(Product product)
    {
        if (product == null)
            throw new ArgumentNullException(nameof(product), $"{nameof(product)} is null.");
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.Update(product);
    }
}