CODEWITHSUNDEEP
c#entity-frameworklinq.net-corerepository-pattern
Jonh Doe
Jonh Doe

Reputation: 11

LinQ Query Dynamic Conditions List

I'm trying to create a dynamic LINQ query method which receives a List< KeyValuePair< string, string>> to apply the conditions.

Basically, for each element of the List< KeyValuePair (...)>, I want to add a condition to my query.

This project is being developed using the repository-pattern that is very popular in .NET Core.

In SQL I've managed to get the query right:

SELECT * FROM events e
WHERE (e.store = "CO" && e.brand = "YL") || (e.store = "VA" && e.brand = "CD");

The elements of the List<KeyValuePair<string, string>> look like this:

List<KeyValuePair<string, string>> filters = new List<KeyValuePair<string, string>>()
{
  new KeyValuePair<string, string>("CO","YL"),
  new KeyValuePair<string, string>("VA", "CD")
};

Now I need a method that iterates through my List<KeyValuePair<string, string>> and builds a Dynamic LINQ query for every item of the list. If I have a List that has 5 elements I want a query with 5 conditions with OR between them like this:

SELECT * FROM events e
WHERE 
(e.store = "CO" && e.brand = "YL") ||
(e.store = "VA" && e.brand = "CD") ||
(e.store = "FP" && e.brand = "CH") ||
(e.store = "MC" && e.brand = "AR") ||
(e.store = "AB" && e.brand = "CH");

This is my attempt:

var query = Query();
foreach (var item in filters)
{
 query = query.Where(e => e.Store.Equals(i.Key) && e.Brand.Equals(i.Value));
}
var results = await query.ToListAsync(ct);

But this way I can't apply the OR operator between the conditions. Does anyone have any idea of how it can be done?

Thank you.

Upvotes: 1

Views: 3163

Answers (3)

Kazbek
Kazbek

Reputation: 182

This question really interested me, and I tried to compile something working without using third-party libraries. Next, I will present pieces of code that together gave the desired result. But most likely this is all for educational purposes.

First, we need function and class taken from here: https://www.c-sharpcorner.com/UploadFile/c42694/dynamic-query-in-linq-using-predicate-builder/

static Expression<T> Compose<T>(Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
{
    // zip parameters (map from parameters of second to parameters of first)    
    var map = first.Parameters
        .Select((f, i) => new { f, s = second.Parameters[i] })
        .ToDictionary(p => p.s, p => p.f);

    // replace parameters in the second lambda expression with the parameters in the first    
    var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

    // create a merged lambda expression with parameters from the first expression    
    return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
}

class ParameterRebinder : ExpressionVisitor {
    readonly Dictionary<ParameterExpression, ParameterExpression> map;

    ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
    {
        this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
    }

    public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
    {
        return new ParameterRebinder(map).Visit(exp);
    }

    protected override Expression VisitParameter(ParameterExpression p)
    {
        ParameterExpression replacement;

        if (map.TryGetValue(p, out replacement))
        {
            p = replacement;
        }

        return base.VisitParameter(p);
    }
}

Then we can do something like this (I used tuples because they are more in line with the idea):

var tup = new List<Tuple<string, string>> { 
    new Tuple<string, string>("CO", "YL"),
    new Tuple<string, string>("VA", "CD") 
};

Expression<Func<YOUR_TYPE_HERE, bool>> baseFunc = t => false;

foreach (var a in tup)
{
    Expression<Func<YOUR_TYPE_HERE, bool>> addFunc = t => t.store == a.Item1 && t.brand == a.Item2;
    baseFunc = Compose(baseFunc, addFunc, Expression.OrElse);
}


var res = _context.YOUR_ENTITY_NAME.Where(baseFunc).ToList();

I checked that it is executed in one query and evaluate on the database side.

UPD: If you wanna be more performance and want use a bit sql:

var res = context.YOUR_ENTITY_NAME.FromSql("SELECT * FROM YOUR_ENTITY_NAME WHERE (...and...) or (...and...)").ToList();

You can generate "where" part manually and put it as string at the end on "SELECT * FROM YOUR_ENTITY_NAME WHERE". But be carefully with injections. Here you need to use parameters.

Upvotes: 0

Peter
Peter

Reputation: 178

Looks like your easiest bet would be to use Dynamic Linq, since that allows you to create your "(A && B) || (C && D)" as strings at runtime and use them in a Where clause. Alternatively you could build expression trees; see for instance http://stackoverflow.com/questions/6295926/how-build-lambda-expression-tree-with-multiple-conditions

Upvotes: 2

Marco Siffert
Marco Siffert

Reputation: 555

I am assuming you have a mistake in your code. e.Store.Equals(i.Key) should be e.Store.Equals(item.Key). Correct me if I'm wrong.

This should do the job.

query = query.Where(e => filters.Any(f => e.Store.Equals(filter.Key) && e.Brand.Equals(filter.Value)));

Note that this query may be executed on the client and not on the database. See here: https://learn.microsoft.com/en-us/ef/core/querying/client-eval

Meaning that the query will work up until EF Core 2.2, but not with EF Core 3.0. See breaking changes in EF Core 3.0 here: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes

Upvotes: 0

Related Questions