How to chain OR clauses, with LINQ?

user_1549298 0
Viewed 2159

I've got a scenario where I need to build a SQL query, using LINQ, that includes a variable amount of OR clauses. I'm writing a function that will build the query based on some input. The function definition looks something similar to ...

function BuildQuery(ICollection<ColumnsThatNeedToBeTrue> columns)
{
  ...
}

So, I'm given a collection of columns that I need to check for true, and the check needs to use OR clauses.

If the columns array contains A and B, I'd need the query to check if column A is true OR column B is true.

If the columns array contains A, B and C, I'd need to query and check if A OR B OR C are true.

I don't know how to do this all within a single Where, because I don't know of a way to progressively tack on additional || clauses. I'm not sure how I'd include additional OR checks in the below, based on the input array.

var query = entities.Where(m => m.A == true || m.B == true ...)

I cannot chain Where functions, each for their own column check, because it builds that query using AND clauses and I need OR.

Is there a way to build out a query like this, using LINQ?

linq c#
2 Answers

I'm currently using Linq2DB, and needed to get many records in a single query, filtered like this:

WHERE [...some other filters...] AND (
    (TipoConfiguracionId = $1 AND ConfiguracionId = $2) OR
    (TipoConfiguracionId = $3 AND ConfiguracionId = $4) OR
    (TipoConfiguracionId = $5 AND ConfiguracionId = $6))

This query should be reading a Configuration table that is not so big, so ended up building my own Expression. This function takes a list of records to be matched:

public async static Task<List<Data.Configuraciones>> GetMany(DataConnection db, List<Data.Configuraciones> list)
{
    // Main query
    var q = from p in db.Configuraciones
        where [...some other normal filters...]
        select p;
    
    Expression completeCondition = null;
    Expression itemCondition = null;
    
    var entityParam = Expression.Parameter(typeof(Data.Configuraciones), "e");
    var propTipoConfiguracionId = Expression.PropertyOrField(entityParam, "TipoConfiguracionId");
    var propConfiguracionId = Expression.PropertyOrField(entityParam, "ConfiguracionId");

    // Iterates the list
    foreach (var config in list)
    {
        // Assembles the inner condition: (TipoConfiguracionId = $1 AND ConfiguracionId = $2)
        itemCondition = Expression.Equal(propTipoConfiguracionId, Expression.Constant(config.TipoConfiguracionId));
        itemCondition = Expression.And(itemCondition, Expression.Equal(propConfiguracionId, Expression.Constant(config.ConfiguracionId)));

        // Adds the inner condition to the complete condition, concatenating it with an OR operator
        if (completeCondition == null)
            completeCondition = itemCondition;
        else
            completeCondition = Expression.Or(completeCondition, itemCondition);
    }

    // Appends the complete condition to the main query
    var lambda = Expression.Lambda<Func<Data.Configuraciones, bool>>(completeCondition, entityParam);
    q = q.Where(lambda);

    return await q.ToListAsync();
}
user_13918720
Related