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.

CodePudding user response：

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

CodePudding user response：

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