views:

69

answers:

3

I have created a dynamic search screen in ASP.NET MVC. I retrieved the field names from the entity through reflection so that I could allow the user to choose which fields they wanted to search on instead of displaying all fields in the view.

When the search result is Posted back to the controller, I receive a FormCollection containing the FieldName and the Value. I don't know how many fields are being searched on, and the FormCollection only contains fields that were chosen by the user.

I want to be able to now take that field name and apply that to my LINQ statement when I query the database for example:

public List<People> SearchPeople(Dictionary<string, string> fieldValueDictionary)
{
    List<People> searchResults = new List<People>();

    foreach (string key in fieldValueDictionary.Keys)
    {
         searchResults.Add(entities.People.Where(p => p.<use the key string as the fieldName> == fieldValueDictionary[key]));
    }

    return searchResults;
}

Where I have "use the key string as the fieldName" it would be like p => p.FirstName == fieldValueDictionary[key] where key = "FirstName". I've tried and failed to use Lambda Expression Trees, and have had a little success with Dynamic LINQ. The only other alternative is to do something like:

public List<People> SearchPeople(Dictionary<string, string> fieldValueDictionary)
{
    IQueryable<People> results = entities.People;

    foreach (string key in fieldValueDictionary.Keys)
    {
         switch (k)
         {
             case "FirstName": results = results.Where(entities.People.Where(p => p.FirstName == k);
             case "LastName": results = results.Where(entities.People.Where(p => p.LastName == k);
             // Repeat for all 26 fields in table
         }
    }

    return results.ToList<People>();
}

UPDATE: I've done research into Lambda Expression Trees through the following posts:

http://stackoverflow.com/questions/1667813/dynamically-create-lambdas-expressions-linq-orderbydescending

http://stackoverflow.com/questions/903558/parameter-problem-with-expression-lambda

http://stackoverflow.com/questions/1299534/linq-passing-lambda-expression-as-parameter-to-be-executed-and-returned-by-metho

I've gotten as far as getting a lambda to output the following: "p => p.FirstName", but I can't get this to work in a where. Any Suggestions? My code is below:

MemberInfo member = typeof(People).GetProperty("FirstName");
ParameterExpression cParam = Expression.Parameter(typeof(People), "p");    
Expression body = Expression.MakeMemberAccess(cParam, member);        

var lambda = Expression.Lambda(body, cParam);
+1  A: 
  public List<People> SearchPeople(Dictionary<string, string> fieldValueDictionary)
        {
            return !fieldValueDictionary.Any()
                   ? entities.People 
                   : entities.People.Where(p => fieldValueDictionary.All(kvp => PropertyStringEquals(p, kvp.Key, kvp.Value)))
                                    .ToList();
        }

  private bool PropertyStringEquals(object obj, string propertyName, string comparison)
        {
            var val = obj.GetType().GetProperty(propertyName).GetValue(obj, null);
            return val == null ? comparison == null : val.ToString() == comparison; ;
        }
Ani
+1  A: 

Have you tried getting the value from PropertyInfo?

entities.People.Where(p => (p.GetType().GetProperty(key).GetValue(p, null) as string) == fieldValueDictionary[key])
John Bowen
Linq to Entities will not support the GetValue() call. I can make this idea work, but it requires me to get the full list of People from the DB and then foreach through them, its cleaner than I had but still not quite what I want.
Scott Lance
A: 

After a lot more trial and error and searching I accidentally found another SO post that covers the same issue:

http://stackoverflow.com/questions/3423270/invalidoperationexception-no-method-where-on-type-system-linq-queryable-is-c

Here is my modified code that works:

        IQueryable query = entities.People;
        Type[] exprArgTypes = { query.ElementType };

        string propToWhere = "FirstName";            

        ParameterExpression p = Expression.Parameter(typeof(People), "p");
        MemberExpression member = Expression.PropertyOrField(p, propToWhere);
        LambdaExpression lambda = Expression.Lambda<Func<People, bool>>(Expression.Equal(member, Expression.Constant("Scott")), p);                            

        MethodCallExpression methodCall = Expression.Call(typeof(Queryable), "Where", exprArgTypes, query.Expression, lambda);

        IQueryable q = query.Provider.CreateQuery(methodCall);

With some hopefully pretty easy modifications, I should be able to get this to work with any type.

Thanks again for your answers Ani & John Brown

Scott Lance