Multiple search parameters with LINQ

Question

I'm writing what I believe should be a relatively straight-forward Windows Form app. I'm using LINQ to SQL, though I have never used it before. We have a SQL Server database, and I'm creating a front-end to access that database. I'm trying to figure out the most efficient way to search for multiple (arbitrary number of) search parameters with it.

In the windows form, I create a dictionary with each search key and its value to search for, and pass it into my search() method. I am trying to find a way to search the database with each of those keys and their associated values. Here is what I am trying to do:

public IQueryable<Product> Search(Dictionary<string, string> searchParams)
{
   DBDataContext dc = new DBDataContext();
   var query = dc.Products;

   foreach (KeyValuePair<string, string> temp in searchParams)
   {
      query = query.Where(x => x.(temp.Key) == temp.Value);
   }

   return query;
}

I realize that syntactically x.(temp.Key) is incorrect, but I hope that illustrates what I am trying to do. I was wondering if there is another way to go about what I am trying to do without having to do a giant switch statement (or if/else if tree).

EDIT

So, I revised it a little, but I'm still having issues with it. Here is what I currently have:

public IQueryable<Product> Search(Dictionary<string, string> searchParams)
{
    DBDataContext dc = new DBDataContext();

    string sQuery = "";
    foreach (KeyValuePair<string, string> temp in searchParams)
    {
        sQuery += temp.Key + "=" + temp.Value + " AND ";
    }

    var query = dc.Products.Where(sQuery);

    return query;
}

According to the LINQ Dynamic Query Library article, this should be OK. Here is the error I'm getting:

The type arguments for method 'System.Linq.Queryable.Where(System.Linq.IQueryable, System.Linq.Expressions.Expression>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

Answer 1

Here's an example that works (I just tested it), using the Dynamic LINQ Query Library.

using System.Linq.Dynamic;
// ...

Dictionary<string, string> searchParams = new Dictionary<string,string>();

searchParams.Add("EmployeeID", "78");
searchParams.Add("EmpType", "\"my emp type\"");

IQueryable<Employee> query = context.Employees;

foreach (KeyValuePair<string, string> keyValuePair in searchParams)
{
    query = query.Where(string.Format("{0} = {1}", keyValuePair.Key,  keyValuePair.Value));
}

List<Employee> employees = query.ToList();

And, to make it absolutely clear that this code actually works here is the actual generated SQL:

FROM [HumanResources].[Employee] AS [t0]
WHERE ([t0].[EmpType] = @p0) AND ([t0].[EmployeeID] = @p1)',N'@p0 nvarchar(11),@p1 int',@p0=N'my emp type',@p1=78

Answer 2

If the Dictionary is not required for some reason, I would make your Search method as follows:

public IQueryable<Product> Search( Func<Product, bool> isMatch )
{
   DBDataContext dc = new DBDataContext();
   return dc.Products.Where( isMatch ).AsQueryable();
}

Then, you would use the method like so:

Obj.Search( item => item.Property1 == "Hello" && item.Property2 == "World" );

Is there some reason that you can't do that?

[Edit: added AsQueryable()]

[Edit: for Dynamic Query using strings]

Take a look here and see if this helps. I haven't used it, but looks like it's what you're looking for: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Personally, I would generally prefer the type-safe Expression> approach since this will give you compile time errors...but if strings are needed, then looks like the best way to do it.

Based on the above link, looks like you should be able to do:

query = query.Where( String.Format("{0}={1}",dict.Key,dict.Value) );

[Edit: String Building Example]

So, one of the problems is that your sql query is going to end with an AND at the end of the string but then no condition after it...so, might try changing to this...syntax might be off slightly, but should be right:

public IQueryable<Product> Search(Dictionary<string, string> searchParams)
{
    DBDataContext dc = new DBDataContext();

    StringBuilder sQuery = new StringBuilder();
    foreach (KeyValuePair<string, string> temp in searchParams)
    {
        if( sQuery.Length > 0 ) sQuery.Append(" AND ");
        sQuery.AppendFormat("{0}={1}",temp.Key,temp.Value);
    }

    var query = dc.Products.Where(sQuery.ToString());

    return query;
}

This will only use "AND" on conditions after the first. Hope it helps...

FYI - It's off-topic, but 'why' I used StringBuilder is that string concatenation the way you had it would result in the string being destroyed and a new string being created in memory 4 times per loop...so changed to a StringBuilder since that will create a buffer that can be filled and only resized when necessary.