Entity Framework Core : add multiple WHERE clauses as OR

Question

In EF Core I have a list of composite Id's, and I then want to have of those ids from the database.

var crits = new List<MyCrit>()
{
    new MyCrit() {Key1 = "A", Key2 = 3},
    new MyCrit() {Key1 = "B", Key2 = 4}
};

It should end up with a SQL query something like this:

select * 
from MyTable 
where (Key1 = "A" and Key2 = 3) or (Key1 = "B" and Key2 = 4)

I have configured the table to have correct setup, but I cannot get the OR.

Here is my code:

var query = _db.MyTable.AsQueryable();

foreach (var crit in crits)
{
    query = query.Where(m => m.Key1 == crit.Key1 && m.Key2 == crit.Key2);
}

Unfortunately, that results in this SQL statement:

select * 
from MyTable 
where (Key1 = "A" and Key2 = 3) and (Key1 = "B" and Key2 = 4)

I can't figure out how to add inside the loop so it becomes OR.

Probably simplest to make a single Where and join the expressions together yourself. — Andrew McClement
– Andrew McClement, Commented Mar 29, 2022 at 11:42
My first guess is that in your Foreach loop, you update the "query" variable, based on the old value of query (query = query.Where[....]) on every iteration. I don't think it is a wanted behavior here. To avoid that, you can try to define a base query, immutable, and then work with a tempQuery variable (tempQuery = constQuery.Where[...]) — Dylan El Bar
– Dylan El Bar, Commented Mar 29, 2022 at 11:53
Use this function FilterByItems It will generate OR for each record. — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Mar 29, 2022 at 12:05

Svyatoslav Danyliv · Accepted Answer · 2022-03-29 19:13:12Z

2

Use FilterByItems extension and you can simplify your query to the following:

var query = _db.MyTable
    .FilterByItems(crits, (m, crit) => m.Key1 == crit.Key1 && m.Key2 == crit.Key2, true);

answered Mar 29, 2022 at 19:13

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrew McClement · Accepted Answer · 2022-03-29 12:16:09Z

1

I have some hope this should work - just Concat the queries together. It should only result in a single query against the database.

using System.Collections.Generic;
using System.Linq;
                    
public class Program
{
    public static void Main()
    {
        var crits = new List<MyCrit>()
        {
            new MyCrit() {Key1 = "A", Key2 = 3},
            new MyCrit() {Key1 = "A", Key2 = 4}
        };
        
        // _db.Table.AsQueryable()
        var table = Enumerable.Empty<DbValue>().AsQueryable();
        
        // Could use a foreach over crits with an initial value of Enumerable.Empty<DbValue>().AsQueryable()
        var filtered = crits.Select(x => table.Where(y => y.Key1 == x.Key1 && y.Key2 == x.Key2))
            .Aggregate(Enumerable.Empty<DbValue>().AsQueryable(), (x, y) => x.Concat(y));
    }
}

public class MyCrit
{
    public string Key1;
    public int Key2;
}

public class DbValue
{
    public string Key1;
    public int Key2;
    public string OtherData;
}

answered Mar 29, 2022 at 12:16

Andrew McClement

1,4589 silver badges16 bronze badges

1 Comment

Thomas Koelle Over a year ago

I tried this and got this error: System.ArgumentException: must be reducible node

kalit · Accepted Answer · 2022-03-29 13:34:06Z

0

Maybe something like this? (I wrote it from memory and fast so something can work wrong...)

UPDATE Now I could check it and fix and now should work

Expression CreateBasicExpression(ParameterExpression parameterExpression1, string crit)
            {
                var rightPart = Expression.Equal(Expression.Property(parameterExpression1, "Name"), Expression.Constant(crit));
                var leftPart = Expression.Equal(Expression.Property(parameterExpression1, "Surname"), Expression.Constant(crit));
                return Expression.And(leftPart, rightPart);
            }

            var parameterExpression = Expression.Parameter(typeof(MyTable));

            Expression basicExpression = null;

            var crits = new List<string>
            {
                "test1",
                "test2"
            };

            foreach (var crit in crits)
            {
                if (basicExpression is null)
                {

                    basicExpression = CreateBasicExpression(parameterExpression, crit);
                }
                else
                {
                    basicExpression = Expression.Or(basicExpression, CreateBasicExpression(parameterExpression, crit));
                }
            }

            var resultExpression = Expression.Lambda(basicExpression, parameterExpression);

            var castedExpression = (Expression<Func<MyTable, bool>>)resultExpression

edited Mar 29, 2022 at 13:34

answered Mar 29, 2022 at 11:58

kalit

2664 silver badges16 bronze badges

9 Comments

Thomas Koelle Over a year ago

I think maybe this is right, and the only thing I can't implement is the last line where the where I maybe need to cast my basic expression back to a Func<MyObj, bool> or something

kalit Over a year ago

You should be able to cast it back to IQueryable<MyObj> - maybe try query = query.Where(exp) or explicit cast?

Thomas Koelle Over a year ago

Thanks, that worked, but then Runtime I got this error: System.InvalidOperationException: The binary operator Or is not defined for the types 'System.Func2[MyTable, Boolean]' and 'System.Func2[MyTable, Boolean]'

kalit Over a year ago

can you show how did you do it?

kalit Over a year ago

Pls looks now on it

|

Collectives™ on Stack Overflow

Entity Framework Core : add multiple WHERE clauses as OR

3 Answers 3

Comments

1 Comment

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related