Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / C#
Technical Blog

Helper Function to Update/Insert/Delete Collections in Linq2Sql

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
20 Jul 2013CPOL 7.8K   3  
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 ...

C#
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:

C#
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:

C#
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:

C#
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Australia Australia
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --