Multiple LinqToSQL queries and performance

Question

Does something like this affect performance badly?

var myQuery = from c in Customers select c;

var filter1 = from c in myQuery where c.ID > 2 select c;

myQuery = filter1;

var filter2 = from c in myQuery where c.Name.Contains("r") select c;

myQuery = filter2;

When I do this it seems to only do the actual query at the end, not on every "var...". Everything up to that point seems to just construct a query, so it seems like this is ok and not much performance difference from placing all the filters in 1 query. Am I wrong and it's actually running multiple queries against the database?

I'm trying to find out a good way to create queries based on a user's input so they can filter on different criteria. I'm not too worried about performance as long as doing this is not going to take a huge amount of time.

I also found posts about the Dynamic Linq library, but using that seems clunky and I don't see much difference from doing it this way.

Answer 1

No, it won't execute any queries until you start to actually ask for results. Building up queries this way is fine, and one of the nice things about LINQ.

It works the same way in LINQ to Objects, by the way (in terms of deferred execution - the actual pipeline is very different), so long as you're using a lazy query operator (basically all the ones which return an IEnumerable<T> or IOrderedEnumerable<T>).

Answer 2

I would look at using Extension methods to construct the query dynamically. I think that it will do exactly what you need to do. And, yes, the query isn't actually evaluated until an operation is performed that needs results so combining them does not necessarily result in additional trips to the database.

var query = db.Customers;
if (selectID.HasValue)
{
   query = query.Where( c => c.ID > selectID.Value );
}
if (!string.IsNullOrEmpty( nameFilter ))
{
   query = query.Where( c => c.Name.Contains( nameFilter ) );
}

foreach (var customer in query) // now the query is performed
{
 ...
}