Entity Framework - dynamic sql

Question

I'm implementing a search feature for an app that uses entity framework. There are several optional fields for searching a particular database table/view. What is the best way to implement such a search with EF? Stored procedure? Or can it be done (realistically) using Linq only?

Answer 1

You should be able to do this in LINQ easily enough. Always remember that LINQ queries are chainable:

var query = (from p in products
             select p);

if(field1 != null)
{
    query = (from p in query
             where p.Field1 = field1
             select p);
}

if(field2 != null)
{
    query = (from p in query
             where p.Field2 = field2
             select p);
}

foreach(Product p in query)
{
   // ...
}

Answer 2

What Loren says will work (+1). Or use Microsoft Dynamic LINQ. It works fine with L2E.

Answer 3

You might take a look at this article about dynamically generating lambda expression objects to do it.

Answer 4

A common pattern for handling optional search parameters is to do something like this:

string p = null;
var q = from o in dataContext.Products
    where ((o.Name == p) || (p == null))
    select o;