Click here to Skip to main content
13,555,796 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


Posted 10 Nov 2011
Licenced CPOL

DataLoadOptions and How to use in Compiled Linq query

, 13 Nov 2011
Rate this:
Please Sign up or sign in to vote.
DataLoadOptions and How to use in Compiled Linq query
DataLoadOption in LINQ allows immediate loading and filtering of related data. The DataLoadOption allows to load related object so this removes the need of the firing subquery every time you ask for the 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 inner for loop fires a query on database to get the customer related to distributor which in turn decreases 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 to make use of DataLoadOptions.

Modified code is something like:
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Distributorgt;(d => d.Customers);
dataContext.LoadOptions = dlo;


  • Be careful when you use DataLoadOption because it may decrease the performance if you are not going to use related objects. Use only in situations 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 information about Compiled look at this post : Increase Linq query performance by Compling it
Now when you attach DataLoadOption to compiled 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 the static DataLoadOption variable because as the compiled linq queries are the static one, it does not consume the DataLoadOption which is not static.

So for that, I have created the below code where GetDataLoadOpt() static function returns DataLoadOptions object and I store it into static variable dlo and then 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)

If you want to get the above exception, try code removing static from the function v and variable dlo1, then assign it to compiled version of query, you will get the run-time exception.



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


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 -
My CV :-


You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01-2016 | 2.8.180515.1 | Last Updated 13 Nov 2011
Article Copyright 2011 by Pranay Rana
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid