Filter data with Lambda on database with who can be null

August 10, 2023

I have a class (who performs as a Filter for a query) with this structure:

public class ExpenseFilter
{
    public string? Description { get; set; }
    public int? Currency { get; set; }
    public int? Type { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
}

I want to make a Lambda query which filters from a DB depending the values of the filter. I tried something like this:

var expenses = Data.GetValidExpenses().Where(x => 
                    filter.Currency.HasValue ? x.Currency == (CurrencyTypeEnum)filter.Currency.Value : true &&
                    filter.Type.HasValue ? x.ExpenseType == (ExpenseTypeEnum)filter.Type.Value : true &&
                    !string.IsNullOrEmpty(filter.Description) ? x.Description == filter.Description : true &&
                    x.Date >= filter.StartDate && x.Date <= filter.EndDate)
                .ToList();           

            return expenses;

But for example if I don’t send Description and Currency but I send Type (1) and StartDate (9/9/2023) and EndDate(10/9/2023) to filter to a set of data with this Expense:

Expense()
                {
                    Id = 1,
                    Description = "Test expense",
                    Date = DateTime.Now,
                    Currency = Entities.Enums.CurrencyTypeEnum.USD,
                    Amount = 15000,
                    ExpenseType = Entities.Enums.ExpenseTypeEnum.Fixed,
                    Status = Entities.Enums.ExpenseStatusEnum.Active,
                    WasInstantPayment = true
                }

I get that expense in my response but thats wrong because the date is before the StartDate from the filter.
What is the best way to filter data with Lambda when values can be null?

>Solution :

First thing you can try is to just enclose the checks in parenthesis:

var expenses = Data.GetValidExpenses().Where(x => 
                    (filter.Currency.HasValue ? x.Currency == (CurrencyTypeEnum)filter.Currency.Value : true) &&
                    (filter.Type.HasValue ? x.ExpenseType == (ExpenseTypeEnum)filter.Type.Value : true) &&
                    (!string.IsNullOrEmpty(filter.Description) ? x.Description == filter.Description : true) &&
                    x.Date >= filter.StartDate && x.Date <= filter.EndDate)
                .ToList();

Because due to operator precedence currently the expression is parsed as:

filter.Currency.HasValue 
   ? ... 
   : (true 
      && (filter.Type.HasValue 
            ? ...
            : (true && ....)
          )
      )

So the date filtering will be applied only if none of the previous conditions where specified.

But in general better approach for dynamic AND conditions is to build query dynamically:

IQueryable<Expense> query = Data.GetValidExpenses();

if(filter.Currency.HasValue)
   query = query.Where(x => x.Currency == (CurrencyTypeEnum)filter.Currency.Value);

if(filter.Type.HasValue)
   query = query.Where(x => x.ExpenseType == (ExpenseTypeEnum)filter.Type.Value);

if(!string.IsNullOrEmpty(filter.Description))
   query = query.Where(x => x.Description == filter.Description);

var expenses = query
    .Where(x => x.Date >= filter.StartDate && x.Date <= filter.EndDate)
    .ToList();