DataContext.GetChangeSet to find what objects have changed

When you make changes to object, delete or insert new objects, linq to SQL is tracking the changes and based on the original values, it generates the appropriate SQL statements. DataContext exposes a method called GetChangeSet that contains all the inserted, updated and deleted objects that will be sent to the database when SubmitChanges gets called. You basically have to loop through each collection and find out what objects have been effected.  If you want to know exactly what SQL statement would be sent to the database their is a private method called GetChangeText that you can call using reflection to find out the exact SQL statement that would be sent to the database. Example below shows a change set that contains few updated objects, an inserted object and an object marked for deletion.

image

image

In the above example, I am modifying the objects that ends up generating 1 insert, two updates and 1 delete statement as shown in the output window.

First Update is the result of Country for the customer ALFKI being modified. 2nd Update is the result of moving 1st order of ALFKI customer and assigning to QUEDE customer.

Insert is the result of an order being added to QUEDE customer and delete is the result of  BOTT customer marked for deletion. Basically the above code demonstrate that GetChangeSet contains all the objects modified during the life time of the DataContext.  Furthermore, I am also printing the all the SQL statements that is sent to the database as a result of the objects being modified, inserted and Deleted. I am making use of GetChangeText which happens to be a private method not exposed by the DataContext. In order to use that method, I am making use of reflection in my code.

3 Comments

  • Hi z,

    Many thanks for that. It is just what I wanted, except I am working in VB.NET (using VWD Express 2008).

    Could you point me to the correct (VB.NET) syntax for your penultimate line of code?
    changenext = ....

    I am not sufficiently familiar with Reflection (yet).

    Also in your code, you have created a new NorthWindDataContext.
    How can I hookup these traces to the datacontext that is being defined and used declaratively (with a formview )in the .aspx markup?

    Thanks,
    Steve

  • Just so that anyone else who finds this page can copy and paste the excellent line of code using reflection to extract the SQL statement that will be run to apply the changeset (assuming your DC instance is named "db", and includes full qualification of BindingFlags so you can do it in the Immediate Window):

    db.GetType().GetMethod("GetChangeText", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic).Invoke(db, null);

  • i am using Vs 2010 .
    After lots of stuff find solution.

    using (var context = new RegistrationByLinqEntities())
    {
    var valtemp = context.FornDatas.SingleOrDefault(a => a.id == id);
    valtemp.Approve = app;
    valtemp.CreateDate = DateTime.Now;
    context.SaveChanges();
    }

Comments have been disabled for this content.