C# Linq-SQL: An UpdateByID method for the Repository Pattern

Question

I have implemented a sort of Repository class and it has has GetByID, DeleteByID methods and so on, but I'm having trouble implementing the UpdateByID method.

I did something like this:

public virtual void UpdateByID(int id, T entity)
{
        var dbcontext = DB;
        var item = GetByID(dbcontext, id);
        item = entity; 
        dbcontext.SubmitChanges();
}

protected MusicRepo_DBDataContext DB
{
    get
    {
        return new MusicRepo_DBDataContext();
    }
}

But it's not updating the passed entity.

Has anyone implemented such a method ?

---

For reference, here is the GetByID method

---

[Update]

As Marc correctly suggested, I am merely changing the values of the local variable. So how do you think I should go about this method? Use reflection and copy the properties from entity to item ?

Answer 1

All you have updated is a local variable; for that to work you would have to copy the member values from entity to item - not quite so simple.

---

Something like below; the only reason I used TKey was that I tested on Northwind.Customer, which has a string key ;-p

The advantage of using the meta-model is that it works even if you are using POCO classes (and the xml-based mapping), and it doesn't try to update anything unrelated to the model.

For the purposes of the example, I have passed in the data-context, and you need to add a SubmitChanges at some point, but the rest should be directly comparable.

BTW - if you are happy to take the ID from the passed in object, that would be easy too - and then you could support composite identity tables.

    static void Update<TEntity>(DataContext dataContext, int id, TEntity obj)
        where TEntity : class
    {
        Update<TEntity, int>(dataContext, id, obj);
    }
    static void Update<TEntity, TKey>(DataContext dataContext, TKey id, TEntity obj)
        where TEntity : class
    {
        // get the row from the database using the meta-model
        MetaType meta = dataContext.Mapping.GetTable(typeof(TEntity)).RowType;
        if(meta.IdentityMembers.Count != 1) throw new InvalidOperationException("Composite identity not supported");
        string idName = meta.IdentityMembers[0].Member.Name;

        var param = Expression.Parameter(typeof(TEntity), "row");
        var lambda = Expression.Lambda<Func<TEntity,bool>>(
            Expression.Equal(
                Expression.PropertyOrField(param, idName),
                Expression.Constant(id, typeof(TKey))), param);

        object dbRow = dataContext.GetTable<TEntity>().Single(lambda);

        foreach (MetaDataMember member in meta.DataMembers)
        {
            // don't copy ID
            if (member.IsPrimaryKey) continue; // removed: || member.IsVersion
            // (perhaps exclude associations and timestamp/rowversion? too)

            // if you get problems, try using StorageAccessor instead -
            // this will typically skip validation, etc
            member.MemberAccessor.SetBoxedValue(
                ref dbRow, member.MemberAccessor.GetBoxedValue(obj));
        }
        // submit changes here?
    }

Answer 2

Well I have something like this (from the top of my head):

public Question UpdateQuestion(Question newQuestion)
    {
        using (var context = new KodeNinjaEntitiesDataContext())
        {
            var question = (from q in context.Questions where q.QuestionId == newQuestion.QuestionId select q).SingleOrDefault();
            UpdateFields(newQuestion, question);
            context.SubmitChanges();                
            return question;
        }
    }

    private static void UpdateFields(Question newQuestion, Question oldQuestion)
    {
        if (newQuestion != null && oldQuestion != null)
        {
            oldQuestion.ReadCount = newQuestion.ReadCount;
            oldQuestion.VotesCount = newQuestion.VotesCount;
            //.....and so on and so on.....
        }
    }

It works ok for simple entities. Of course if you have many entities you could use reflection.

Answer 3

If I'm understanding correctly, you shouldn't need reflection for this.

To do this for a specific entity you need to take your entity and attach it to the DB context. Once it is attached LINQ-to-SQL will determine what needs to be updated. Something along the lines of:

// update an existing member
dbcontext.Members.Attach(member, true);

// commit to database
dbcontext.SubmitChanges();

That would be for updating a Member in the Members table. The true argument says that you modified it. Alternatively if you had the original lying around you could pass that in as the second argument and let the DB context perform the diff for you. That is a major part of the DB context implementation (which implements the "Unit of Work" pattern).

To generalize this, you would replace the Member type with T, and replace the .Members with .GetTable:

public virtual void Update(T entity)
{
        var dbcontext = DB;
        dbcontext.GetTable<T>().Attach(entity, true);
        dbcontext.SubmitChanges();
}

Assuming that the ID is already set correctly on the entity (and that it is marked as a primary key in the model), you don't even need to look it up first. If you feel the need, you could look it up by ID and then pass it into the Attach method, but this probably just causes an extra lookup that isn't needed.

EDIT: You need to set UpdateCheck to Never on your columns in the model, otherwise it tries to perform concurrency checking. You will get a last update wins if you set it to Never. Otherwise you add tmestamp fields to your tables and the concurrency checking will determine if the entity is out of date or not.

UpdateCheck.Never combined with Attach(entity, bool) will be the simplest and most performant way to solve this with LINQ-to-SQL.

Answer 4

Hey dreas, I've also struggled with this and found a very elegant solution.

You essentially have to use the DataContext.Attach(EntityToUpdate,OriginalEntity) method.

There are a few gotchas...so, read this information, it will explain everything.

Once you've read it, just come back to me with any questions. I've written a really useful EntitySaver Class based on that info, so if you need, we can go through your Class once you get the gotchas.

cheers

EDIT: here's my Class in full, in case you want to try it out. It actually handles Updates and Inserts automatically. let me know of you have any questions.

Entity Saver:

    using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QDAL.CoreContext;
using QDAL.CoreEntities;
using LinqExtension.CustomExtensions;

namespace QDAL
{
    internal class DisconnectedEntitySaver
    {
        private QDataDataContext ContextForUpdate;

        public DisconnectedEntitySaver() {
            ContextForUpdate = Base.CreateDataContext();
        }

        public List<TEntityType> SaveEntities<TEntityType, TKeyType>(List<TEntityType> EntitiesToSave) {

            string PKName;

            PKName = Base.GetPrimaryKeyName(typeof(TEntityType), ContextForUpdate);

            return SaveEntities<TEntityType, TKeyType>(EntitiesToSave, PKName);
        }

        public List<TEntityType> SaveEntities<TEntityType, TKeyType>(List<TEntityType> EntitiesToSave, string KeyFieldName)
        {
            List<TEntityType> EntitiesToPossiblyUpdate;
            List<TEntityType> EntitiesToInsert;
            List<TEntityType> HandledEntities = new List<TEntityType>();

            bool TimeStampEntity;
            Type ActualFieldType;

            if (EntitiesToSave.Count > 0) {
                TimeStampEntity = Base.EntityContainsTimeStamp(typeof(TEntityType), ContextForUpdate);

                ActualFieldType = EntitiesToSave.FirstOrDefault().GetPropertyType(KeyFieldName);

                if (ActualFieldType != typeof(TKeyType)) {
                    throw new Exception("The UniqueFieldType[" + typeof(TKeyType).Name + "] specified does not match the actual field Type[" + ActualFieldType.Name + "]");
                }

                if (ActualFieldType == typeof(string)) {
                    EntitiesToPossiblyUpdate = EntitiesToSave.Where(ent => string.IsNullOrEmpty(ent.GetPropertyValue<string>(KeyFieldName)) == false).ToList();
                    EntitiesToInsert = EntitiesToSave.Where(ent => string.IsNullOrEmpty(ent.GetPropertyValue<string>(KeyFieldName)) == true).ToList();
                } else {
                    EntitiesToPossiblyUpdate = EntitiesToSave.Where(ent => EqualityComparer<TKeyType>.Default.Equals(ent.GetPropertyValue<TKeyType>(KeyFieldName), default(TKeyType)) == false).ToList();
                    EntitiesToInsert = EntitiesToSave.Where(ent => EqualityComparer<TKeyType>.Default.Equals(ent.GetPropertyValue<TKeyType>(KeyFieldName), default(TKeyType)) == true).ToList();
                }

                if (EntitiesToPossiblyUpdate.Count > 0) {
                    EntitiesToInsert.AddRange(ResolveUpdatesReturnInserts<TEntityType, TKeyType>(EntitiesToPossiblyUpdate, KeyFieldName));

                    HandledEntities.AddRange(EntitiesToPossiblyUpdate.Where(ent => EntitiesToInsert.Select(eti => eti.GetPropertyValue<TKeyType>(KeyFieldName)).Contains(ent.GetPropertyValue<TKeyType>(KeyFieldName)) == false));
                }

                if (EntitiesToInsert.Count > 0) {
                    ContextForUpdate.GetTable(typeof(TEntityType)).InsertAllOnSubmit(EntitiesToInsert);

                    HandledEntities.AddRange(EntitiesToInsert);
                }

                ContextForUpdate.SubmitChanges();
                ContextForUpdate = null;

                return HandledEntities;
            } else {
                return EntitiesToSave;
            }
        }

        private List<TEntityType> ResolveUpdatesReturnInserts<TEntityType, TKeyType>(List<TEntityType> PossibleUpdates, string KeyFieldName)
        {
            QDataDataContext ContextForOrginalEntities;

            List<TKeyType> EntityToSavePrimaryKeys;
            List<TEntityType> EntitiesToInsert = new List<TEntityType>();
            List<TEntityType> OriginalEntities;

            TEntityType NewEntityToUpdate;
            TEntityType OriginalEntity;

            string TableName;

            ContextForOrginalEntities = Base.CreateDataContext();

            TableName = ContextForOrginalEntities.Mapping.GetTable(typeof(TEntityType)).TableName;
            EntityToSavePrimaryKeys = (from ent in PossibleUpdates select ent.GetPropertyValue<TKeyType>(KeyFieldName)).ToList();

            OriginalEntities = ContextForOrginalEntities.ExecuteQuery<TEntityType>("SELECT * FROM " + TableName + " WHERE " + KeyFieldName + " IN('" + string.Join("','", EntityToSavePrimaryKeys.Select(varobj => varobj.ToString().Trim()).ToArray()) + "')").ToList();

            //kill original entity getter
            ContextForOrginalEntities = null;

            foreach (TEntityType NewEntity in PossibleUpdates)
            {
                NewEntityToUpdate = NewEntity;
                OriginalEntity = OriginalEntities.Where(ent => EqualityComparer<TKeyType>.Default.Equals(ent.GetPropertyValue<TKeyType>(KeyFieldName),NewEntityToUpdate.GetPropertyValue<TKeyType>(KeyFieldName)) == true).FirstOrDefault();

                if (OriginalEntity == null)
                {
                    EntitiesToInsert.Add(NewEntityToUpdate);
                }
                else
                {
                    ContextForUpdate.GetTable(typeof(TEntityType)).Attach(CloneEntity<TEntityType>(NewEntityToUpdate), OriginalEntity);
                }
            }

            return EntitiesToInsert;
        }

        protected  TEntityType CloneEntity<TEntityType>(TEntityType EntityToClone)
        {
            var dcs = new System.Runtime.Serialization.DataContractSerializer(typeof(TEntityType));
            using (var ms = new System.IO.MemoryStream())
            {
                dcs.WriteObject(ms, EntityToClone);
                ms.Seek(0, System.IO.SeekOrigin.Begin);
                return (TEntityType)dcs.ReadObject(ms);
            }
        }
    }
}

You'll need these helpers too:

    using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QDAL.CoreContext;
using QDAL.CoreEntities;
using System.Configuration;

namespace QDAL
{
    internal class Base
    {
        public Base() {
        }

        internal static QDataDataContext CreateDataContext() {
            QDataDataContext newContext;
            string ConnStr;

            ConnStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            newContext = new QDataDataContext(ConnStr);

            return newContext;
        }

        internal static string GetTableName(Type EntityType, QDataDataContext CurrentContext) {
            return CurrentContext.Mapping.GetTable(EntityType).TableName;
        }

        internal static string GetPrimaryKeyName(Type EntityType, QDataDataContext CurrentContext) {
            return (from m in CurrentContext.Mapping.MappingSource.GetModel(CurrentContext.GetType()).GetMetaType(EntityType).DataMembers where m.IsPrimaryKey == true select m.Name).FirstOrDefault();
        }

        internal static bool EntityContainsTimeStamp(Type EntityType, QDataDataContext CurrentContext) {
            return (CurrentContext.Mapping.MappingSource.GetModel(CurrentContext.GetType()).GetMetaType(EntityType).DataMembers.Where(dm => dm.IsVersion == true).FirstOrDefault() != null);
        }
    }
}

and these extensions make reflection easier:

<System.Runtime.CompilerServices.Extension()> _
    Public Function GetPropertyValue(Of ValueType)(ByVal Source As Object, ByVal PropertyName As String) As ValueType
        Dim pInfo As System.Reflection.PropertyInfo

        pInfo = Source.GetType.GetProperty(PropertyName)

        If pInfo Is Nothing Then
            Throw New Exception("Property " & PropertyName & " does not exists for object of type " & Source.GetType.Name)
        Else
            Return pInfo.GetValue(Source, Nothing)
        End If
    End Function

    <System.Runtime.CompilerServices.Extension()> _
    Public Function GetPropertyType(ByVal Source As Object, ByVal PropertyName As String) As Type
        Dim pInfo As System.Reflection.PropertyInfo

        pInfo = Source.GetType.GetProperty(PropertyName)

        If pInfo Is Nothing Then
            Throw New Exception("Property " & PropertyName & " does not exists for object of type " & Source.GetType.Name)
        Else
            Return pInfo.PropertyType
        End If
    End Function

Answer 5

Taking a fresh look here, previous answers to question made various assumptions about the application.

Concurrency within an application is something that needs to be thought about upfront and is something for which there isn't really a one-size-fits-all answer. Things to consider when making the choice for your application:

• LINQ to SQL / Entity Framework are very configurable because there isn't a one-size-fits-all.
• You aren't going to see the effect of concurrency until you have a certain load on your app (i.e. you, alone, at your own machine might never see it)
• How often does your application allow 2 (or more) users to edit the same entity?
• How do you want to handle the situation of when two edits overlap?
• Is your app serializing the data back and forth across another layer (e.g. Ajax)? If so then how do you know if the edited entity was modified between the read/update? Timestamp? Version field?
• Do you care if edits overlap? Pay special attention to FK relationships. Data integrity is where you can get bitten by last one wins.

Different solutions have very different performance implications! You won't notice as you're developing but your app may fall over when 25 people use it simultaneously. Watch for lots of copying back and forth and for many SQL reads:

• Don't call SQL in a loop (watch for this when you pass in a list of entities)
• Don't use reflection for this when you already have concurrency checking via LINQ
• Minimize the copying back and forth of fields (may be necessary when crossing N-Tier boundaries).
• Don't make a separate query to look up the old entity, (only use it if you have it lying around already) let LINQ do this as it is more optimized for doing it in SQL.

Here are some good links for deeper reading to decide your specific needs:

• Optimistic Concurrency Overview (LINQ to SQL, MSDN)
• Anti-Pattern: Mishandled Concurrency (June 2009, MSDN Magazine)

My recommended solution:

public virtual void Update(T entity)
{
    var DB = ...;
    DB.GetTable<T>().Attach(entity, true);
    try
    {
     // commit to database
     DB.SubmitChanges(ConflictMode.ContinueOnConflict);
    }
    catch (ChangeConflictException e)
    {
     Console.WriteLine(e.Message);
     foreach (ObjectChangeConflict occ in DB.ChangeConflicts)
     {
      occ.Resolve(REFRESH_MODE);
     }
    }
}

Where REFRESH_MODE specifies one of the following:

• RefreshMode.KeepChanges
• RefreshMode.KeepCurrentValues
• RefreshMode.OverwriteCurrentValues

You will also need to make some considerations about your model:

Probably goes without saying but you will need to let LINQ know which field is your primary key to update the entities. You don't have to pass this in as another param (as in your original method) because LINQ already knows this is the PK.

You get to (rather than "have to") decide which fields are actually checked. For instance a foreign key field is very important to have concurrency checking, whereas the description field probably deserves a last-one-wins. You control that via the UpdateCheck attribute. The default is UpdateCheck.Always. From MSDN:

Only members mapped as Always or WhenChanged participate in optimistic concurrency checks. No check is performed for members marked Never. For more information, see UpdateCheck.

To enable optimistic concurrency, you need to specify a field to use as the concurrency token (e.g. timestamp or version) and this field always has to be present when serializing back and forth. Mark this column with IsVersion=true.

If you don't want concurrency checking then you must mark all as UpdateCheck.Never.

Answer 6

I'm not very familiar with repository patterns but what if you delete the old entity from the database, and put the new entity in the database with the same ID? something like this:

public virtual void UpdateByID(int id, T entity)
{
    DeleteByID(id);
    var dbcontext = DB;
    //insert item (would have added this myself but you don't say how)
    dbcontext.SubmitChanges();
}

Answer 7

I had some similar issues and ended up going with PLINQO, lots of enhancements to the LINQ-TO-SQL generated code. However it does require a CodeSmith purchase (although free to eval for 30 days) if you don't already have it.