C#C
C#•3y ago
TeBeCo

EFCore Optional filter / Composite filter

I'm trying to run targeted filtering with a filter that look like this:

public record GetItemsFilter(string? Title, bool? IsCompleted);

and only apply the Where if the value is not null
I have it working like this:
public async Task<List<Items>> GetItems(GetItemsFilters getItemsFilter)
{
  IQueryable<Items> itemsQuery = _dbContext.Items;

  if(getItemsFilter.Title != null)
  {
    itemsQuery = itemsQuery.Where(item => item.Title == getItemsFilter.Title);
  }

  if(getItemsFilter.IsCompleted != null)
  {
    itemsQuery = itemsQuery.Where(item => item.IsCompleted == getItemsFilter.IsCompleted);
  }

  return await items.ToListAsync();
}

I wanted to see if it was possible with something like that:
public async Task<List<Items>> GetItemsAsync(GetItemsFilters getItemsFilter)
{
  return await _dbContext
    .Items
    .Where(item => item.Title == getItemsFilter.Title); // won't build because nullable
    .Where(item => item.IsCompleted == getItemsFilter.IsCompleted); // won't build because nullable
    .ToListAsync();
}

the "hack" for nullable in C# would make it look like:
public async Task<List<Items>> GetItems(GetItemsFilters getItemsFilter)
{
  return await _dbContext
    .Items
    .Where(item => item.Title == getItemsFilter.Title ?? "") // bad SQL generated in WHERE clause
    .Where(item => item.IsCompleted == (getItemsFilter.IsCompleted ?? item.IsCompleted)); // bad SQL generated in WHERE clause
    .ToListAsync();
}

if I do:
var items = await GetItemsAsync(new GetItemsFilter(null, null));

it generates a
SELECT ...
FROM ...
WHERE title ... '' and iscompleted == coelse(null .... ;-- BAD SHOULD NOT HAVE BEEN GENERATE


what's the way to have it NOT adding that SQL ? with a simplier C# syntax
Was this page helpful?