Dynamic linq query with multiple / unknown criteria

Question

I am looking to implement a system that uses the condition "build", and then return the received data from the database. There is currently a stored procedure that generates SQL on the fly and executes it. This is a special problem that I want to remove.

My problem comes from the fact that I can have several fields within my criteria, and for each of these fields there can be 1 or more values ​​with different potential operators.

For example,

from t in Contacts where t.Email == " [e-mail] " || t.Email.Contains ("mydomain") where t.Field1 == "valuewewant" where t.Field2 != "valuewedontwant" select t 

The field, criteria and operator are stored in the database (and List<FieldCriteria> ) and will be like this (based on above);

 Email, Equals, " [e-mail] " Email, Contains, "mydomain" Field1, Equals, "valuewewant" Field2, DoesNotEqual, "valuewedontwant" 

or

 new FieldCriteria { FieldName = "Email", Operator = 1, Value = " [e-mail] " } 

Therefore, using the information that I have, I want to be able to create a request with any number of conditions. I have seen previous links to Dynamic Linq and PredicateBuilder, but cannot imagine this as a solution to my own problem.

Any suggestions would be appreciated.

Update

Following Dynamic Linq's suggestion, I came up with a very simple solution using a Single Operator, with two fields and several criteria. A bit rude at the moment, as encoded in LinqPad, but the results are exactly what I wanted;

 enum Operator { Equals = 1, } class Condition { public string Field { get; set; } public Operator Operator { get; set;} public string Value { get; set;} } void Main() { var conditions = new List<Condition>(); conditions.Add(new Condition { Field = "Email", Operator = Operator.Equals, Value = " [e-mail] " }); conditions.Add(new Condition { Field = "Email", Operator = Operator.Equals, Value = " [e-mail] " }); conditions.Add(new Condition { Field = "Field1", Operator = Operator.Equals, Value = "Chris" }); var statusConditions = "Status = 1"; var emailConditions = from c in conditions where c.Field == "Email" select c; var field1Conditions = from c in conditions where c.Field == "Field1" select c; var emailConditionsFormatted = from c in emailConditions select string.Format("Email=\"{0}\"", c.Value); var field1ConditionsFormatted = from c in field1Conditions select string.Format("Field1=\"{0}\"", c.Value); string[] conditionsArray = emailConditionsFormatted.ToArray(); var emailConditionsJoined = string.Join("||", conditionsArray); Console.WriteLine(String.Format("Formatted Condition For Email: {0}",emailConditionsJoined)); conditionsArray = field1ConditionsFormatted.ToArray(); var field1ConditionsJoined = string.Join("||", conditionsArray); Console.WriteLine(String.Format("Formatted Condition For Field1: {0}",field1ConditionsJoined)); IQueryable results = ContactView.Where(statusConditions); if (emailConditions != null) { results = results.Where(emailConditionsJoined); } if (field1Conditions != null) { results = results.Where(field1ConditionsJoined); } results = results.Select("id"); foreach (int id in results) { Console.WriteLine(id.ToString()); } } 

With SQL generated from

 -- Region Parameters DECLARE @p0 VarChar(1000) = 'Chris' DECLARE @p1 VarChar(1000) = ' [e-mail] ' DECLARE @p2 VarChar(1000) = ' [e-mail] ' DECLARE @p3 Int = 1 -- EndRegion SELECT [t0].[id] FROM [Contacts].[ContactView] AS [t0] WHERE ([t0].[field1] = @p0) AND (([t0].[email] = @p1) OR ([t0].[email] = @p2)) AND ([t0].[status] = @p3) 

And console output:

 Formatted Condition For Email: Email=" [e-mail] "||Email=" [e-mail] " Formatted Condition For Field1: Field1="Chris" 

You just need to clear this and add other Operators, and it looks good.

If anyone has comments on this, any input would be appreciated

Answer 1

I think Dynamic LINQ would be one option. DLINQ allows you to specify part of a LINQ query as a "string", and DLINQ then compiles this string into an expression tree to pass it to the underlying LINQ provider. Your need is also such that you need to create expression trees at run time.

I suggest you make the Operator property in FieldCriteria as Enum , which represents all the necessary operations (equal, less, etc.). Then you will need to write a function that takes a FieldCriteria list and returns the string “expression”, which can then be sent to DLINQ to get an expression tree.

Answer 2

The LINQ trick would be to create Expression from the data. As an example, to illustrate the above example:

 var param = Expression.Parameter(typeof(MyObject), "t"); var body = Expression.Or( Expression.Equal(Expression.PropertyOrField(param, "Email"), Expression.Constant(" [e-mail] ")), Expression.Call(Expression.PropertyOrField(param, "Email"), "Contains", null, Expression.Constant("mydomain")) ); body = Expression.AndAlso(body, Expression.Equal(Expression.PropertyOrField(param, "Field1"), Expression.Constant("valuewewant"))); body = Expression.AndAlso(body, Expression.NotEqual(Expression.PropertyOrField(param, "Field2"), Expression.Constant("valuewedontwant"))); var lambda = Expression.Lambda<Func<MyObject, bool>>(body, param); var data = source.Where(lambda); 

In particular, note how AndAlso can be used to compile various operations (just like a few Where , but simpler).

Answer 3

This can simply be done by Linq, where you attach additional operators to the query object. Here is an example.

 query = db.Contacts.Where( ... ); query = query.Where( ... ); query = query.Where( ... ); 

This is a simpler and shorter solution.