C# IQueryable use dictionary inside Where throws Unable to create a constant value of type Exception

Question

I am using the dictionary inside the IQueryable lambda linq throws the

Unable to create a constant value of type 'System.Collections.Generic.KeyValuePair`2

Code :

Dictionary<int, int> keyValues = new Dictionary<int, int>();

IQueryable<Account> = context.Account
    .Where(W => keyValues
        .Where(W1 => W1.Key == S.AccountID)
        .Where(W1 => W1.Value == S.Balance)
        .Count() > 0);

Details:

I have the data inside the dictionary like this

AccountID Balance

1 1000

2 2000

3 3000

I want the user which have the (ID = 1 AND Balance = 1000) OR (ID = 2 AND BALANCE = 2000) OR (ID = 3 AND BALANCE = 3000)

So how can I write the lambda for it ?

Edited

Thanks @caesay, Your answer help me lots.

I want one more favor from you.

From you answer I create the expression which look like below:

private static Expression<Func<Accounting, bool>> GenerateExpression(Dictionary<int, int> lstAccountsBalance)
{
    try
    {
        var objAccounting = Expression.Parameter(typeof(Accounting));
        Expression expr = null;
        const bool NOT_ALLOWED = false;

        if (lstAccountsBalance != null && lstAccountsBalance.Count > 0)
        {
            var clauses = new List<Expression>();

            foreach (var kvp in lstAccountsBalance)
            {
                clauses.Add(Expression.AndAlso(
                    Expression.Equal(Expression.Constant(kvp.Key), Expression.Property(objAccounting, nameof(Accounting.ID))),
                    Expression.Equal(Expression.Constant(kvp.Value), Expression.Property(objAccounting, nameof(Accounting.Balance)))
                ));
            }

            expr = clauses.First();
            foreach (var e in clauses.Skip(1))
            {
                expr = Expression.OrElse(e, expr);
            }

            var notAllowedExpr = Expression.AndAlso(
                    Expression.Equal(Expression.Constant(NOT_ALLOWED), Expression.Property(objAccounting, nameof(Accounting.ALLOWED))),
                    Expression.Equal(Expression.Constant(true), Expression.Constant(true))
                );

            expr = Expression.And(notAllowedExpr, expr);
        }

        var allowedExpr = Expression.AndAlso(
                Expression.Equal(Expression.Constant(!NOT_ALLOWED), Expression.Property(objAccounting, nameof(Accounting.ALLOWED))),
                Expression.Equal(Expression.Property(objAccounting, nameof(Accounting.ID)), Expression.Property(objAccounting, nameof(Accounting.ID)))
            );

        if (expr != null)
        {
            expr = Expression.OrElse(allowedExpr, expr);
        }
        else
        {
            expr = allowedExpr;
        }

        return Expression.Lambda<Func<Accounting, bool>>(expr, objAccounting);
    }
    catch (Exception ex)
    {
        throw objEx;
    }
}

After that I compiled the expression like this:

Expression<Func<Accounting, bool>> ExpressionFunctions = GenerateExpression(lstAccountsBalance);

var compiledExpression = ExpressionFunctions.Compile();

And I used like this:

.Select(S => new
{
    Accounting = S.Accounts
        .Join(context.AccountInfo,
                objAccounts => objAccounts.ID,
                objAccountInfo => objAccountInfo.ID,
                (objAccounts, objAccountInfo) => new Accounting
                {
                    ID = objAccounts.ID,
                    Balance = objAccountInfo.Balance,                               
                })
        .Where(W => W.ID == user.ID)
        .AsQueryable()
        .Where(W => compiledExpression(W))
        .Select(S1 => new Accounting()
        {
            ID = S1.ID,
            Balance = S1.Balance
        })
        .ToList(),
}

And it throws the exception with the message:

System.NotSupportedException: The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.

Without Compile

Without the compile it works like charm. It gives the output want I want.

 Expression<Func<Accounting, bool>> ExpressionFunctions = GenerateExpression(lstAccountsBalance);

Use:

.Select(S => new
{
    Accounting = S.Accounts
        .Join(context.AccountInfo,
                objAccounts => objAccounts.ID,
                objAccountInfo => objAccountInfo.ID,
                (objAccounts, objAccountInfo) => new Accounting
                {
                    ID = objAccounts.ID,
                    Balance = objAccountInfo.Balance,                               
                })
        .Where(W => W.ID == user.ID)
        .AsQueryable()
        .Where(ExpressionFunctions)
        .Select(S1 => new Accounting()
        {
            ID = S1.ID,
            Balance = S1.Balance
        })

Thank you..

Answer 1

Everything inside of a EF linq query needs to be compiled to an Expression tree, and then to SQL, but the dictionary is an IEnumerable so there is no way that EF could know how to compile that.

You can either build the expression tree yourself, or use the System.Linq.Dynamic nuget package to build the sql yourself.

The example using System.Linq.Dynamic, first install the nuget package and add the using to the top of your file, then there will be a Where overload that takes a string as a parameter:

context.Account.Where(
    String.Join(" OR ", keyValues.Select(kvp => $"(ID = {kvp.Key} AND Balance = {kvp.Value})")));

Essentially, everything inside of the Where clause will be executed directly as SQL, so be careful to use the Where(string, params object[]) overload to paramaterize your query if accepting user input.

The expression tree (untested) approach might look like the following:

var account = Expression.Parameter(typeof(Account));
var clauses = new List<Expression>();
foreach(var kvp in keyValues)
{
    clauses.Add(Expression.AndAlso(
        Expression.Equal(Expression.Constant(kvp.Key), Expression.Property(account, nameof(Account.AccountID))),
        Expression.Equal(Expression.Constant(kvp.Value), Expression.Property(account, nameof(Account.Balance)))
    ));
}

var expr = clauses.First();
foreach (var e in clauses.Skip(1))
    expr = Expression.OrElse(e, expr);

context.Account.Where(Expression.Lambda<Func<Account, bool>>(expr, account));

Essentially inside the foreach loop we're creating all of the (ID = ... AND Balance = ...) and then at the end we join them all with an OR.