This post is written by guest blogger Albin Sunnanbo. He’s a great friend and source of inspiration and also one of the best developers I know. It was Albin that first introduced me to LINQ, EF Migrations and jQuery that I now use daily.
I often find myself in situations where I need to update a collection property on some object in LINQ2SQL to match another collection property. Usually I get those collections to my data layer either from an import or from a complex update in the UI (via my business layer).
The most straight forward way to solve this is to clear all old items in the collection in the database and generate new items. Sometimes this is not possible when you have foreign key constrains and rows in other tables referring your collection items.
Many times I have written code where I try to figure out what items that are new, deleted or just updated and process them accordingly.
Today I finally wrote an extension method that encapsulates this pattern.
public static class LinqCollectionUpdate { /// <summary> /// Updates an EntitySet with matching items from another collection of business items /// Adds new items, deletes missing items and updates all new and remaining items /// /// See example usage at http://coding.abel.nu/2012/07/updating-entitysets-with-linq2sql/ /// </summary> /// <typeparam name="TDatabaseEntity"></typeparam> /// <typeparam name="TBusinessEntity"></typeparam> /// <typeparam name="TKey">A common key type used to compare TDatabaseEntity and TBusinessEntity</typeparam> /// <param name="databaseEntities">The EntitySet to update</param> /// <param name="databaseTable">The table where EntitySets should be removed</param> /// <param name="businessEntities">A list of items merge with the <paramref name="databaseEntities"/></param> /// <param name="databaseKeyGenerator">A method to generate a unique key from a TDatabaseEntity</param> /// <param name="businessKeyGenerator">A method to generate a unique key from a TBusinessEntity</param> /// <param name="createNewDatabaseEntityMethod">A method to generate a new TDatabaseEntity with initial data set</param> /// <param name="updateMethod">A method to update a new or existing TDatabaseEntity from a TBusinessEntity</param> /// <param name="deleteMethod">A cleanup method called before deleting a TDatabaseEntity. /// Use this method to clean up nested tables with foreign keys.</param> public static void MergeCollections<TDatabaseEntity, TBusinessEntity, TKey>( this EntitySet<TDatabaseEntity> databaseEntities, Table<TDatabaseEntity> databaseTable, IList<TBusinessEntity> businessEntities, Func<TDatabaseEntity, TKey> databaseKeyGenerator, Func<TBusinessEntity, TKey> businessKeyGenerator, Func<TBusinessEntity, TDatabaseEntity> createNewDatabaseEntityMethod = null, Action<TDatabaseEntity, TBusinessEntity> updateMethod = null, Action<TDatabaseEntity> deleteMethod = null) where TDatabaseEntity : class, new() where TBusinessEntity : class { HashSet<TKey> databaseKeys = new HashSet<TKey>(from dbItem in databaseEntities select databaseKeyGenerator(dbItem)); HashSet<TKey> businessKeys = new HashSet<TKey>(from businessItem in businessEntities select businessKeyGenerator(businessItem)); // Remove missing entites foreach (TDatabaseEntity dbItem in databaseEntities .Where(e => !businessKeys.Contains(databaseKeyGenerator(e))).ToList()) { if (deleteMethod != null) { deleteMethod(dbItem); } databaseTable.DeleteOnSubmit(dbItem); databaseEntities.Remove(dbItem); } // Add placeholders for new entites foreach (TBusinessEntity newItem in businessEntities .Where(newLine => !databaseKeys.Contains(businessKeyGenerator(newLine))).ToList()) { if (createNewDatabaseEntityMethod != null) { databaseEntities.Add(createNewDatabaseEntityMethod(newItem)); } else { databaseEntities.Add(new TDatabaseEntity()); } } // Update existing and new entites var updateItems = from newItem in businessEntities join dbItem in databaseEntities on businessKeyGenerator(newItem) equals databaseKeyGenerator(dbItem) select new { newItem, dbItem }; foreach (var updateItem in updateItems.ToList()) { if (updateMethod != null) { updateMethod(updateItem.dbItem, updateItem.newItem); } } } } |
The usage is pretty straight forward, using the AdventureWorks database.
Throw in an EntitySet, like category.ProductSubcategories and the matching table in the DataContext (required for delete).
Give a reference list of business objects.
Give a method for creating a key from both business and data objects. Those keys are used to match business objects with database objects to find out what objects that are new, updated or deleted.
Create update, new and delete methods. Note that the new method does not need to set any values except initial values as the updateMethod is called on each new object after the createNewDatabaseEntityMethod is called.
using (var context = new AdventureWorksDataContext()) { var category = context.ProductCategories.FirstOrDefault(c => c.Name == "My test category"); if (category == null) { category = new ProductCategory { Name = "My test category", ModifiedDate = DateTime.Now, }; context.ProductCategories.InsertOnSubmit(category); } Console.WriteLine(category.Name); foreach (var subCat in category.ProductSubcategories) { Console.WriteLine("- " + subCat.Name.PadRight(25, ' ') + subCat.rowguid); } category.ProductSubcategories.MergeCollections( context.ProductSubcategories, new[]{ "My test subcategory 1", //"My test subcategory 2", "My test subcategory 3", "My test subcategory 4", }, dbItem => dbItem.Name, listItem => listItem, listItem => new ProductSubcategory { Name = listItem, rowguid = Guid.NewGuid() }, (dbItem, listItem) => { dbItem.ModifiedDate = DateTime.Now; } ); Console.WriteLine("After update"); foreach (var subCat in category.ProductSubcategories) { Console.WriteLine("- " + subCat.Name.PadRight(25, ' ') + subCat.rowguid); } context.SubmitChanges(); } Console.ReadKey(); |
This gives the output
My test category - My test subcategory 1 cb3dde89-7036-4556-beae-75af64887405 - My test subcategory 2 3c431004-1787-422a-b906-d7351010247e - My test subcategory 3 9ec94df7-4672-429d-9329-f3714d9b1ed2 After update - My test subcategory 1 cb3dde89-7036-4556-beae-75af64887405 - My test subcategory 3 9ec94df7-4672-429d-9329-f3714d9b1ed2 - My test subcategory 4 3de7c063-ae79-4047-a516-40e28daf1439
If I want to match on the rowguid instead I use item.rowguid in the key generators and I also need to include the GUID in my business objects. I use anonymous types for brevity here, but in a real application this is typically a DTO class or a class from your business layer.
category.ProductSubcategories.MergeCollections( context.ProductSubcategories, new[]{ new{Name = "My test subcategory 1", rowguid = Guid.Parse("cb3dde89-7036-4556-beae-75af64887405")}, //new{Name = "My test subcategory 2", // rowguid = Guid.Parse("3c431004-1787-422a-b906-d7351010247e")}, new{Name = "My test subcategory 3", rowguid = Guid.Parse("9ec94df7-4672-429d-9329-f3714d9b1ed2")}, new{Name = "My test subcategory 4", rowguid = Guid.Parse("3c431004-1787-422a-b906-d7351010247e")}, }, dbItem => dbItem.rowguid, listItem => listItem.rowguid, listItem => new ProductSubcategory { Name = listItem.Name, rowguid = listItem.rowguid }, (dbItem, listItem) => { dbItem.ModifiedDate = DateTime.Now; dbItem.Name = listItem.Name; } ); |
This gives the output
My test category - My test subcategory 1 cb3dde89-7036-4556-beae-75af64887405 - My test subcategory 2 3c431004-1787-422a-b906-d7351010247e - My test subcategory 3 9ec94df7-4672-429d-9329-f3714d9b1ed2 After update - My test subcategory 1 cb3dde89-7036-4556-beae-75af64887405 - My test subcategory 4 3c431004-1787-422a-b906-d7351010247e - My test subcategory 3 9ec94df7-4672-429d-9329-f3714d9b1ed2
For nested collections MergeCollection can be called on the subcollections in the updateMethod.
If you don’t use cascading deletes, implement the delete of nested collections in the deleteMethod.
The code above can be installed using the NuGet package LINQ2SQL.Collection.Update