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

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?

CodePudding user response：

You can use the Contains method, something like this:

.Where(c => (new List<IdTagType>{value1,value2}).Contains(IdTag))

CodePudding user response：

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)