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?

Womera

With entity framework you can use Where function like this: ob.Where(p => p.age > 70 && gender == "male"). – Osteomyelitis

the age and the 70 are string filters that are retrieved from DB. so how can I write this line of code? Read the question again please. thanks. – Womera

You can use this expression builder – Wina

Any internal thing like this? – Womera

Just a detail : I would at least put the operator in a separate field – Harborage

@RaphaëlAlthaus I am agree with you. I need to at least rewrite that builder to get the operator in addition to filter and value. Any other Internal soloution? – Womera

Why do you want to split the entire where clause at all? If you do split then reconstructing would at least potentially be a headache. – Hattie

@PaulZahra It could make parsing easier (or rather avoid the need to parse to some extent since the predicate is already structured). Note that you ultimately want to build LINQ expression trees, not strings. T.Rahgooy suggests using Dynamic LINQ to parse an arbitrary language, and it's definitely not a bad idea, though if OP is constructing predicates dynamically (not just some values) then OP will have to resort to some string concatenation (not generally recommended) or more advanced codegen (which is convoluted considering you can simply use first-class objects to build expression trees). – Bushed

@Womera If you'd like your question to be more general/accurate, you should consider rephrasing it as something like "How can I filter data using dynamically constructed predicates retrieved from a database table?". But then maybe your instinctive phrasing will be the same as that of other people, making it easier to find. Your call. – Bushed

Please add C# and expression-trees in your question's tags – Loesch

@T.Rahgooy Good idea, tagged linq-expressions to be more specific (might want dynamic-linq too). Not sure about C#, OP didn't specify. – Bushed

@T.Rahgooy , you are great and your answer was greater. I am working on filter table and the expression tree to be more flexible. I want to accept your answer but i want to post a completed answer after reaching desired result by myself. Then i will accept yours. thanks a lot. I wish GOD give you anything you wish. – Womera

@Bushed , As I got, you are so clever and so smart. your vision about my question was even better of mine! thank you a lot for your help. – Womera

Another thought might be to serialize / deserialize you expression trees and save them to your DB. I have done something similar and it works quite well. Have a look here. Then you can pass the expression to your LINQ Where clause. – Chemar

I have also used the Serialize.Linq library which allows for serialization to different formats. – Chemar

@Chemar Depending on how easily you can split and compose sub-expressions in serialized form (I assume very easily), this sounds like a great choice as it allows OP to forgo with creating a new language with a specific code generator and parser. I assume these filters are generated dynamically from a UI of some sort; the UI can simply create LINQ expression trees and serialize each node separately in order to insert it into a table with a hierarchical structure. You should post it as an answer. – Bushed

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.

Loesch

Your example will greatly help OP visualize what needs to be done. Let me just suggest something for interested readers to figure out their next step: consider the interpreter pattern or the visitor pattern (comparison). This usually yields a design more easily maintainable, but the idea is the same of course. – Bushed

Both your answers are brilliant. but i think i like the second one more. THANKS a lot. – Womera

You are welcome. The second one is more challenging, I congratulate you for your courage!! – Loesch

Answer 2

This is an interesting question. First off, make sure you're honest with yourself: you are creating a new query language, and this is not a trivial task (however trivial your expressions may seem).

If you're certain you're not underestimating the task, then you'll want to look at LINQ expression trees (reference documentation).

Unfortunately, it's quite a broad subject, I encourage you to learn the basics and ask more specific questions as they come up. Your goal is to interpret your filter expression records (fetched from your table) and create a LINQ expression tree for the predicate that they represent. You can then pass the tree to Where() calls as usual.

Bushed

Answer 3

Without knowing what your UI looks like here is a simple example of what I was talking about in my comments regarding Serialize.Linq library

    public void QuerySerializeDeserialize()
    {
            var exp = "(User.Age > 7 AND User.FirstName == \"Daniel\") OR User.Age < 10";
            var user = Expression.Parameter(typeof (User), "User");
            var parsExpression = 
                   System.Linq.Dynamic.DynamicExpression.ParseLambda(new[] {user}, null, exp);

            //Convert the Expression to JSON
            var query = e.ToJson();

            //Deserialize JSON back to expression
            var serializer = new ExpressionSerializer(new JsonSerializer());
            var dExp = serializer.DeserializeText(query);

            using (var context = new AppContext())
            {
                var set = context.Set<User>().Where((Expression<Func<User, bool>>) dExp);
            }

   }

You can probably get fancier using reflection and invoking your generic LINQ query based on the types coming in from the expression. This way you can avoid casting the expression like I did at the end of the example.

Chemar