Using LINQ to Filter Multi Dimensional Criteria

Question

I'm trying to build a search query using LINQ to pass it to Entity Framework utilizing the criteria passed by the user. The criteria are two dimensional.

The criteria include "FieldName" and "SearchOperator", for example:

FieldName may include "FirstName", "LastName", "DateOfBirth", ...etc

SearchOperator may include "Equals", "BeginsWith", "EndsWith", "Greater", ...etc

As you can see there are too many conditions to be handled. What is the best way to implement the code?

My current code looks like this, but I feel like there should be a better way to do it.

IQueryable<Employee> query = dbContext.Employee;

    switch (FieldName)
    {
        case "FirstName":
            switch (SearchOperator)
            {
                case "Equals":
                    query = query.Where(x => x.FirstName.Equals(SearchValue));
                    break;
                case "BeginsWith":
                    query = query.Where(x => x.FirstName.StartsWith(SearchValue));
                    break;
            }
            break;
        case "LastName":
            switch (SearchOperator)
            {
                case "Equals":
                    query = query.Where(x => x.LastName.Equals(SearchValue));
                    break;
                case "BeginsWith":
                    query = query.Where(x => x.LastName.StartsWith(SearchValue));
                    break;
            }
    }

Tanveer Ali · Answer 1 · 2016-01-28T22:00:24.853

2

You can use dynamic linq:

check the article:

http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library.

using dynamic linq you can do like this:

switch (SearchOperator)
                    {
                        case "Equals":
                            query = query.Where(String.Format("{0}={1}",FieldName,SearchValue));
                            break;
                        case "Contains":
                            query = query.Where(String.Format("{0}.Contains({1})",FieldName,arrayOfSearchValues));
                        break;
                        case "StartsWith":
                            query = query.Where(String.Format("{0}.StartsWith({1})",FieldName,SearchValue));
                        break;
                      case "Greater":
                          query = query.Where(String.Format("{0}>{1}",FieldName,SearchValue));
                            break;

                    }

edited Jan 28 '16 at 22:00

answered Jan 28 '16 at 21:45

Tanveer Ali

151
7

1

Can you please update your answer with String.StartsWith, String.Contains, and if possible Date greater than – EGN Jan 28 '16 at 21:49
Have you tested if your code is working? It is telling me cannot convert string to System.Ling.Expression.Expression> – EGN Jan 29 '16 at 14:11
I like the approach but in order to make it work you have to install Dynamic Library which adds two dependencies to your project. That's why I'm not selecting it as a correct solution. But thank you! voting up – EGN Feb 01 '16 at 14:07
Thanks. yeah i know its adding Dynamic Linq dependency but that's required if you are doing linq stuff dynamically. – Tanveer Ali Feb 01 '16 at 18:51

Maxim Fleitling · Answer 2 · 2016-01-28T22:08:57.597

You can keep you filter dynamicaly in a Dictionary:

       IQueryable<Employee> query = dbContext.Employee;

        Dictionary<Tuple<string, string>, Func<Employee, string, bool>> _filter = new Dictionary<Tuple<string, string>, Func<Employee, string, bool>>()
        {
           { new Tuple<string, string>("FirstName","Equals"),   (x,s) => x.FirstName.Equals(s)},
            { new Tuple<string, string>("FirstName","BeginsWith"),   (x,s) => x.FirstName.StartsWith(s)},
             { new Tuple<string, string>("LastName","Equals"),   (x,s) => x.LastName.Equals(s)},
              { new Tuple<string, string>("LastName","BeginsWith"),   (x,s) => x.LastName.StartsWith(s)},

        }; 


        public Employee Get()
        {
             Func<Employee, string, bool> filter = _filter.FirstOrDefault(k => k.Key.Item1 == FieldName && k.Key.Item2 == SearchOperator).Value;

            return query.FirstOrDefault(e => filter(e, SearchValue));
        }

So it is extendable if you need.

score 0 · Answer 3 · edited May 23 '17 at 12:15

If you do not need high performance or you have small database, you may use IEnumerable to chaining where statements like this:

IEnumerable<Employee> query = dbContext.Employee;
foreach(var searchOperator in searchOperators)
{
     query = query.Where(n=> ....); 
}

otherwise you can try to build expression tree. Take a look here: Expression.Lambda and query generation at runtime, simplest "Where" example

For example:

var employee = Expression.Parameter(typeof(Employee), "employee");
var exprList = new List<BinaryExpression>();
foreach(var searchOperator in searchOperators)
{
  switch(FieldName)
  {
      case "FirstName":
      {   
         var property = Expression.Property(item, "FirstName");

         switch(SearchOperator)
         {
             case "Equal":
                 var equalTo = Expression.Constant(SearchFirstName);
                 var expr = Expression.Equal(property, equalTo);
                 exprList.Add(expr);
                 break;
             case "StartWith":
                 ....
         }


         break;
      }
  }
}
var combined = exprList.Aggregate((n,m)=> Expression.And(n,m));
Expression<Func<Employee, bool>> expr = Expression.Lambda<Func<Item, bool>>(combined, employee);
var output = dbContext.Employee.Where(expr);

( I didn't test code, it may have bugs but I'm convinced that it should look more or less like this) Basicly, it is more efficient because Entity Framework is converting your query to SQL query by traversing Expression>.

Using LINQ to Filter Multi Dimensional Criteria

3 Answers3