Click here to Skip to main content
Click here to Skip to main content
Technical Blog

DataLoadOptions and how to use it in a compiled LINQ query

, 30 Nov 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
DataLoadOption in LINQ allows immediate loading and filtering of related data.

DataLoadOptions in LINQ allows immediate loading and filtering of related data. DataLoadOptions allows to load related objects so this removes the need for firing a subquery every time you ask for related object(s).

Consider the 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, the inner for loop fires a query on the database to get the customer related to the distributor which in turn decreases performance. But if you know in advance that you will need to use the related list when you are loading the main list, i.e., you need to load data of related entities, make use of DataLoadOptions.

The modified code is something like:

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Distributorgt;(d => d.Customers);
dataContext.LoadOptions = dlo;

Note

  • Be careful when you use DataLoadOptions because it may decrease performance if you are not going to use related objects. Use only in situations when you want to load related objects early and you are going to consume it all.
  • You an only attach DataLoadOptions once with the instance of a DataContext.

The above DataLoadOptions 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 a second time, it produces an exception.

DataLoadOptions in complied queries

First to get more info about complied queries, take a look at this post: Increase LINQ query performance by compiling it. Now when you attach DataLoadOptions to a complied query as we did above, it gives you an exception at run-time.

Compiled queries across DataContexts with different LoadOptions not supported

To avoid the exception, you need to create a static DataLoadOptions variable because as the compiled LINQ queries are static, they will not consume DataLoadOptions which is not static.

So for that I have created the below code where the GetDataLoadOpt() static function returns the DataLoadOptions object and I store it into a static variable dlo and than attach dlo1 with the compiled version of the 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 after removing static from the function v and variable dlo1 and then assign it to the compiled version of the query, and you will get the run-time exception.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Pranay Rana
Software Developer (Senior) GMind Solusion
India India

Microsoft C# MVP (12-13)

 
Hey, I am Pranay Rana, working as a ITA in MNC. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 5.5 years now.
 
For me def. of programming is : Programming is something that you do once and that get used by multiple for many years
 

You can visit my blog

StackOverFlow - http://stackoverflow.com/users/314488/pranay
My CV :- http://careers.stackoverflow.com/pranayamr
 
Awards:


Follow on   Twitter   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141216.1 | Last Updated 30 Nov 2011
Article Copyright 2011 by Pranay Rana
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid