Entity Framework skip multiple condition in where clause when any param can be null or empty

Question

I am trying to fetch the records from db where any param can be null or empty, this is what I have, but not giving me the correct result. From the five params any one can be null but one must have value.

 public async Task<IEnumerable<SomeTable>> GetByCondition(string param1, string param2, string param3, string param4, int param5)
    {

    var data = await _dataContext.SomeTable.Where(a => a.param1 == (string.IsNullOrEmpty(param1) ? "" : param1)
                                              && a.param2 == (string.IsNullOrEmpty(param2) ? "" : param2)
                                              && a.param3 == (string.IsNullOrEmpty(param3) ? "" : param3)
                                              && a.param4 == (string.IsNullOrEmpty(param4) ? "" : param4)
                                              && a.param5 == (param5 == 0 ? 0 : param5)).ToListAsync();
    return data;
}

Answer 1

Expanding on the link provided in the comments, the approach you are using will result in the conditionality of the parameters needing to be translated down into SQL. It can work, but it is quite bulky to write. Your scenario is likely not working because the record params could be #null, but your resulting query would be comparing rows without a param requirement to an empty string. What you would likely need would look more like:

var data = await _dataContext.SomeTable
    .Where(a => a.Param1 == string.IsNullOrEmpty(param1) ? a.param1 : param1
        && a.param2 == string.IsNullOrEmpty(param2) ? a.param2 : param2
        && a.param3 == string.IsNullOrEmpty(param3) ? a.param3 : param3
        && a.param4 == string.IsNullOrEmpty(param4) ? a.param4 : param4
        && a.param5 == !param5.HasValue() ? a.param5 : param5)
    .ToListAsync();

... and that may still not have the desired behaviour depending on how you really want to handle #null parameters and/or #null column values.

However, as mentioned this will result in any null check conditions being written into the SQL and computed for every parameter in every query. A better solution when dealing with simple scenarios that are ANDed together is to move the conditionality into code and only append filter conditions if a condition is present:

var query = _dataContext.SomeTable.AsQueryable();
if (!string.IsNullOrEmpty(param1))
    query = query.Where(a => a.param1 == param1);
if (!string.IsNullOrEmpty(param2))
    query = query.Where(a => a.param2 == param2);
if (!string.IsNullOrEmpty(param3))
    query = query.Where(a => a.param3 == param3);
if (!string.IsNullOrEmpty(param4))
    query = query.Where(a => a.param4 == param4);
if (param5.HasValue)
    query = query.Where(a => a.param5 == param5.Value);

var data = await query.ToListAsync();

This results in an SQL query that will only include conditions on parameters that have values to filter. The use of AsQueryable() initially is only needed to cast the resulting query variable to IQueryable<SomeTable> rather than DbSet<SomeTable>. Alternatively you can just start it off use explicit casting:

IQueryable<SomeTable> query = _dataContext.SomeTable; 

... or if you have a non-conditional Where clause, the AsQueryable() is not needed, i.e.:

var query = _dataContext.SomeTable
    .Where(a => a.IsActive);  // query will be IQueryable<SomeTable>

For more complex scenarios where you might want things like OR conditions then I would recommend using a dynamic predicate builder solution that can build a Where clause expression conditionally with the desired logic. It isn't necessary where conditions are ANDed together as this is the default behaviour when EF encounters multiple Where clauses.