I'm trying to filter dbset of entities of such pseudo-structure (field1, field2, field3) using request of IEnumerable of SomeModel where SomeModel contains pairs (field1, field2) (the same fields subset)

I've tried

var ordersList3 = await _dbContext.MyEntities.
AsNoTracking().
Where(a => request.Contains(new SomeModel() { field1 = a.field1, field2 = a.field2})).
ToListAsync();

but it doesn't work

could you please suggest the correct way of filtering a dbset by list of models containing fields subset?

CodePudding user response：

You get the error because entity framework cannot translate into sql query the expression. This is due to the predicate inside the where clause. The correct implementation would be:

var ordersList3 = await _dbContext.MyEntities.
AsNoTracking().
Where(a => request.Any(r => r.field1 == a.field1 && r.field2 == a.field2})).
ToListAsync();

CodePudding user response：

I do not know if this is nessessary in your case, but sometimes you need to write down the expressions directly. As this can be sort of fun, here you go:

public static Expression<Func<PseudoStructure, bool>> GetPredicate<PseudoStructure>(
    IEnumerable<PseudoStructure> request) {
    var parameter = Expression.Parameter(typeof(PseudoStructure), "o");
    var expression = request.Aggregate(
        (Expression)null,
        (acc, next) => MakeBinary(
            acc, 
            CompareInstances(parameter, next),
            ExpressionType.Or));
    return Expression.Lambda<Func<PseudoStructure, bool>>(expression, parameter);
}

This method generates an Expression<Func<PseudoStructure, bool>> which consists of Expressions checking equality of instances:

private static Expression CompareInstances<PseudoStructure>(
    ParameterExpression parameter,
    PseudoStructure constant) 
        => typeof(PseudoStructure)
        .GetFields()
        .Select(fieldInfo => Expression.Equal(
            Expression.Constant(fieldInfo.GetValue(constant)),
            Expression.Field(parameter, fieldInfo)))
        .Aggregate(
            (Expression)null,
            (expression, binaryExpression) => MakeBinary(expression, binaryExpression, ExpressionType.And)
        );
    }
}

Please note that equality means equality of the values of the fields. MakeBinary is a simple wrapper for Expression.MakeBinary:

private static Expression MakeBinary(Expression left, Expression right, ExpressionType expressionType)
    => left == null || right == null ?
        left ?? right
        : Expression.MakeBinary(expressionType, left, right);

You can use this like so:

public struct Foo
{
    public int A;
    public decimal B;
    public decimal C;
} 

var values = new List<Foo> {
    new Foo {A = 1, B = 2, C = 3},
    new Foo {A = 4, B = 5, C = 6},
    new Foo {A = 7, B = 8, C = 9},
    new Foo {A = 10, B = 11, C = 12}
};

var expression =
    GetPredicate(new[] { 
        new Foo {A = 1, B = 2, C = 3}, 
        new Foo {A = 10, B = 11, C = 12}
    });

var result = values.AsQueryable().Where(expression).ToList();