Entity Framework filter data by string sql

Question

I am storing some filter data in my table. Let me make it more clear: I want to store some where clauses and their values in a database and use them when I want to retrieve data from a database.

For example, consider a people table (entity set) and some filters on it in another table:

"age" , "> 70"
"gender" , "= male"

Now when I retrieve data from the people table I want to get these filters to filter my data.

I know I can generate a SQL query as a string and execute that but is there any other better way in EF, LINQ?

Answer 1

One solution is to use Dynamic Linq Library , using this library you can have:

filterTable = //some code to retrive it
var whereClause = string.Join(" AND ", filterTable.Select(x=> x.Left + x.Right));
var result = context.People.Where(whereClause).ToList(); 

Assuming that filter table has columns Left and Right and you want to join filters by AND.

My suggestion is to include more details in the filter table, for example separate the operators from operands and add a column that determines the join is And or OR and a column that determines the other row which joins this one. You need a tree structure if you want to handle more complex queries like (A and B)Or(C and D).

Another solution is to build expression tree from filter table. Here is a simple example:

var arg = Expression.Parameter(typeof(People));
Expression whereClause;
for(var row in filterTable)
{
     Expression rowClause;
     var left = Expression.PropertyOrField(arg, row.PropertyName);
     //here a type cast is needed for example
     //var right = Expression.Constant(int.Parse(row.Right));
     var right = Expression.Constant(row.Right, left.Member.MemberType);
     switch(row.Operator)
     {
          case "=":
              rowClause = Expression.Equal(left, right);
          break;
          case ">":
              rowClause = Expression.GreaterThan(left, right);
          break;
          case ">=":
              rowClause = Expression.GreaterThanOrEqual(left, right);
          break;
      }
      if(whereClause == null)
      {
          whereClause = rowClause;
      }
      else
      {
          whereClause = Expression.AndAlso(whereClause, rowClause);
      }
}
var lambda = Expression.Lambda<Func<People, bool>>(whereClause, arg);
context.People.Where(lambda);

this is very simplified example, you should do many validations type casting and ... in order to make this works for all kind of queries.