Querying Entity with LINQ using Dyanmic Field Name

Question

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:

dynamically create lambdas expressions + linq + OrderByDescending

Parameter problem with Expression.Lambda()

LINQ: Passing lambda expression as parameter to be executed and returned by method

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);

Answer 1

Have you tried getting the value from PropertyInfo?

entities.People.Where(p => (p.GetType().GetProperty(key).GetValue(p, null) as string) == fieldValueDictionary[key])

Answer 2

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

InvalidOperationException: No method 'Where' on type 'System.Linq.Queryable' is compatible with the supplied arguments

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 Bowen

Answer 3

  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; ;
        }