c# - 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).