© 2026 Hedgehog Software, LLC

TwitterGitHubDiscord
More
CommunitiesDocsAboutTermsPrivacy
Search
Star
Setup for Free
C#C
C#•3y ago•
10 replies
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);
public record GetItemsFilter(string? Title, bool? IsCompleted);

and only apply the
Where
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();
}
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();
}
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();
}
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));
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
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
C# banner
C#Join
We are a programming server aimed at coders discussing everything related to C# (CSharp) and .NET.
61,871Members
Resources
Was this page helpful?

Similar Threads

Recent Announcements

Similar Threads

SQL EFCore
C#CC# / help
2y ago
❔ efcore querying
C#CC# / help
3y ago
✅ LINQ / EFCore
C#CC# / help
3y ago
❔ EFCore migrations
C#CC# / help
3y ago