Tuesday, April 22, 2008

Handling concurrency conflicts in LINQ to SQL

Apologies for the delay in getting this post out, been busy and been playing around with too many good things lately. Virtual machines, Unit Testing etc, recording some radio from the net etc. Anyways, I have been planning to write about my experiences with Conflict handling in Linq2Sql, what happens when two users want to update the infamous Product table in Northwind.

When I started playing around with it I also discovered that I needed to investigate a bit more about DataContexts, so I went reading around the web to gather info, did some experiments in my machine etc and interestingly enough I had a very related question at work where a colleague asked about the what are best practises when instantiating them. He had a static DataContext to be shared amongst his data access for a particular entity. My opinion on this is that DataContexts are cheap and light to create and that creating them for a single unit of work I do say in my GetProductByID function should be good enough. I'll say that persisiting DataContexts would create a bit of an overhead when I think that it has to keep all the mappings and ObjectTracking etc ( mind due, you can turn off ObjectTracking if you are going to do read only..). Also DCxt are not thread safe so that's another big reason not to do that. But when I started Linqing2Sql I did entertain the idea of sharing my DataContext through a singleton. Hrmm.

public voidTurnOffObjectTracking()
{
   var dal = newProductDataContext();
   // If you don't want to update stuff, don't track them.
 
dal.ObjectTrackingEnabled = true;           
   Productprod = dal.Products.SingleOrDefault(a => a.ProductID == Guid.NewGuid());
   Assert.IsNull(prod);
}

Let me not get sidetracked, it's 10:30pm and I want to go to sleep, back to our interleaving..

First of all, let's recreate the case where <  1 users are updating a record and our sys throws up a smelly. But before I do that, let's keep in mind that DataContexts are neat, I mean sometimes too neat since some stuff happens behind the scenes kind of magic, but that maybe just me :-(

For example, note how in the example below two products are obtained from the db and one of them is upated, and then without committing this change to storage, I get another instance of the same product, then I commit the original change and then compare the Products....

[TestMethod]
public void Test_DataContext_Product_Independence()
{
    using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
    {
        var linq = new ProductDataContext();
        linq.Log = Console.Out;

        Guid productID = new Guid("50BD6DEB52C14D65BEC268957601EF25");
        
        // Get a Product with the ID as above
        Product product_1 = linq.Products.Single(p => p.ProductID == productID);
        
        // check that the Prod Name is as expected..
        Assert.AreEqual("Tourtière", product_1.ProductName);
        // Update the Prod Name
        product_1.ProductName = "Côte de Tourtière";
        // Let's retrieve the product again, note that the update above has not been committed to the database
        Product product_2 = linq.Products.Single(p => p.ProductID == productID);
        
        // Now commit!
        linq.SubmitChanges();

        // Note that the new name of Product2...obtained from DataContext Memory, before I committed the change! neat!
        Assert.AreEqual("Côte de Tourtière", product_2.ProductName);               
    }
}

firstTestPassed

OK, so to simulate interleaving, we'll simulate the two users trying to update a record in the db. The record look like below

ProductID / ProductName / UnitPrice

 455984DB-92DD-4BA4-87D7-91E4E2E5E00D    Camomile Tea    10.00    ....

Now our two unsuspecting users are going to try to update the Product Name for this product

[TestMethod]
public void RecreateInterleavingCondition()
{
    var user1DAL = new ProductDataContext();
    var user2DAL = new ProductDataContext();

    Guid pID = new Guid("455984db-92dd-4ba4-87d7-91e4e2e5e00d");
    Product user1Product = user1DAL.Products.Single(p => p.ProductID == pID);
    user1Product.ProductName = "Royal Camomile Tea";
    Console.WriteLine(user1Product.ProductName);

    Product user2Product = user2DAL.Products.Single(p => p.ProductID == pID);
    user2Product.ProductName = "Royal Camomile Tigris Tea";
    Console.WriteLine(user1Product.ProductName);

    user1DAL.SubmitChanges();
    Console.WriteLine(user2Product.ProductName);
    user2DAL.SubmitChanges();
}

Run the above results in: Conflict Exception!

interleavingException

User1 retrieves our Product and changes the name to "Royal Camomile Tea", not Submitting the changes, now User2 gets in there, retrieves the record changes to "Royal Camomile Tigris Tea", but User1 submits his changes and then User2 submits her changes. End result Conflict!

Now...How does LINQ to SQL handles it?

L2SQL uses what is called Optimistic concurrency is based on the un-healthy assumption that db transactions won't conflict with other transactions, that basically the chances of two users at exactly the same time wont happen that often. The alternative is pessimistic concurrency, this is where users will retrieve a record from the database and hold a lock on that record/table to prevent other pesky users to change that, problem is that is not scalable at all, systems grew larger and records where locked for ages before the sys will release the handle on it, also the emergence of the Web, the demands of stateles apps made it totally unfeasible for the pessimistic model.

LINQ to SQL uses Optimistic concurrency, what it does is that when updating a record, it checks the previous values of that record, it the values where unchanged, then is all good, otherwise it will complain and it will throw an exception and then you can handle it and choose how to handle the conflict.

In effect, if you turn Log on when Updating a record, and see the query passed onto the db you'll see that all values will be passed in the WHERE clause to compare against the columns in your table, not only the ID for the record. See below for the updates we tried to do above. MSDN has a good article on LINQ to SQL and Optimistic conc here

Royal
UPDATE [dbo].[Product]
SET [ProductName] = @p5
WHERE ([ProductID] = @p0) AND ([ProductName] = @p1) AND ([UnitPrice] = @p2) AND ([CategoryID] = @p3) AND ([CreatedDate] = @p4)
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [455984db-92dd-4ba4-87d7-91e4e2e5e00d]
-- @p1: Input VarChar (Size = 12; Prec = 0; Scale = 0) [Camomile Tea]
-- @p2: Input Money (Size = 0; Prec = 19; Scale = 4) [10.0000]
-- @p3: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [c9735b53-9c82-4f11-891a-8a53dc89565d]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [17/04/2242 11:07:05 PM]
-- @p5: Input VarChar (Size = 5; Prec = 0; Scale = 0) [Royal]

Royal Tigris
UPDATE [dbo].[Product]
SET [ProductName] = @p5
WHERE ([ProductID] = @p0) AND ([ProductName] = @p1) AND ([UnitPrice] = @p2) AND ([CategoryID] = @p3) AND ([CreatedDate] = @p4)
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [455984db-92dd-4ba4-87d7-91e4e2e5e00d]
-- @p1: Input VarChar (Size = 12; Prec = 0; Scale = 0) [Camomile Tea]
-- @p2: Input Money (Size = 0; Prec = 19; Scale = 4) [10.0000]
-- @p3: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [c9735b53-9c82-4f11-891a-8a53dc89565d]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [17/04/2242 11:07:05 PM]
-- @p5: Input VarChar (Size = 12; Prec = 0; Scale = 0) [Royal Tigris]

DataContext implements Optimistic by default, but you can turn it off, imagine that it passed lots of parameters, this will certainly cause performance dramas or you simply could streamline it. You can fix your mappings with the UpdateCheck attribute. More info check MSDN article here and here

Ok, so the above will throw an exception when the Update query cannot find a match, it will interpret this as "oh, someone else got in first and changed something before I did! :-_(  , I'll throw a ChangeConflictException then..."

The art of handling conflicts

First of all, wrap your potentially conflict-causing code in a try/catch block and look out for a ChangeConflictException as below..

try
{
    linq_2.SubmitChanges();
    // Here the values change!
    var result = new ProductDataContext();
    Product product13 = result.Products.Single(i => i.ProductID == pID);
    linq.SubmitChanges();    
}
catch (ChangeConflictException ex)
{ ....

Once you catch the exception, you have choices of how to handle the conflict by basically examining the guilty entities and members and choosing which values to keep, be it Database, overwriting database  values  or merging changes with the database. Let's have a look first at how to retrieve the conflicting data.

DataContext objects have a property called ChangeConflicts thats gives you a ChangeConflictCollection of ObjectChangeConflict objects that eventually you can enumerate and then drill down and get at the values that are in conflict.

catch (ChangeConflictException ex)
{
    foreach (ObjectChangeConflict occ in linq.ChangeConflicts)
    {
    ...........................................
    ...........................................
Each ObjectChangeConflict object represents the conflicted instance, in plain english it encapsulates the update attempt that caused the conflict. 
We can then iterate over the entities that "participated" in that conflict and get specific information about them. You do this by accessing the MemberConflicts collection of ObjectChangeConflict.
catch (ChangeConflictException ex)
{
    foreach (ObjectChangeConflict occ in linq.ChangeConflicts)
    {
        foreach (MemberChangeConflict mcc in occ.MemberConflicts)
        {
            Console.WriteLine("Original: " + mcc.OriginalValue);
            Console.WriteLine("Database value: " + mcc.DatabaseValue);
            Console.WriteLine("CurrentValue: " + mcc.CurrentValue);
        }

Here is where it starts to get juicy. See how above we can get the different values for a particular entity through the "stages" of the conflict. This information could be very useful, you might want to present this details to the user and let her make a decision of what to submit to the database in  a grid or something...right?

But of course, you can explicitly resolve the conflicts by once the error is detected, choosing to keep current values, database values or merge. Let's have a look at it.

Overwriting Database values

First up we are going to solve the conflict by merging the database values with User1 only since User1 got in there last, he is the last committer (is there such a work in english?), User2 won't see anything since her changes when in and the Optimistic handling didn't detect any changes. So the conflict is a conflict from the context of User1, he created the conflict, he is the last in so he has to tell the database what to do with the changes..The database if it talked, it could ask the question.."oh..you are last in...what do you we do with your changes...", That's the way I see it. :-)  Shut up and show me code please ... thanks.

[TestMethod]
public void CheckProductNamesWithTwoDataContexts()
{           
    var linq = new ProductDataContext();
    var linq_2 = new ProductDataContext();
    Guid pID  = new Guid("455984db-92dd-4ba4-87d7-91e4e2e5e00d");

    Product p1 = linq.Products.Single(p => p.ProductID == pID );
    p1.ProductName = "Red Tea";
    p1.CategoryID = new Guid("00000000-0000-0000-0000-000000000000");
    
    Product p2 = linq_2.Products.Single(p => p.ProductID == pID);
    p2.ProductName = "Green Tea";
    p2.CategoryID = new Guid("90000000-1111-1111-1111-000000000000");
    p2.UnitPrice = 10.0M;
    
    try
    {
        linq_2.SubmitChanges();
        var result = new ProductDataContext();
        linq.SubmitChanges(ConflictMode.ContinueOnConflict);  
    }
    catch (ChangeConflictException ex)
    {
        foreach (ObjectChangeConflict occ in linq.ChangeConflicts)
        {
            foreach (MemberChangeConflict mcc in occ.MemberConflicts)
            {
                Console.WriteLine("Original: " + mcc.OriginalValue);
                Console.WriteLine("Database value: " + mcc.DatabaseValue);
                Console.WriteLine("CurrentValue: " + mcc.CurrentValue);
            }  
            linq.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);
        }
        linq.SubmitChanges(); 
    }
}

Quite a lengthy function yeah, but let's see what happens. User1 retrieved the Product, changed the name and the CategoryID, User2 changed the name, CategoryID and UnitPrice. Now note that I called ResolveAll of the ChangeConflict fame from our DataContext, in this case I called it linq. ( You can also call ObjectChangeConflict's Resolve function) This function takes an enum that could be KeepCurrentValues, KeepChanges and OverwriteCurrentValues. In this case we use KeepCurrentChanges, which means we'll keep our changes for the data that clashed with User2, anything else that User2 changed will be ignored and the database defaults will be reinstated. I suppose the way to interpret it is KeepCurrentValues keeps the current values in the DataContext, in this case User1. Note: Make sure once you handle the exception any way you see fit, make sure you SubmitChanges() again for that DataContext otherwise nothing will happen! :-)

Also note that you can choose also when to handle the exceptions. Say you have a lot of changes that the user made and it will be too cumbersome for them to re-enter all those values again. Since SubmitChanges accumulates all conflicts in a batch, the number of changes could be huge.. Good news is that SubmitChanges() can be overloaded to take a ConflictMode enum that will either tell the instance of DataContext to either throw the ConflictException as soon as it detects it (thus minimising the potential hassles for users) called FailOnFirstConflict, or to keep accumulating and attempting to update data and then report the whole lot (ContinueOnConflict).

  ProductName CategoryID UnitPrice
Database row (originally) Camomile Tea E1422E86-.... 0.00
User 1 Red Tea  Guid.Empty  
User 2 Green Trea Guid.NewGuid 10

Result is:

  ProductName CategoryID UnitPrice
database RedTea Guid.Empty 0.00

Note that User1 values went it , User2 data was thrown out and even though in UnitPrice there wasn't a clash between the users, the database value was the chosen one!

Retaining Database values

This is in fact the opposite as our previous example, User2 values are merged with the database and User1's values are chucked out of the window. Basically User1 says, "I'll let the other guy's values go through..I'll comeback to it again and see where my data fits with hers..". The term OverwriteCurrentValues, the way I interpret it that is read from the point of view of User1, "Overwrite my current values, therefore since User2 has committed and are in the db, keep them..".  This is achieved by using:

linq.ChangeConflicts.ResolveAll(RefreshMode.OverwriteCurrentValues);

Table please...

  ProductName CategoryID UnitPrice
Database Camomile E1422E86 0.00
User1 Red Tea Guid.Empty  
User2 Green Tea NewGuid 10.0
  ProductName CategoryID UnitPrice
  Green Tea NewID 10.0

Merging Conflicts with Database values

Using ResolveAll(RefreshMode.KeepChanges) means keeps all the changes in the DataContext and merge them with the database, overwritting the row values if necessary.

  ProductName CategoryID UnitPrice
Database Camomile E1422E86 0.00
User1 Red Tea    
User2 Green Tea NewGuid 10.0

The results are as expected, User1 keeps his Red Tea, and User2 keep hers NewGuid and UnitPrice, in effect the merging has been "between" the users only.

  ProductName CategoryID UnitPrice
  Red Tea NewID 10.0

 

Apologies for the delay in getting this one out, I have been busy and I was writing this up when we noted our beautiful and very lively ( and vicious) pet parrot passed away last Thursday night.  Very sad and it all was all of a sudden. I hope the poor fella didn't suffer and I certainly miss his singing in the mornings. Lesson learned though, birds should be free and while they are very entertaining I don't think is fair to keep them [birds] inside for our selfish gratification.

I certainly enjoyed writing this post and playing around with the DataContext, generating errors etc. There is ton of info on this in MSDN, I certainly think the guys have done a great job in documenting Conflict resolution in LINQ to SQL.

Place for you to start would be:

http://msdn2.microsoft.com/en-us/library/bb399389.aspx

Sunday, April 6, 2008

Playing around with LINQ to SQL: Enumerable.ToDictionary Extension Method

Playing around with Linq I noticed the Enumerable.ToDictionary extension method on IEnumerables<T>. I found it very handy to store expected results for Unit Testing. Yesterday I was playing around with general stuff and I did some looking around a ToDictionary.

We all know what Dictionaries are in the programming world right? Well they are data structures where each element is a key/value pair. Each key in it must be unique and it cannot be null. They are blindly fast for element retrieval by key too, O(n) . So basically they are very efficient at locating, inserting, deleting records associated by keys,

The ToDictionary<TSource,TKey> can be used in many ways, 4 to be more precise but in all them you have to specify a Func<TSource,TElement> to be your key selector, you also have the choice to provide another function to your element selector, ie the object that you are going to store as key and finally you can pass on a IEqualityComparer<T> to compare keys so the underlying HashTable can determine whether keys used are equal. (If you try to add an Element with a Key that already exists in the Dictionary, an ArgumentException will be thrown.

Examples:

 

[TestMethod]
public void CreateDictionary_of_AnonymousTypes()
{
var ps = from p in productos
orderby p.ProductID ascending
select new
{ p.ProductID,p.ProductName};

// Creates a Dictionary where the Key is a productID and the Object is an Anonymous type.
var dummyDictionary = ps.ToDictionary(a => a.ProductID); // Note the Ano

// 77 Products in Northwind...
Assert.AreEqual(77, dummyDictionary.Count());
Assert.AreEqual("Tofu", dummyDictionary[14].ProductName);
// Is it a fib that oo.ProductName is not 'Chai tea' ?
Assert.IsFalse(dummyDictionary[1].ProductName != "Chai tea"); // Product Name of Product with ID = 1 is 'Chai tea'
}

In this example all I did was crated a Dictionary by using the productID as key selector, the dictionary will have a integer as key and the Element will be an Anonymous type.

[TestMethod]
public void CreateDictionary2()
{
var ps = from p in productos
orderby p.ProductID ascending
select new
{ p.ProductID, p.ProductName };

// Creating a dictionary by explicitly determining the Key and the Element
Dictionary<int,string> elDictionary = ps.ToDictionary(a => a.ProductID, a=>a.ProductName);
CollectionAssert.AllItemsAreUnique(elDictionary.Keys);
}

This example a function to select keys and select Elements was done, resulting in a Dictionary<int,String>

[TestMethod]
public voidDictionary_Products_By_CategoryName()
{
       Dictionary<string, IEnumerable<Product>> superDictionary;        
          
        var prods = fromp inproductos
                    groupp byp.Category.CategoryName intoprods_by_categoryName
                    select new
                  
{                           
                        list = prods_by_categoryName
                    };
        superDictionary = prods.ToDictionary(a => a.list.Select(b => b.Category.CategoryName).First(), c => c.list.Select(y=>y));
        IOrderedEnumerable<String> coll = superDictionary.Keys.OrderBy(a=>a);

        IEnumerable<String> orderedCategories = ((fromcats inctx.Categories
                                                     selectcats.CategoryName).OrderBy(h=>h));
        List<String> myList = coll.ToList();
        myList.Reverse();
        myList.Reverse();
        CollectionAssert.AreEqual(myList , orderedCategories.ToList());
       
}


This example I created a Dictionary of Products, where the key is the CategoryName and the Elements is a IEnumberable of Products for that Category.

The same can be achieved as below, much simpler!


[TestMethod]
public void Dictionary_Products_By_CategoryName_Simple()
{
Dictionary<string, IEnumerable<Product>> superDictionary;
superDictionary = (from categories in ctx.Categories
select new
{
categories.CategoryName,
ProductsInCategory = categories.Products.Select(a=>a)
}).ToDictionary(a=>a.CategoryName,a=>a.ProductsInCategory);

Assert.IsInstanceOfType(superDictionary, typeof(Dictionary<string, IEnumerable<Product>>));
}

Here I want to list all Products by Category, and sort them by total unit price...


[TestMethod]
public void do_all_that_stuff_in_less_lines_of_code()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var result = (from categories in ctx.Categories
select new
{
CategoryName = categories.CategoryName,
TotalCategoryUnitPrice = categories.Products.Sum(a=>a.UnitPrice),
Products = categories.Products.OrderByDescending(a=>a.UnitPrice).ToList()
}).OrderByDescending(a=>a.TotalCategoryUnitPrice);

foreach (var category in result)
{
Console.WriteLine( String.Format("\n{0}:\n{1}",category.CategoryName, new String('-', category.CategoryName.Length )));
category.Products.ForEach(p => Console.WriteLine(String.Format("{0,-35} {1,10:C2}", p.ProductName, p.UnitPrice)));
Console.WriteLine(String.Format( "{0,46}", new String('-',6) ));
Console.WriteLine("Total:".PadLeft(38) + String.Format("{0,8:C2}", category.TotalCategoryUnitPrice));
}
}

Capture


Also the ToLookup function is worth mentioning. What it does is creates a Lookup<TKey,TElement> ( basically a collection where each key is mapped to one or more values)


[TestMethod]
public void ToLookup()
{
var prods_by_cat = from products in ctx.Products
select products;

Lookup<string, string> ellookup = (Lookup<string, string>)prods_by_cat.ToLookup(
a => a.Category.CategoryName, // Key
a => a.ProductName); // Value
// Iterate through each IGrouping in the Lookup and output the contents.
foreach (IGrouping<string, string> packageGroup in ellookup)
{
// Print the key value of the IGrouping.
Console.WriteLine(packageGroup.Key);
// Iterate through each value in the IGrouping and print its value.
foreach (string str in packageGroup)
Console.WriteLine(" {0}", str);
}
}

and the output..note that packageGroup is just a collection of Products for that particular Category. Cool eh?


Beverages
Chai tea
Changta
Guaraná Fantástica
Steeleye Stout
Côte de Blaye
Chartreuse verte
Ipoh Coffee
Laughing Lumberjack Lager
Outback Lager
Rhönbräu Klosterbier
Lakkalikööri
Condiments
Aniseed Syrup
Chef Anton's Cajun Seasoning
Chef Anton's Gumbo Mix
Grandma's Boysenberry Spread
Northwoods Cranberry Sauc

Anyway, I don't think I'll be playing with any Dictionaries for a while, done and dusted!

Playing around with LINQ to SQL: Using DataLoadOptions. Immediate loading and filtering of related data

Linq2Sql use deferred execution of queries, lazy loading to get data from the database. The data is only retrieved from the source when we are ready to use it, say when we iterate through it and do something to it. The are advantages to it, say when we have hierarchical data like Orders->OrderDetails, iterating through the Order will not get you all the child objects ( the OrderDetails) by default. This is a good thing of course, it returns you what you want but it can also lead to unexpected results and unnecessary round trips to the database if not careful.

order_order_details_Products

 

[TestMethod]
public void Lazy_Load_Them()
{
    var context = new NorthwindDataContext();
    context.Log = Console.Out;

    var mydata = from order in context.Orders
                 select order;

    foreach (Order o in mydata)
    {
          Console.WriteLine(o.OrderID);
    }
}

 

The only query generated is below:

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]

In the above case it all very simple, all we wanted was the Order(s) entities, network bandwidth was minimised, memory consumption minimised etc.  But what if inside the foreach we also want to output the OrderDetails children of Orders and their corresponding Products? All the child records will be requested when we want, in the below case when we output them...

[TestMethod]
public void Lazy_Load_Them()
{
    var context = new NorthwindDataContext();
    context.Log = Console.Out;
    var mydata = from order in context.Orders
                 select order;

    foreach (Order o in mydata)
    {
         Order_Detail od = o.Order_Details.First();//Just the first record for brevity
         Console.WriteLine(od.ProductID + ", " + od.Product.ProductName);
     }
}

Well, the results are gory, as we request details and the Product for each Order, a separate query will be created and this will mean a new round trip to the database for every Order. The queries are below and trust me, the are like that for every row of the Order table.

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity], [t0].[Discount]
FROM [dbo].[Order Details] AS [t0]
WHERE [t0].[OrderID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10248]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[ProductID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [11]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

11, Queso Cabrales
SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity], [t0].[Discount]
FROM [dbo].[Order Details] AS [t0]
WHERE [t0].[OrderID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10249]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[ProductID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [14]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

14, Tofu
SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity], [t0].[Discount]
FROM [dbo].[Order Details] AS [t0]
WHERE [t0].[OrderID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10250]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

As you can see we have queries for the OrderDetails and another query for the Product data..Three queries for for that! For evey single Order? Hrmm

I have made a total stuff-up, a good thing 'lazy loading' turned into a monster. I have been careless in this case, but how can we improve it? How can minimize the round trips?

DataLoadOptions: LoadWith(LambdaExpression)

LinqToSql provides a method for immediate loading of data related to the main Entity we are working through the DataLoadOptions class. This class provides two methods to get immediate loading of related data, The LoadWith method and AssociateWith method that allow for filtering of related objects.

All you have to do is specify which data that is related to your main target and should be retrieved at the same time with a Lambda expression, for example in our case above our main Entity is the Order, and we would like to get the Order_Details for each Order. This will result in only one trip to the database for all that information, we eliminate all the subqueries that we needed because of the lazy loading. This will improve things significantly for our applications and our users.

In the exaple below I want to get all the Order_Details related to my main interest, Order.

public void Get_Deferred_Data_With_Loading_Options()
{
    var dataContext = newNorthwindDataContext();
    DataLoadOptionsdataLoadOptions = newDataLoadOptions();           
    dataLoadOptions.LoadWith<Order>(order => order.Order_Details);
    dataContext.LoadOptions = dataLoadOptions;
     
    var orders = from order indataContext.Orders
                       select order;

    foreach (Order order in orders)
    {
      List<Order_Detail> ods = order.Order_Details.ToList();
      ods.ForEach(od => Console.WriteLine( order.CustomerID+ " "+ od.OrderID + " , ProdID: "+ od.ProductID + " : "+ od.UnitPrice));
    }          
}

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry], [t1].[OrderID] AS [OrderID2], [t1].[ProductID], [t1].[UnitPrice], [t1].[Quantity], [t1].[Discount], (
    SELECT COUNT(*)
    FROM [dbo].[Order Details] AS [t2]
    WHERE [t2].[OrderID] = [t0].[OrderID]
    ) AS [value]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN [dbo].[Order Details] AS [t1] ON [t1].[OrderID] = [t0].[OrderID]
ORDER BY [t0].[OrderID], [t1].[ProductID]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

VINET 10248 , ProdID: 11 : 14.0000
VINET 10248 , ProdID: 42 : 9.8000
VINET 10248 , ProdID: 72 : 34.8000
TOMSP 10249 , ProdID: 14 : 18.6000
TOMSP 10249 , ProdID: 51 : 42.4000
HANAR 10250 , ProdID: 41 : 7.7000
HANAR 10250 , ProdID: 51 : 42.4000

 

And we can also further load more child records as below.. by adding the LoadWith to get Product associated records from the Order Details entity

 

public void Get_Deferred_Data_With_Loading_Options()
{
      var dataContext = new NorthwindDataContext();

      DataLoadOptions dataLoadOptions = new DataLoadOptions();
      dataLoadOptions.LoadWith<Order>(order => order.Order_Details);
      dataLoadOptions.LoadWith<Order_Detail>(od => od.Product);
        
      dataContext.LoadOptions = dataLoadOptions;
      dataContext.Log = Console.Out;
      
       var orders = from order in dataContext.Orders
                    select order;

       foreach (Order order in orders)
       {
           List<Order_Detail> ods = order.Order_Details.ToList();
           ods.ForEach(od => Console.WriteLine( od.Product.ProductName + ", " + order.CustomerID+ " " + od.OrderID + " , ProdID: " + od.ProductID + " : " + od.UnitPrice ));
       }           
}
 

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry], [t1].[OrderID] AS [OrderID2], [t1].[ProductID], [t1].[UnitPrice], [t1].[Quantity], [t1].[Discount], [t2].[ProductID] AS [ProductID2], [t2].[ProductName], [t2].[SupplierID], [t2].[CategoryID], [t2].[QuantityPerUnit], [t2].[UnitPrice] AS [UnitPrice2], [t2].[UnitsInStock], [t2].[UnitsOnOrder], [t2].[ReorderLevel], [t2].[Discontinued], (
    SELECT COUNT(*)
    FROM [dbo].[Order Details] AS [t3]
    INNER JOIN [dbo].[Products] AS [t4] ON [t4].[ProductID] = [t3].[ProductID]
    WHERE [t3].[OrderID] = [t0].[OrderID]
    ) AS [value]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN ([dbo].[Order Details] AS [t1]
    INNER JOIN [dbo].[Products] AS [t2] ON [t2].[ProductID] = [t1].[ProductID]) ON [t1].[OrderID] = [t0].[OrderID]
ORDER BY [t0].[OrderID], [t1].[ProductID]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Queso Cabrales, VINET 10248 , ProdID: 11 : 14.0000
Singaporean Hokkien Fried Mee, VINET 10248 , ProdID: 42 : 9.8000
Mozzarella di Giovanni, VINET 10248 , ProdID: 72 : 34.8000

Query is a bit more complex, but thats all! No more round trips to our database. Cool eh?

 

AssociateWith: Filtering at the DataContext Level

This is used to specify in advance, sub-queries to limit the amount of data retrieved for any query against that particular context. Your DataContext instance will filter the data given by the Lambda you specified with AssociateWith all the time.

For example, I want to only process orders where the QuantiyInOrder is less than 10 because of shipping difficulties or special offers.

public void Get_Deferred_Data_With_Loading_Options()
{
      var dataContext = new NorthwindDataContext();
      DataLoadOptions dataLoadOptions = new DataLoadOptions();
      dataLoadOptions.LoadWith<Order>(order => order.Order_Details);
      dataLoadOptions.AssociateWith<Order>(order => order.Order_Details.Where(p => p.Quantity < 10));            

 

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry], [t1].[OrderID] AS [OrderID2], [t1].[ProductID], [t1].[UnitPrice], [t1].[Quantity], [t1].[Discount], (
    SELECT COUNT(*)
    FROM [dbo].[Order Details] AS [t2]
    WHERE ([t2].[Quantity] < @p0) AND ([t2].[OrderID] = [t0].[OrderID])
    ) AS [value]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN [dbo].[Order Details] AS [t1] ON ([t1].[Quantity] < @p0) AND ([t1].[OrderID] = [t0].[OrderID])
ORDER BY [t0].[OrderID], [t1].[ProductID]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

VINET 10248 , ProdID: 72 : 5
TOMSP 10249 , ProdID: 14 : 9
VICTE 10251 , ProdID: 22 : 6
HILAA 10257 , ProdID: 39 : 6
ERNSH 10258 , ProdID: 32 : 6
CENTC 10259 , ProdID: 37 : 1
RATTC 10262 , ProdID: 56 : 2

All our queries within the scope of this DataContext will be filtered in advance rather than you having to specify a Where clause.

 

DataLoadOptions in MSDN can be found below

http://msdn2.microsoft.com/en-us/library/system.data.linq.dataloadoptions.aspx