Short Q&A on ADO.NET Entity Framework query parameters

It seems that this year, ADO.NET Entity Framework (EF) has really gotten into the hearts and minds of .NET developers. Developers have sent many questions to me asking about miscellaneous ADO.NET EF things, but one area that seems to be common is how to dynamically build LINQ query parameters, i.e. how to create those "WHERE" clauses at runtime from user-specified data.

With that, here are three common questions and answers to them. These resolve around datetimes and strings. I'm assuming you are using SQL Server as your database in the answers.

Q1. I have a SQL Server orders table with datetime fields, that is fields with a date and a time value. I want to retrieve orders created on a particular date, but when I do so, I do not get any orders.

A1. The problem is that you are asking Entity Framework (and SQL Server) to retrieve on a given date, but since you are not giving a time value, no matching records are returned. Remember that a datetime contains both a date and a time value. Try to execute a query like this in C#:

var matching = from o in entities.Orders
               where (o.OrderDate.Value.Year == matchDate.Year) &&
               (o.OrderDate.Value.Month == matchDate.Month) &&
               (o.OrderDate.Value.Day == matchDate.Day)
               select o;

Q2. I want to retrieve orders from my SQL Server date between two dates. How would I do that with LINQ and ADO.NET EF?

A2. The problem is related to Q1, and the solution is based on the same ideas. To retrieve orders between different dates, for instance 2010-05-12 and 2010-05-24, you would need to construct two datetime values: 2010-05-12 0:00:00 and 2010-05-24 23:59:59. This way, it is easy to query the order values. Again, example in C#:

DateTime dateFrom = new DateTime(2010, 5, 12);
DateTime dateTo = new DateTime(2010, 5, 24);
NorthwindEntities entities = new NorthwindEntities();

// create a date that ends at 23:59:59 o'clock
DateTime adjustedDateTo = dateTo.AddDays(1).AddSeconds(-1); 
var matching = from o in entities.Orders
               where (o.OrderDate.Value >= dateFrom) &&
               (o.OrderDate.Value <= adjustedDateTo)
               select o;

Note how this is somewhat like an SQL "BETWEEN" query, but LINQ to Entities does not use a BETWEEN query when calling SQL Server.

Q3. I have a customer table in SQL Server, and each customer is identified with a unique string ID. I want my customer to dynamically select several of these IDs, and then query customer information for all these IDs in a single query. How would I do that? I'm using Visual Studio 2008 and .NET 3.5.

A3. What you need to do in SQL terms is creating an WHERE clause with OR search criteria. LINQ to Entities 3.5 doesn't support building these "OR queries" directly, but you can create some helper methods to get started.

Assume you would have the user-selected customer IDs in a list of strings, for example:

List custIds = new List()
  {
    "BLAUS",
    "FAMIA",
    "QUEDE",
    "WARTH",
    "VINET"
  };

Now, your first attempt might be a query like this:

// this won’t work
var matching = entities.Customers.Where(
      c => custIds.Contains(c.CustomerID));

Although this looks very convenient, the problem is that it doesn't work with ADO.NET Entities 3.5. Instead, you will get an exception at runtime:

System.NotSupportedException: LINQ to Entities does not recognize the
method 'Boolean Contains(System.String)' method, and this method cannot be translated into a store expression.

However, although your first attempt didn't work, there's always something more to try. Let's write a quick helper method (thanks AJ) the you can use:

using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
...
internal static Expression>
    BuildLinqOrExpression(
        Expression> valueSelector,
        IEnumerable values)
{
    // check parameter values
    if (null == valueSelector)
        throw new ArgumentNullException("valueSelector");
    if (null == values)
        throw new ArgumentNullException("values");
    // start to create an lambda expression
    ParameterExpression p = valueSelector.Parameters.Single();
    if (!values.Any())
        return e => false;
    var equals = values.Select(value =>
        (Expression)Expression.Equal(
             valueSelector.Body,
             Expression.Constant(
                 value, typeof(TValue)
             )
        )
    );
    var body = equals.Aggregate(
             (accumulate, equal) => Expression.Or(accumulate, equal));
    return Expression.Lambda>(body, p);
}

That's long chunck of code, but what it does is it implements a static helper method called BuildLinqOrExpression. This method takes in as parameters a enumerable list (for instance, a List) and builds a LINQ expression with OR clauses in the query search criteria.

Here's how you would use this new method (you could make it part of your utilities or your data access classes):

NorthwindEntities entities = new NorthwindEntities();
var matching = entities.Customers.Where(
      MyUtilities.BuildLinqOrExpression(
        c => c.Customer, psnValues));

That's it! Now you have a nice way of creating queries like "CustomerId = 'ALFKI' OR CustomerId = 'WILMA'" etc. This is by the way much more efficient than first fetching all customer records to the client, and then filtering the data in memory.

Keywords: ADO.NET EF, dynamic query parameters with LINQ problem, OR clauses in WHERE, how to combine multiple query parameters.