Chaining Linq Queries

This topic came up at work today and so I wanted to put up a post about it in case others were still not quite clear on this point. And that point would be…you can chain linq queries! A lot of people are probably still thinking that Linq queries execute as soon as the query is declared, or that the query is executed as soon as the query variable is accessed, but that is simply not true.

Let’s take for example this linq query:

  var query2 = (from u in context.Users
      where u.Id == value && u.Username == name
      orderby u.Username select u).Skip(skip).Take(take);

This query is a very simple query that pulls a user from the database by Id and by Username. I can hear you now… "Hold on, did you just say by Id and Username?" Yep. "But that makes no sense!" Of course it doesn’t. Why would you ever need to query something out by Id and by Username, shouldn’t Ids be unique? Yes, of course. So how do we do this with Linq? Well, some people may start thinking that they need to use Linq’s dynamic query support, but this is not the case. Nothing we are doing here is really dynamic, we simply have optional parts of our query.

If we have an Id then we want a query like this:

  var query2 = (from u in context.Users
      where u.Id == value
      orderby u.Username select u).Skip(skip).Take(take);

And if we have a Username then we want a query like this:

  var query2 = (from u in context.Users
      where u.Username == name
      orderby u.Username select u).Skip(skip).Take(take);

But of course we don’t want to actually write two queries, do we? That would totally violate all of our inner agileness. You do have inner agileness don’t you?

Well, the solution to this problem is actually quite easy. All we have to do is just use the Linq methods to slowly build up our query, adding on one layer at a time. Like this:

  var query = from u in context.Users
              select u;
 
  if (value != null)
  {
    query = query.Where(u => u.Id == value);
  }
 
  if (!String.IsNullOrEmpty(name))
  {
    query = query.Where(u => u.Username == name);
  }
 
  query = query.OrderBy(u => u.Username).Skip(skip).Take(take);

Now we get our "dynamic" query but everything has intellisense and is checked at compile time. The best part is that nothing is executed in any intermediate step. In fact, in all of the examples above nothing is every executed. We are forming a bunch of expression trees but we are never actually executing them. In order to execute them we just need to call "ToList", "ToArray", or use it in something like a "foreach" statement. Something like this:

  var list = query.ToList();

Also since all we are doing is building up an IQueryable expression tree and nothing is ever executed we can pass this IQueryable object around to different methods. This would allow us to define our own extension methods (or just plain methods) like this:

  public IQueryable<T> SkipAndTake<T>
    (this IQueryable<T> query, int skip, int take)
  {
    return query.Skip(skip).Take(take);
  }

We could then call it like this:

  query = query.SkipAndTake(skip, take);

Or like this:

  var query = (from u in context.Users
              where u.Username.StartsWith("ju")
              orderby u.Username
              select u).SkipAndTake(skip, take);

Well, I hope that you learned a little something about chaining Linq queries and how you can use this method to build up Linq queries and also define your own custom generic methods for operating on Linq queries. Adios.

Be Sociable, Share!

4 comments

  1. Great idea! I think this is one of the best solutions to the dynamic query problem I have seen. Much better than just manipulating strings.

  2. Great post Justin! We’re also implementing this at my work.

  3. We are actually doing the exact same thing w/o extension methods. Are there any benefits to using extension methods though? Denny stop surfing the web and get back to work.

  4. @Luke – yeah, but if your queries are truly dynamic then you must use another method

    @Jerry – there is no advantage to using extension methods other than you can call your methods as a member of any query rather than passing your query into them. Makes your interface more "fluent".

Leave a comment