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?

Have you tried budget_query.Where(x=>q.IdTagSel.Contains(x.IdTag )) (As a side note, please don't use underscore for variable names, unless for discards.) — Mat J
– Mat J, Commented Apr 21, 2022 at 14:29

Demetrius Axenowski · Accepted Answer · 2022-04-21 14:22:55Z

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)

answered Apr 21, 2022 at 14:22

Demetrius Axenowski

7617 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kal800 Over a year ago

Second option worked for me, thank you, cheers!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related