Helper Function to Update/Insert/Delete Collections in Linq2Sql

Introduction

Pretty often, we have to save a master object with a collection of children.

Given the following model of Person with multiple Phones ...

public class Phone
{
    public int Id { get; set; } // primary key
    public string Number { get; set; }
    public string Note { get; set; }
}

public class Person
{
    public int Id { get; set; } // primary key
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public List<Phone> Phones { get; set; }
}

... code that saves person might look like this:

public void Save(Person person)
{
    ... save person object first
    ... this code is not shows for simplicity

    // get phones currently saved in db
    var phonesInDb = DataContext.Phone
        .Where(p => p.PersonId == person.Id)

        .ToList();
    // delete phones

    var toDelete = phonesInDb
        .Where(p => !person.Phones.Any(h => h.Id == p.Id))
        .ToList();

    DataContext.Phone.DeleteAllOnSubmit(toDelete);

    // insert phones
    var toInsert = person.Phones
        .Where(p => !phonesInDb.Any(h => h.Id == p.Id))
        .Select(p => new Db.Phone
        {
            PersonId = person.Id,
            Number = p.Number,
            Note = p.Note
        })
        .ToList();

    DataContext.Phone.InsertAllOnSubmit(toInsert);
    // update phones
    foreach (var dbPhone in phonesInDb)
    {
        var newPhone = person.Phones.Where(p => p.Id == dbPhone.Id).SingleOrDefault();
        if (newPhone != null)
        {
            newPhone.Number = newPhone.Number;
            newPhone.Note = mod.Note;
        }
    }
    // 
    DataContext.SubmitChanges();
}

The problem here is that this code is pretty long and you might have to duplicate it for all child/parent objects in your project.
The code can be much shorter:

public void Save(Person person)
{
    ... save person object first
    ... this code is not shows for simplicity
    // get phones currently saved in db
    var phonesInDb = DataContext.Phone
        .Where(p => p.PersonId == person.Id)
        .ToList();
    // insert, update and delete
            Ctx.InsertUpdateDelete(person.Phones, phonesInDb,
                                    (m, d) => (m.Id == d.Id),
                                    (m, d) =>
                                        {
                                            d.PersonId = person.Id;
                                            d.Number = m.Number;
                                            d.Note = m.Note;
                                        });

    // 
    DataContext.SubmitChanges();
}

If you add the following generic function to your DataContext class:

public partial class DataContext
{
    public void InsertUpdateDelete<TModel, TDatabase>
    (IEnumerable<TModel> mod, IEnumerable<TDatabase> db, 
    Func<TModel, TDatabase, bool> match, Action<TModel, 
    TDatabase> convert) where TDatabase : class, new()
    {
        var table = this.GetTable<TDatabase>();
        // delete
        var toDelete = db.Where(d => !mod.Any(m => match(m, d)));
        table.DeleteAllOnSubmit(toDelete);
        // insert
        var toInsert = mod
            .Where(m => !db.Any(d => match(m, d)))
            .Select(m =>
                        {
                            var newDb = new TDatabase();
                            convert(m, newDb);
                            return newDb;
                        });

        table.InsertAllOnSubmit(toInsert);
        // update
        foreach (var d in db)
        {
            var m = mod.Where(x => match(x, d)).SingleOrDefault();
            if (m != null) convert(m, d);
        }

        // note: it does not call SubmitChanges!
    }
}

The good thing is that you can use InsertUpdateDelete for all parent/child relations in your project.

mod and db are collection of model and database objects, database objects are updated/deleted/inserted based model objects.

match is that function that finds corresponding pairs of model and db objects.

convert is the translation from model into db object.