EF C# Building complex query having multiple predicates within WHERE clause

Question

Part of my API query is an array of IDs to be used in a select query. Here is how the query is created:

var budget_query = _context.Budgets
                .Where(_ => _.IdOwner == q.IdOwner)
                .Where(_ => _.Month >= startdate && _.Month <= enddate)
                .Where(_ => _.IsDeleted == 0);

            if (q.IsCategory != 1)
                budget_query = budget_query.Where(_ => _.IsUncat == 0);

            if (q.IdCurrency != null && q.IdCurrency != 0)
                budget_query = budget_query.Where(_ => _.IdCurrency == q.IdCurrency);

            if (q.IdTagSel.Length > 0)
                foreach (var sel in q.IdTagSel)
                    budget_query = budget_query.Where(_ => _.IdTag == sel);

It results in null response, because obviously one record's field cannot have many different values and in this case it creates SQL query like:

SELECT * FROM budgets WHERE IdTag = value1 AND IdTag = value2, etc

Whereas, I'd like to have something like this as the result:

SELECT * FROM budgets WHERE (IdTag == value1 OR IdTag == value2)

Is there any simple way to achieve this using LINQ expression?

EDIT

It worked for me in the case be means of expression:

 budget_query = budget_query.Where(_ => q.IdTagSel.Any(x=>x ==_.IdTag));

But when I tried to use it for my second query having identical index in the following code:

var trans_query = _context.Transactions
                        .Where(_ => _.TransactionDate > startdate && _.TransactionDate < enddate)
                        .Where(_ => _.IsDeleted == 0)
                        .Where(_ => _.IdOwner == q.IdOwner)
                        .Where(_ => _.IsCredit == q.IsIncome);

            if (q.IdTagSel.Length > 0)
                trans_query = trans_query.Where(_ => q.IdTagSel.Any(y => y == _.IdTag));

            if (q.IdCurrency != null && q.IdCurrency != 0)
                trans_query = trans_query.Where(_ => _.IdCurrency == q.IdCurrency);

            var trans = trans_query.OrderBy(_ => _.TransactionDate).ToList();

I'm getting the following Exception:

"The LINQ expression 'DbSet<Transaction>()\n    .Where(t => t.TransactionDate > __startdate_0 && t.TransactionDate < __enddate_1)\n    .Where(t => (int)t.IsDeleted == 0)\n    .Where(t => t.IdOwner == __q_IdOwner_2)\n    .Where(t => (Nullable<int>)(int)t.IsCredit == __q_IsIncome_3)\n    .Where(t => __q_IdTagSel_4\n        .Any(y => (Nullable<int>)y == t.IdTag))' could not be translated.

I tried to debug it and everything looks ok - all locals are fine and identical to the first case. Now, I'm really confused. Any ideas what could cause this difference?

Answer 1

Any Where(...) creates an AND condition. What you need is to change youre foreach to an .Any() or Contains() condition. Not sure which one is correct for EF

    if (q.IdTagSel.Length > 0)
        budget_query = budget_query.Where(_ => q.IdTagSel.Contains(_.IdTag));

//--- or

    if (q.IdTagSel.Length > 0)
        budget_query = budget_query.Where(_ => q.IdTagSel.Any(x => x == _.IdTag));

that should create

SELECT * FROM budgets WHERE IdTag IN (value1, value2)