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.

//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.

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:

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.

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, .. }:

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.

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?

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.

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

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.

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!

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.

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.

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.

Hello There. I found your weblog using msn. That is a really well written article. I will be sure to bookmark it and return to learn extra of your useful information. Thank you for the post. I will definitely return.