Dynamic linq query with multiple/unknown criteria

Question

I am looking to implement a system whereby a use that 'build' conditions and then return the resulting data back from the database. At present, there is a stored procedure which generates SQL on the fly and executes it. This is a particular issue that I want to remove.

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

For example,

from t in Contacts 
where t.Email == "[email protected]" || 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 would be some thing like this (based on above);

Email, Equals, "[email protected]"
Email, Contains, "mydomain" Field1,
Equals, "valuewewant" Field2,
DoesNotEqual, "valuewedontwant"

or

new FieldCriteria
{
FieldName = "Email",
Operator = 1, 
Value = "[email protected]"
}

So using the information that I have, I want to be able to build a query with any number of conditions. I have seen previous links to Dynamic Linq and PredicateBuilder, but am not able to visualise this as a solution to my own problem.

Any suggestions would be appreciated.

Update

Following on from the suggestion about Dynamic Linq, I came up with a very basic solution, using a Single Operator, with 2 Fields and multiple Criteria. A little crude at the moment as coded 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 = "[email protected]"
    });

    conditions.Add(new Condition {
        Field = "Email",
        Operator = Operator.Equals,
        Value = "[email protected]"
    });

    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 an SQL generated of;

-- Region Parameters
DECLARE @p0 VarChar(1000) = 'Chris'
DECLARE @p1 VarChar(1000) = '[email protected]'
DECLARE @p2 VarChar(1000) = '[email protected]'
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="[email protected]"||Email="[email protected]"
Formatted Condition For Field1: Field1="Chris"

Just need clean this up and add the other Operators and it is looking good.

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

Answer 1

The trick with LINQ would be to build an Expression from the data. As an example, to illustrate the example shown:

var param = Expression.Parameter(typeof(MyObject), "t");

var body = Expression.Or(
            Expression.Equal(Expression.PropertyOrField(param, "Email"), Expression.Constant("[email protected]")),
            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 compose the various operations (the same as multiple Where, but simpler).

Answer 2

I think Dynamic LINQ will be one of option. DLINQ allows you to specify part of the LINQ query as "string" and DLINQ then compiles that string to Expression tree so that be passed to the underlying LINQ provider. Your need is also same i.e you need to create Expression trees at runtime.

I would suggest you to make the property Operator in FieldCriteria as an Enum which represent all the required operations (equals, less then etc). Then you will need to write a function that takes a list of FieldCriteria and return a "expression" string which then can be fed into DLINQ to get the expression tree.