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

Solution 1

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.

Solution 2

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

Comments

[Adrian Zanescu]

The whole idea of "LINQ to <insert DB provider>" is to convert static C# compile time expressions to some sort of string to be sent to the underlying database for execution. Your suggestion of using a string to be converted to a linq expression that in turn gets converted to a string seems a little bit redundant :)

[Ankur]

You are correct about "static" expression to SQL, but in the question the user doesn't know about the expression at compile time and hence you need to generate expressions at runtime. DLINQ is one way to do it from "strings" and other way is to use Expression API to create the required expressions at runtime.

[ChrisBint]

does the code above not require you to know the fields/criteria/operator. In my scenario. none are known at compile time and it needs to be completely dynamic in that respect. Forgive me I am mistaken as Lambda functions are a little out of my current knowledge.

[Marc Gravell]

@Chris no, but you would have to write some code that generates the expressions

[Neo]

how to use IN inside where clause?

[Austin Salgat]

This is for AND logic, but what about OR logic?