LINQ: How to build complex queries utilizing deferred execution and anonymous types

While working with LINQ to SQL and LINQ to Entities (Entitiy Framework), I really appreciate and utilize the deferred execution capabilities, i.e. the queries are not materialized (sent to server) until they are needed. This great feature can help us to construct our query in several steps and then send a single optimized TSQL version to the database for processing. This approach is similar to our application of CTE (Common Table Expressions) in T-SQL, with which we can build our T-SQL queries in small manageable steps and then finally run a single optimized query on the database. This post will present some scenarios to show how we can utilize the deferred execution capabilities and anonymous types to build complex queries in LINQ.

Filtering a single table with multiple (and optional) where conditions
Let’s suppose, we are building a search form with multiple criteria to restrict the filtering, i.e. we need to restrict our query based on user inputs. Here’s a typical LINQ version for handling such situation:

 //start with all the records
var query = from c in dc.Customers select c;

//filter the result set based on user inputs
if ( !string.IsNullOrEmpty( countryFilter ) )
  query = query.Where ( c=>c.Country == countryFilter );
if ( !string.IsNullOrEmpty( cityFilter ) )
  query = query.Where ( c=>c.City == cityFilter );

  .....

//at this point, our query has all the filters set; yet it has not been sent to the server

//run the query on database and grab the results
var results = query.ToList();

Notice that in this example, we have build our query in several steps but due to the deferred execution of LINQ and IQueryable, only single TSQL query will be generated in the whole process.
Do note that the where conditions described in this sample and ANDed with each other. To build queries with OR conditions, we need to use Expression Trees. Luckily, good people are around us and they have build the great Predicate Builder for simplifying such situations. Follow the link for some cool examples.

Filtering a result of join of several tables with multiple (and optional) where conditions
Let’s look at an example when we have a result set obtained by joining two or more tables and we need to apply filtering on those original tables. Let’s assume we start building our query as the approach described in the previous example but we can’t proceed since our result does not contain the columns we need to filter:

var query =
//joins containing several tables
from c in dc.Customers
join ct in dc.CustomerTypes on c.CustomerTypeId equals ct.CustomerTypeId
join pt in dc.PaymentTerms on c.PaymentTermId equals pt.PaymentTermId

 //result we need
select new 
{ 
  CustomerId = c.CustomerID, 
  Name = c.CustomerName, 
  Type = ct.CustomerType, 
  PaymentTerm = pt.PaymentTerm 
}

//we cant filter here as our anonymous type does not contain a definition for the fields upon which we need to filter
if ( filterByCountry )
  query = query.Where ( c=>c.Country == countryFilter )

Such situation can be handled by creating an intermediate anonymous type containing ALL the tables records that are joined, performing filtering in steps, and finally selecting the columns we need. Here’s the code that demonstrates this:

var query =
//joins
from c in dc.Customers
join ct in dc.CustomerTypes on c.CustomerTypeId equals ct.CustomerTypeId
join pt in dc.PaymentTerms on c.PaymentTermId equals pt.PaymentTermId

//select ALL records variables for later use
select new { c, ct, pt  }

//now we can apply filters on ANY of the joined tables
if ( filterByCountry )
  query = query.Where ( q=>q.c.Country == countryFilterValue )
if ( filterPaymentTerms )
  query = query.Where( q=>q.pt.SomeField == paymentTermsFilterFieldValue )
if ( filterCustomerType )
  query = query.Where( q=>q.ct.SomeField == customerTypeFilterFieldValue )
...

//finally select the columns we needed
var result =
 from q in query
 select new 
 { 
   CustomerId = q.c.CustomerID, 
   Name = q.c.CustomerName, 
   Type = q.ct.CustomerType, 
   PaymentTerm = q.pt.PaymentTerm 
 }

Notice that due to the use of anonymous type containing all the tables, we can refer to any table in our filtering criteria. Again, because of the deferred execution of LINQ queries, the only SQL that gets generated and executed is the last one that brings back only the columns we needed to build our result.

Grouping with filtered aggregates
Such approach of using intermediate anonymous types can be extended to queries having grouping or left outer joins. Let’s look at another sample to find sales of certain product categories per customer.

//joins of several tables
from c in dc.Customers
join o in dc.Orders on c.CustomerId equals o.CustomerId
join od in dc.OrderDetails on o.OrderId equals od.OrderId
join p in dc.Products on od.ProductId equals p.ProductId
join pc in dc.ProductCategories on p.CategoryId equals pc.CategoryId

//grouping
group c by c.CustomerName into grp
select new
{
  CustomerName = grp.Key,
  BeveragesCount = grp.Count ( g=>g.Category == "Beverages" )  //not possible, we don't have category in our grouping
  StationeryCount = grp.Count ( g=>g.Category == "Stationary" )  //not possible
}

Notice that we are trapped in the same situation: our grouping has cleared the joined result and hence we are unable to filter our count by category. Again, to achieve this, we need to group into an anonymous type containing all the joined tables so that our category record is still available for us to perform filtered counting. Here’s the LINQified version in which we replaced group c with group by new { c, .. }:

//joins of several tables
from c in dc.Customers
join o in dc.Orders on c.CustomerId equals o.CustomerId
join od in dc.OrderDetails on o.OrderId equals od.OrderId
join p in dc.Products on od.ProductId equals p.ProductId
join pc in dc.ProductCategories on

//grouping
group new { c, o, od, p, pc } by c.CustomerName into grp
select new
{
  CustomerName = grp.Key,
  BeveragesCount = grp.Count ( g=>g.pc.Category == "Beverages" ) 
  StationeryCount = grp.Count ( g=>g.pc.Category == "Stationary" ) 
}

Again, by using an anonymous type we are able to reference any of the fields from the original tables and the final generated SQL is optimized to query only what is needed in the result set.

That’s all. This post demonstrated uses of anonymous types and deferred execution of LINQ in a few scenarios. It was intended to serve as a starting point in writing complex LINQ queries. Remember, with the use of intermediate anonymous types, we can refer to all our original table fields anywhere in our query building process. The great thing is that this ease comes with NO COST, as the deferred execution of LINQ guarantees that SQL will be generated only when required and will include only what is required. Happy LINQing.

About these ads

31 Responses to “LINQ: How to build complex queries utilizing deferred execution and anonymous types”

  1. DotNetShoutout Says:

    LINQ: How to build complex queries utilizing deferred execution and anonymous types « Mehroz’s Experiments…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. Mastan Says:

    so nice so nice, really its useful Mehroz, Alhamdulillah.

    Are you started using .NET 4?

    Take care

  3. Syed Mehroz Alam Says:

    Thanks very much, Mastan. JazakAllah.

    Yes, my current project involves creation of an MS Project 2010 AddIn to integrate it with Microsoft Dynamics Navision and this is being developed in .NET 4.

  4. Jaime Says:

    I specially like the last trick regarding the group by with joined tables…. it has save me a lot of time! Thanks
    Jaime

  5. Syed Mehroz Alam Says:

    Thanks, Jaime. I am glad you like it.

  6. Tweets that mention LINQ: How to build complex queries utilizing deferred execution and anonymous types « Mehroz’s Experiments -- Topsy.com Says:

    [...] This post was mentioned on Twitter by Andrew Wan. Andrew Wan said: Using LINQ to build queries with deferred execution and anonymous types in mind http://tinyurl.com/yjhpzrc [...]

  7. LINQ: How to write queries with complex join conditions « Mehroz’s Experiments Says:

    [...] join conditions April 13, 2010 — Syed Mehroz Alam This post is a follow-up of my previous post in which I discussed some common scenarios and how we can handle them in our LINQ queries. This [...]

  8. João Vitor Says:

    Great post!!!
    The last trick regarding the group by with joined tables was very usefull…

  9. Patricia Faulkinberry Says:

    THANKS! Especially for that last tip, which is invaluable.

  10. roahnluo Says:

    really helpful info! Thanks a lot!

  11. Bob Kaine Says:

    Any insight on using this approach with predicatebuilder(pb)

    I have multi-select dropdownlist which fills a textbox with value1; value2; etc. and a keyword textbox where the user can enter keywords in the same format (cat; dog)

    normally using pb I would enter something like:

    var pb = PredicateBuilder.False();
    string[] keywords = txtKeywords.Text.Split(‘;’);

    var query = from im in db.Images
    select im;

    foreach (string keyword in keywords)
    {
    pb = pb.Or(c => c.Keywords.Contains(keyword.Trim()));
    }

    query = query.Where(pb);

    But this won’t work if the guery looks like this:

    var query = from ix in db.IndexImageXREFs
    join i in db.Images on ix.ImageID equals i.ImageID
    select new { ix, i };

  12. K1r Says:

    Hi
    Nice article! You wrote that there is NO COST for this approach, but how about query compilation overhead? I use very similar logic, but query time differs from time to time. I compiled all the constnt queries, but it is not possible for these complex dynamic queries. Am I missing something?

    Thanx

  13. manu Says:

    Very useful Thanks

  14. Olawale Says:

    This is very logical and interesting.Well done

  15. Robert Says:

    Very nice, but what if you want to use outerjoins, so only join the tables if “Paymentterm” or “Customertypes” are filled in.

    Now I am building different queries for each scenario, but I think there has to be a more simpel solution.

    regards,

    Robert

    My Code below:
    /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

    IQueryable debtors;

    if ((!string.IsNullOrEmpty(whereClauseAddress.Single(wca => wca.Key == “Postcode”).Value)
    || !string.IsNullOrEmpty(whereClauseAddress.Single(wca => wca.Key == “City”).Value))
    && !string.IsNullOrEmpty(whereClauseContact.Single(wcc => wcc.Key == “Name”).Value)
    ) //join both adressess and contacts
    {
    var query = from debt in dc.Debtors.Where(d => d.Deleted == false)
    join daddr in dc.DebtorAddresses on debt.DebtorID equals daddr.debtorID
    join dcont in dc.DebtorContacts on debt.DebtorID equals dcont.debtorID
    select new { debt, daddr, dcont };

    if (!string.IsNullOrEmpty(whereClauseAddress.Single(wca => wca.Key == “Postcode”).Value))
    query = query.Where(q => q.daddr.postcode.Contains(whereClauseAddress.Single(wca => wca.Key == “Postcode”).Value));
    if (!string.IsNullOrEmpty(whereClauseAddress.Single(wca => wca.Key == “City”).Value))
    query = query.Where(q => q.daddr.city.Contains(whereClauseAddress.Single(wca => wca.Key == “City”).Value));

    if (!string.IsNullOrEmpty(whereClauseContact.Single(wcc => wcc.Key == “Name”).Value))
    query = query.Where(q => (q.dcont.infix + ” ” + q.dcont.lastname).Contains(whereClauseContact.Single(wcc => wcc.Key == “Name”).Value));

    //finally select the columns we needed
    debtors = from q in query
    select q.debt;
    }
    else if ((!string.IsNullOrEmpty(whereClauseAddress.Single(wca => wca.Key == “Postcode”).Value)
    || !string.IsNullOrEmpty(whereClauseAddress.Single(wca => wca.Key == “City”).Value))
    && string.IsNullOrEmpty(whereClauseContact.Single(wcc => wcc.Key == “Name”).Value)
    ) //only join address
    {
    var query = from debt in dc.Debtors.Where(d => d.Deleted == false)
    join daddr in dc.DebtorAddresses on debt.DebtorID equals daddr.debtorID
    select new { debt, daddr };

    if (!string.IsNullOrEmpty(whereClauseAddress.Single(wca => wca.Key == “Postcode”).Value))
    query = query.Where(q => q.daddr.postcode.Contains(whereClauseAddress.Single(wca => wca.Key == “Postcode”).Value));
    if (!string.IsNullOrEmpty(whereClauseAddress.Single(wca => wca.Key == “City”).Value))
    query = query.Where(q => q.daddr.city.Contains(whereClauseAddress.Single(wca => wca.Key == “City”).Value));

    //finally select the columns we needed
    debtors = from q in query
    select q.debt;
    }
    else if ((string.IsNullOrEmpty(whereClauseAddress.Single(wca => wca.Key == “Postcode”).Value)
    && string.IsNullOrEmpty(whereClauseAddress.Single(wca => wca.Key == “City”).Value))
    && !string.IsNullOrEmpty(whereClauseContact.Single(wcc => wcc.Key == “Name”).Value)
    ) //only join contact
    {
    var query = from debt in dc.Debtors.Where(d => d.Deleted == false)
    join dcont in dc.DebtorContacts on debt.DebtorID equals dcont.debtorID
    select new { debt, dcont };

    if (!string.IsNullOrEmpty(whereClauseContact.Single(wcc => wcc.Key == “Name”).Value))
    query = query.Where(q => (q.dcont.infix + ” ” + q.dcont.lastname).Contains(whereClauseContact.Single(wcc => wcc.Key == “Name”).Value));

    //finally select the columns we needed
    debtors = from q in query
    select q.debt;
    }
    else
    {
    var query = from debt in dc.Debtors
    select debt;
    debtors = query;
    }

    //filter debtorvalues…
    debtors = debtors.Where(d => d.Deleted == false);
    debtors = debtors.Where(d => d.MemberID == int.Parse(whereClause.SingleOrDefault(w => w.Key == “MemberID”).Value));
    if (!string.IsNullOrEmpty(whereClause.SingleOrDefault(w => w.Key == “Name”).Value))
    debtors = debtors.Where(d => d.Name.Contains(whereClause.SingleOrDefault(w => w.Key == “Name”).Value));
    if (!string.IsNullOrEmpty(whereClause.SingleOrDefault(w => w.Key == “DebtorIDbyMember”).Value))
    debtors = debtors.Where(d => d.DebtorIDbyMember.Contains(whereClause.SingleOrDefault(w => w.Key == “DebtorIDbyMember”).Value));

    if (Convert.ToBoolean(whereClause.SingleOrDefault(w => w.Key == “UsesBloemplein”).Value))
    debtors = debtors.Where(d => d.UsesBloemplein == true);

    return debtors.Distinct();

    /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

  16. Bob Says:

    Great Article! Just one question, with the section “Grouping with filtered aggregates” how would you get a property out of say your customer object, c?

  17. amit Says:

    Hi,
    I would like to optimize the Linq to Object query using deffered execution so that query can be easily debug and maintain in the scenario where millions of records are filtered. Write now i am joining 4-5 object collection with left outer join in a single block. and at the end i m returning .ToArray.. I have read your second scenario which fits into my requirement.

    Please suggest me how to write a deffered query in case of million records.

    Thanks,
    Amit

  18. Decent estate agent Says:

    Magnificent information, thought provoking. Cheers!

  19. Andrew Says:

    Great stuff dude. More straight forward and readable than predicatebuilder IMO :)

  20. google plus age limit Says:

    What’s up it’s me, I am also visiting this site
    regularly, this web site is genuinely good and the visitors are truly sharing fastidious
    thoughts.

  21. http://www.xtrememind.com/ Says:

    Definitely believe that which you said. Your favorite
    justification seemed to be on the net the simplest thing to be aware of.
    I say to you, I definitely get annoyed while people think about
    worries that they plainly don’t know about. You managed to hit the nail upon the top
    as well as defined out the whole thing without having side effect ,
    people could take a signal. Will likely be back to get
    more. Thanks

  22. Lorrie Says:

    What’s Going down i am new to this, I stumbled upon this I’ve found It absolutely
    useful and it has aided me out loads. I am hoping to give a contribution &
    help other customers like its aided me. Great job.

  23. http://www.dominicanattorney.com/ Says:

    Hello there! This post couldn’t be written much better!
    Looking at this post reminds me of my previous roommate! He constantly kept talking
    about this. I am going to forward this post to him.
    Fairly certain he’ll have a good read. Many thanks for sharing!

  24. covet fashion cheats Says:

    Viewing the action itself and playing Euromillions in Sweden, you might get subjected to is sheer negligence.
    Playing mobile games being introduced. In the
    pro-reader, high-engagement world of Spore in the series.
    Harry Potter Game is the one and all. To understand the market covet fashion cheats for mobile owners.
    Gun Bros and as per your requirements. Now there is a
    game together and get the very good recipe for cooking the smoked salmon sandwiches very easily
    extended to several other platforms have also gained immense
    recognition for engaging the users.

  25. http://www.dapharco.com.vn/vi/user-profile/ben7638.aspx Says:

    Thanks for finally writing about >LINQ: How to build complex queries utilizing deferred execution and
    anonymous types | Mehroz’s Experiments <Liked it!

  26. three-week diet detox Says:

    I have been browsing on-line more tthan 3 hours as of late, but I byy no means found any fascinating article like yours.
    It’s beautiful price enough for me. In my opinion, if all web owners and bloggers madxe
    excellemt content materfial as you probahly did, the internet wiill probably be much more useful than evcer before.

  27. Marylyn Says:

    You ought to be a part of a contest for one of the best websites on the web.
    I am going to recommend this website!

  28. productos para bajar de peso Says:

    Hi there, I desire to subscribe for this weblog to take most recent updates, therefore where can i do
    it please help out.

  29. Eliza Says:

    Fine way of describing, and pleasant post to get facts about my presentation topic,
    which i am going to deliver in academy.

  30. toronto long term disability lawyer Says:

    When someone writes an piece of writing he/she maintains the thought of a user in his/her brain that how a user can understand it.
    Thus that’s why this paragraph is amazing. Thanks!

  31. seo Says:

    Pretty element of content. I simply stumbled upon your blog and in accession capital to claim that I get actually enjoyed account your weblog posts.
    Anyway I’ll be subscribing for your augment or
    even I fulfillment you get right of entry
    to consistently fast.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 57 other followers

%d bloggers like this: