CODEWITHSUNDEEP
c#.netentity-frameworklinq
Kal800
Kal800

Reputation: 63

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

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?

Upvotes: 0

Views: 322

Answers (1)

Demetrius Axenowski
Demetrius Axenowski

Reputation: 761

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)

Upvotes: 1

Related Questions