Entity framework core add multible where 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 SQL 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);
}

Unfortunatly that gives me this SQL:

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, Mar 29 '22 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, Mar 29 '22 at 11:53
Use this function [FilterByItems](https://stackoverflow.com/a/67666993/10646316) It will generate OR for each record. — Svyatoslav Danyliv, Mar 29 '22 at 12:05

score 1 · Answer 1 · answered Mar 29 '22 at 12:16

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;
}

I tried this and got this error: System.ArgumentException: must be reducible node — Thomas Koelle, Mar 29 '22 at 12:42

score 1 · Accepted Answer · answered Mar 29 '22 at 19:13

1

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 '22 at 19:13

Svyatoslav Danyliv

21,911
3
16
32

kalit · Answer 3 · 2022-03-29T13:34:06.403

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 '22 at 13:34

answered Mar 29 '22 at 11:58

kalit

256
4
15

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 or something – Thomas Koelle Mar 29 '22 at 12:41
You should be able to cast it back to IQueryable - maybe try `query = query.Where(exp)` or explicit cast? – kalit Mar 29 '22 at 13:02
Thanks, that worked, but then Runtime I got this error: System.InvalidOperationException: The binary operator Or is not defined for the types 'System.Func`2[MyTable, Boolean]' and 'System.Func`2[MyTable, Boolean]' – Thomas Koelle Mar 29 '22 at 13:05
can you show how did you do it? – kalit Mar 29 '22 at 13:06
To get it to compile I just did this: var funcQuery = Expression.Lambda>(first); But then when I ran the code I didn't even get to it because the OR method didn't work – Thomas Koelle Mar 29 '22 at 13:07
1

Pls looks now on it – kalit Mar 29 '22 at 13:35
I get the same runtime error, and I think I get it because where I have Key1+Key2 as crit then your example have just a string. But maybe I am just not converting your example to my example correct. I would assume your name/surname somehow is my Key1/Key2, but I don't see how that match with "test1", "test2" – Thomas Koelle Mar 29 '22 at 13:54
That happens in CreateBasicExpression – kalit Mar 29 '22 at 14:20
The one I set as answer 100% solved my issue. So I think people can use that – Thomas Koelle Mar 30 '22 at 12:59

Entity framework core add multible where as OR

3 Answers3

Linked