The DataContext keeps track of the state of business entities in LINQ To SQL, but many times the original DataContext is not available for updates and hence state information and changes made to the business entity is lost.

This situation is common in an n-tier architecture where the business entity is serialized and sent across tiers. The entity is disconnected from the original data context and no change tracking is possible.

The Attach Method on the DataContext in LINQ To SQL allows you to re-attach an existing business entity that came from the database as well as information about the original state of the entity to help with optimistic concurrency.

The Attach method is provided on the ITable instance that supposedly allows re-attach an instance to be change tracked. Unfortunately this doesn’t work quite as expected.

The following code example illustrates:

    public partial class AttachDemo

    {

        public static void DetachDemo()

        {

 

            string userName = “mytestuser”;

            // Creates a user to use in demo.

            CreateDemoUser(userName);

 

            User user;

 

            // SCENARIO 1

 

            using (LinqToSQLDataClassesDataContext db =

                new LinqToSQLDataClassesDataContext())

            {

                user = db.Users.Single(u => u.Username == userName);

                // Simulate detachment

                user = Detach<User>(user);

            }

 

            // Make changes to the detached user.

            user.Password = “s1password”;

 

            try

            {

                Scenario_1_FailsToUpdateUser(user);

            }

            catch (Exception exp)

            {

                Console.WriteLine(

                    “FailsToUpdateUser method exception: {0}”,

                    exp.Message);

            }

 

 

            // SCENARIO 2

 

            using (LinqToSQLDataClassesDataContext db2 =

                new LinqToSQLDataClassesDataContext())

            {

                user = db2.Users.Single(u => u.Username == userName);

                // Simulate detachment

                user = Detach<User>(user);

            }

            // Make changes to the detached user.

            user.Password = “s2password”;

            // Update the detached user.

            Scenario_2_UpdateUser(user);

 

 

            // SCENARIO 3

 

            User originalUser;

 

            using (LinqToSQLDataClassesDataContext db3 =

                new LinqToSQLDataClassesDataContext())

            {

                user = db3.Users.Single(u => u.Username == “mytestuser”);

                // originalUser = Detach<User>(user);

                originalUser = db3.Users.GetOriginalEntityState(user);

                // Simulate detachment

                user = Detach<User>(user);

                // This will simulate a situation where we hold original

                //  state in some server side state cache or on the client tier

                //  itself and thus is detached also.

                originalUser = Detach<User>(originalUser);

            }

            // Make changes to the detached user.

            user.Password = “s3password”;

            // Update the detached user.

            Scenario_3_UpdateUser(user, originalUser);

 

 

        }

 

        // Serializes and deserializes the user. This has the effect

        //  of detaching the user from the data context as would happen

        //  in an n-tier application for example where the entities would

        //  be serialized over the wire.

        public static T Detach<T>(T entity)

        {

            DataContractSerializer dcs =

                new DataContractSerializer(typeof(T));

            Stream mstream = new MemoryStream();

            dcs.WriteObject(mstream, entity);

            mstream.Position = 0;

            return (T)dcs.ReadObject(mstream);

        }

 

        public static void Scenario_1_FailsToUpdateUser(User user)

        {

            using (LinqToSQLDataClassesDataContext dc =

                new LinqToSQLDataClassesDataContext())

            {

                dc.Log = Console.Out;

                User orgUser;

                orgUser = dc.Users.Single(u => u.Username == user.Username);

                dc.Users.Attach(user, orgUser);

                Console.WriteLine(dc.GetChangeSet());

                dc.SubmitChanges();

            }

        }

 

        public static void Scenario_2_UpdateUser(User user)

        {

            using (LinqToSQLDataClassesDataContext dc =

                new LinqToSQLDataClassesDataContext())

            {

                dc.Log = Console.Out;

                User orgUser;

                using (LinqToSQLDataClassesDataContext dca =

                    new LinqToSQLDataClassesDataContext())

                {

                    // dca.ObjectTrackingEnabled = false; // May save some overhead

                    orgUser = dca.Users.Single(u => u.Username == user.Username);

                }

 

                dc.Users.Attach(user, orgUser);

                Console.WriteLine(dc.GetChangeSet());

                dc.SubmitChanges();

            }

        }

 

        public static void Scenario_3_UpdateUser(User user, User originalUser)

        {

            using (LinqToSQLDataClassesDataContext dc =

                new LinqToSQLDataClassesDataContext())

            {

                dc.Log = Console.Out;

                dc.Users.Attach(user, originalUser);

                Console.WriteLine(dc.GetChangeSet());

                dc.SubmitChanges();

            }

        }

 

        public static User GetUser(string username)

        {

            using (LinqToSQLDataClassesDataContext dc =

                new LinqToSQLDataClassesDataContext())

            {

                return dc.Users.Single(u => u.Username == username);

            }

        }

    }

The public static T Detach<T>(T entity) method ensures that the entity passed in is detached from its data context by serializing it and then de-serializing it in memory using the DataContractSerializer class from the System.Runtime.Serialization namespace. This is what would generally happen in an n-tier architecture when entities are passed from tier to tier over the network. The important thing here is that they will no longer be attached to the originating data context and, of course, that originating data context can no longer track changes.

The Scenario_1_FailsToUpdateUser method

Here we create a new data context and query it for the user that we want to update, in order to get the current state of the user in the database. This is then passed into the second parameter (“original”) of the Attach method. The first parameter is the updated user.

What we want here, of course, is for the data context to compare the updated entity (first parameter) with the original (second parameter) and generate SQL accordingly.

However, The Scenario_1_FailsToUpdateUser method throws an exception.

The pertinent console output here is:

FailsToUpdateUser method exception: Cannot add an entity with a key that is already in use.

 

As you can see we get the exception “Cannot add an entity with a key that is already in use.” thrown by the dc.Users.Attach(user, orgUser) call.

 

I suspect that this is a bug, since one would expect the Attach(TEntity entity, TEntity original) method to work in such a scenario. The error seems to be caused by the very fact that I have executed the query:

orgUser = dc.Users.Single(u => u.Username == user.Username)

in order to get the current state to pass into the second parameter of the attach method – all within the same data context.

 

If we simply call the dc.Users.Attach(user) overload, the same error will occur.

 

Calling dc.Users.Attach(user, true) (asModified=true) produces the exception:

 

“An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy.”

 

This we would expect.

 

Calling dc.Users.Attach(user, false) (asModified=false) again produces the exception:

 

“Cannot add an entity with a key that is already in use.”

 

Calling dc.Users.Attach(user, false) (asModified=false) without executing the preceding query produces no exception, but does not update the database!

 

The Scenario_2_UpdateUser method

The difference between the Scenario_1_FailsToUpdateUser method and the Scenario_2_UpdateUser method, which works, is simply that the query for the current state is executed on a separate data context. The console output for this method is:

 

{Inserts: 0, Deletes: 0, Updates: 1}

UPDATE [dbo].[Users]

SET [Password] = @p2

WHERE ([Username] = @p0) AND ([Password] = @p1) AND ([Active] = 1)

 

As we can see it has successfully worked out that we need an update and produces the minimal SQL to perform the task.

The Scenario_3_UpdateUser method

This method works.

This method differs from Scenario_2_UpdateUser method in that it requires an original user entity to be passed in, in addition to the updated entity.

The original user entity passed in is generated by the original data context:

                originalUser = db3.Users.GetOriginalEntityState(user);

                // Simulate detachment

                user = Detach<User>(user);

 

In this case there is no significance to using the GetOriginalEntityState method. I could equally have written:

originalUser = Detach<User>(user);

which would return a copy of the user. There is no significance to the fact that the originalUser was generated by the original context.

This scenario would be typical of an architecture where the original state is stored on the client and returned along with changes to the data tier.

So the reason that Scenario_3_UpdateUser method works is the same as the reason the Scenario_3_UpdateUser method works. It is by virtue of the fact that the original state is not obtained by querying on the same data context that you want to attach it to for updating.

 

This does seem odd. Any enlightenment on why this is would be welcome! Microsoft…..?

 

 

Tags: Attach, Concurrency, DataContext, Linq, LINQ to SQL

This entry was posted on Sunday, June 29th, 2008 at 8:11 pm and is filed under Data Platform, Linq. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.