I am Microsoft C# MVP July - 2012 My name is Pranay Rana. Currently, I'm working as senior software engineer in a mid-sized company (Ahmedabad). I have 5 years of the experience in web development with Asp.Net, C# and MS SQL server. For me, the definition of programming is: something that you do once and that gets used by multiple users for many years Pranay is a DZone MVB and is not an employee of DZone and has posted 34 posts at DZone. You can read more from them at their website. View Full User Profile

DataLoadOptions and How to use in Compiled Linq query

10.26.2011
| 2682 views |
  • submit to reddit
DataLoadOption in LINQ allows immediate loading and filtering of related data. The DataLoadOption allow to load related object so this remove need of the firing subquery every time you ask for the related object(s).

Consider below case

If you do code like this
var distlist = (from d in edb.Distributors select d).ToList();
            foreach(Distributor d in distlist)
            {
              var clientlist = d.Customers;
              foreach( Customer c in clientlist)
              {
                   //do the code 
              }
            }
each time inner for loop fire query on database to get the customer related to distributor which in turn decrease the performance. But if you know in advance that you are need to use the related list when you are loading main list i.e you need to load data of related entity eagerly make use of DataLoadOptions.

Modified code is something like
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Distributorgt;(d => d.Customers);
dataContext.LoadOptions = dlo;
Note
  • Be careful when you use DataLoadOption because it may decrease the performance if you are not going to use related objects. Use only in situation when you want to load related object early and going to consume it all.
  • You an only attach DataLoadOption once with the instance of datacontext.

The above DataLoadOption runs perfectly when you use regular Linq Queries. But it does not work with compiled queries. When you run this code and the query hits the second time, it produces an exception:

DataLoadOptions in Complied queries
First to get more info about Complied look this post : Increase Linq query performance by Compling it
Now when you attache DataLoadOption to complied query as we did above it give you an exception at run-time
Compiled queries across DataContexts with different LoadOptions not supported

To avoid the exception you need to create the static DataLoadOption variable because as the compiled linq queries are the static one it not consume the DataLoadOption which is not static.

So for that I have created below code where GetDataLoadOpt() static function returns DataLoadOptions object and I store it into static variable dlo and than attach this dlo1 with the compiled version of query.
public static DataLoadOptions dlo1 = GetDataLoadOpt();

    public static Func<DataLoadTestDataContext, string, IQueryable<Product>>
        ProductByCategory =
        CompiledQuery.Compile((DataLoadTestDataContext db, string category) =>
        from p in db.Products where p.Category == category select p);

    public static DataLoadOptions GetDataLoadOpt()
    {
        DataLoadOptions dlo = new DataLoadOptions();
        dlo.LoadWith<Product>(p => p.ProductWithCategory);
        return dlo;
    }

    public static void testfunction()
    {
        DataLoadTestDataContext context = new DataLoadTestDataContext();
        context.LoadOptions = dlo1;
        var productlist = ProductByCategory(context, "mobile");

        foreach (Product p in productlist)
        {
            Console.WriteLine(p.ProductWithCategory);
        }
    }
If you want to get the above exception try code removing static from the function v and variable dlo1 than assign it to compiled version of query you will get the run-time exception.
References
Published at DZone with permission of Pranay Rana, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)