February 2009 Archives

Here’s the skinny:  Don’t get into a situation where you have to update an object from a disconnected DataContext.  You will fail, and miserably.

Sure, you can do some Googling and find some resources on the subject, and even find some slightly tolerable solutions.. but even those require some nasty stuff, some hard coding of methods in the DataContext class itself, and/or adding versioning field to your database table.

Google:  “linq disconnected updates” and “LINQ Update Disconnected Entity Objects”

The best solution that I found, that worked for me – was a bit more of tightly coupling what was happening, and passing around an open DataContext from start-to-finish through the various methods.

It seems to me that the best way would be something hypothetical like this:

[your]DataContext.Update<[YourTable]>([YourEntityObject]);
[your]DataContext.SubmitChanges();

Of course, that “Update” method doesn’t exist, only the “Attach” method that seems like it should do what we want, but doesn’t really.  The “Update” method of course, would update using only the primary key of the entity to perform the update.  I suppose I see why this might not exist due to complications with child entity objects.

The other alternative AFAIK is to create a CopyFromEntityToEntity extension method or class method to copy from the object you wish to update, to an object acquired from the database during the update method.  Something like the following:

//Warning, this is pseduo-code and will not work for various reasons and should be a generic method.  This also assumes the PK is a GUID[uniqueidentifier] field.  There is no error checking, etc.
void Update(orders_mainTable updateEntity, Guid identifier) {
  myDataContext dc = new myDataContext();
  
  var original = dc.Table.Where(o => o.Guid == identifier).SingleOrDefault();
  if (original == null)
    throw new Exception(“Original Entry in Database Not Found.”);

  // Some Extension Method?
  original.CopyFrom(updateEntity);

  // Update the database
  dc.SubmitChanges();
}

The CopyFrom method would iterate over the reflected values of the objects and copy the data from the “updateEntity” object to the original object.  This way, you’re not going to run into any issues issuing an update to a 'disconnected’ object.

Good luck!  This one sure makes me mad though.. LINQ-to-SQL should be smart enough to take care of this for [us] automatically in my opinion.  This is going to be a huge issue for n-tier disconnected architectures.  Instead of being able to fully take advantage of X,Y,Z those portions that do the updating will have to go through hoops like the example above to be able to update something.

Note:  My example above does not address child objects that may be stored along with the entity.

P.S.  I absolutely love LINQ [with the exception of this!].  I had to re-write a substantial part of the application I am working on after some major database changes for features requested by the client.. well, I made a decision to replace portions of code that were already written that needed to be update with LINQ-To-SQL stuff.. and wow!  I was able to write the new LINQ-based code with more features than the original code had.. and implement it faster than I would have been able to modify the existing code to update it to the new requirements. 

Wow.

Further Reading: 

Rick Strahl has some interesting information on this topic as well but offers some more detail in the suggestions such as adding a timestamp field, etc.
Link:  LINQ to SQL and Disconnected Entities Follow-up

Omar Al Zabir has some ideas as well.
LINK:  Linq to SQL: How to Attach object to a different data context

Here are some other generic Google results that I found during research:
http://msdn.microsoft.com/en-us/bb546187.aspx

http://geekswithblogs.net/michelotti/archive/2007/12/30/118076.aspx

http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/6f42d725-4540-4044-be86-afc7bc2d2b46/

http://stackoverflow.com/questions/273578/linq-to-sql-disconnected-updating-object-from-different-data-context

Thanks!

Matthew MacSuga

I choose to love it.

Here is a post that I posted on Stack Over Flow, asking for some assistance in generating the following query seen below: 

Complicated query with aggregate data for a report from multiple tables for an ordering system

from pmt in products_mainTable
join opt in orders_productsTable on pmt.guid equals opt.products_mainTableGUID into tempProducts
from orderedProducts in tempProducts.DefaultIfEmpty()
join omt in orders_mainTable on orderedProducts.orders_mainTableGUID equals omt.guid into tempOrders
from ordersMain in tempOrders.DefaultIfEmpty()
group pmt by new { pmt.sku, orderedProducts.color, orderedProducts.size } into g
orderby g.FirstOrDefault().sku
select new {
    g.FirstOrDefault().guid,
    g.Key.sku,
    g.Key.size,
    QTY = g.FirstOrDefault().orders_productsTable.Sum(c => c.qty),
    SUM = g.FirstOrDefault().orders_productsTable.Sum(c => c.itemprice * c.qty),
    AVG = g.FirstOrDefault().orders_productsTable.Average(c => c.itemprice * c.qty),
    Some = g.FirstOrDefault().orders_productsTable.Average(p => p.qty).GetValueOrDefault(0),
};

I’ve got to say that working with LINQ-TO-SQL has been an absolute pleasure.  I even fired up LINQPad and have really been enjoying the use of that program.  The $19 for the IntelliSense was definitely worth it.  What a great tool.

I can now take the query above into my code, and render it into reports, grids, etc. and whatever else I need it for using my DevExpress framework.  Exciting stuff, to be sure.

I’ll write some more on this topic when I have some additional time, but since I had a difficult time Googling for the answer on my Stackoverflow question, I thought I’d write about it here.

The above represents how to do a full LEFT OUTER JOIN in LINQ and C# while performing GROUPING and AGGREGATE functions.  Hopefully this will be a good starting place for someone else looking to do the same thing.

The important bits here are DefaultIfEmpty() which helps to perform the LEFT OUTER JOIN and FirstOrDetaulf() which allows you access to the underlying type.  While I knew about DefaultIfEmpty() I did not know about FirstOrDefault().. using FoD was the real key to making this query work the way that I wanted it to.

Now I am able to quickly and easily query my sales data.  This query for instance queries the products table, finds products that have sold and generates a quick report that I can use as a DataSource for other components.

Viva la LINQ!

Matthew MacSuga