Database queries

edit

ExpressionQueryBuilder<T> reads the same syntax as QueryBuilder<T>, but its handlers are expressions. The query becomes an Expression<Func<T, bool>> that Entity Framework Core translates to SQL, so the database does the filtering:

// Define a query builder for your entity
var queryBuilder = new ExpressionQueryBuilder<Customer>();

// Add handlers for string properties (uses Contains for partial matching).
// Case sensitivity follows the provider: the database collation for EF Core, ordinal for LINQ to Objects.
// Pass a StringComparison to force one, but note that EF Core cannot translate it.
queryBuilder.AddHandler("name", customer => customer.FullName);

// Add handlers for comparable types (supports range and comparison operators)
queryBuilder.AddHandler<int>("age", customer => customer.Age);
queryBuilder.AddHandler<DateTime>("created", customer => customer.CreatedAt);

// Optional: handle free-text search across multiple fields
queryBuilder.SetFreeTextHandler(text =>
    customer => customer.FullName.Contains(text) || customer.Email.Contains(text));

// Build the query
var query = queryBuilder.Build("name:John AND age>=21");

// Apply to an IQueryable (e.g., an EF Core DbSet)
var customers = new[]
{
    new Customer("John Doe", "john@example.com", 30, new DateTime(2024, 1, 1)),
    new Customer("Johnny Smith", "johnny@example.com", 18, new DateTime(2024, 1, 1)),
    new Customer("Jane Doe", "jane@example.com", 40, new DateTime(2024, 1, 1)),
};
var results = query.Apply(customers.AsQueryable()).ToList();

Assert.Equal(["John Doe"], results.Select(customer => customer.FullName));

with the following entity type:

internal sealed record Customer(string FullName, string Email, int Age, DateTime CreatedAt);

Apply adds a Where to any IQueryable<T>. On an Entity Framework Core DbSet, run the result as usual, for instance with await query.Apply(dbContext.Customers).ToListAsync().

Kinds of handlers

RegistrationGeneratesOperators
AddHandler(key, x => x.Text)x.Text != null && x.Text.Contains(value):, =, <>
AddHandler(key, x => x.Text, matchMode)x.Text != null && x.Text == value, or StartsWith, EndsWith, Contains:, =, <>
AddHandler<TValue>(key, x => x.Value) for a type that can be orderedComparisons with the parsed valueAll, and ranges
AddHandler<TValue>(key, x => x.Value) for other typesx.Value == value:, =, <>
AddHandler(key, value => …)The expression your function returns for the value:, =, <>
AddHandler(key, op, value => …)The expression your function returns, for one operatorThe one you give
  • A string handler uses string.Contains(string) unless you pick another match mode. Its case sensitivity is the provider's: the collation of the column for Entity Framework Core, ordinal in memory. Passing a StringComparison makes the query run in memory only: Entity Framework Core cannot translate those overloads.
  • Numbers, dates (DateTime, DateTimeOffset, DateOnly), TimeOnly, TimeSpan, decimal, enums and the other types that define < can be ordered, and their nullable versions. bool and Guid support equality only.
  • <> negates the : handler of the key when no handler is registered for <> itself.
  • A value that cannot be parsed generates a condition that is always false.

The values of the query are not inserted in the expression as constants. They are read from an object, like a captured variable, which LINQ providers such as Entity Framework Core turn into query parameters.

String match modes

Pass a StringMatchMode to choose how a string column matches the value:

ModeGenerates
Contains (default)x.Text != null && x.Text.Contains(value)
Exactx.Text != null && x.Text == value
StartsWithx.Text != null && x.Text.StartsWith(value)
EndsWithx.Text != null && x.Text.EndsWith(value)

In memory, Contains and Exact are ordinal, but string.StartsWith(string) and string.EndsWith(string) compare with the current culture, which ignores some characters such as \0: a value made only of them matches every text. Pass a StringComparison to choose the comparison of a query that runs in memory.

Entity Framework Core translates all the modes, to = and LIKE for most databases:

var queryBuilder = new ExpressionQueryBuilder<Ticket>();

// Generates ticket.Title != null && ticket.Title.StartsWith(value)
queryBuilder.AddHandler("title", ticket => ticket.Title, StringMatchMode.StartsWith);

var tickets = new[]
{
    new Ticket("Crash on startup", TicketPriority.Critical),
    new Ticket("Startup is slow", TicketPriority.Normal),
};

var query = queryBuilder.Build("title:Crash");
Assert.Equal(["Crash on startup"], query.Apply(tickets.AsQueryable()).Select(ticket => ticket.Title));

with the following types:

internal enum TicketPriority
{
    Low,
    Normal,
    High,
    Critical,
}

internal sealed record Ticket(string Title, TicketPriority Priority);

<> matches the rows that do not match, and the null ones. A string handler does not support <, <=, > and >=; register a custom expression for them.

Enums

An enum property supports the comparison operators and ranges, like a number. The values are compared by their numeric value, the order in which the members are declared unless you give them values:

var queryBuilder = new ExpressionQueryBuilder<Ticket>();
queryBuilder.AddHandler<TicketPriority>("priority", ticket => ticket.Priority);

var tickets = new[]
{
    new Ticket("Typo in the footer", TicketPriority.Low),
    new Ticket("Startup is slow", TicketPriority.Normal),
    new Ticket("Crash on startup", TicketPriority.Critical),
};

// Compares the numeric values: (int)ticket.Priority >= (int)value
var query = queryBuilder.Build("priority>=normal");
Assert.Equal(["Startup is slow", "Crash on startup"], query.Apply(tickets.AsQueryable()).Select(ticket => ticket.Title));

// Ranges work too
query = queryBuilder.Build("priority:low..normal");
Assert.Equal(["Typo in the footer", "Startup is slow"], query.Apply(tickets.AsQueryable()).Select(ticket => ticket.Title));

The ordered comparisons convert both sides to the underlying type of the enum, such as (int)x.Priority < (int)value, which Entity Framework Core translates for a column that stores the number. For a column that stores the name of the member (HasConversion<string>()), only equality is meaningful: : and <> compare the enum values without a conversion, so they keep working.

Custom expressions

For a key that does not map to one property, return the expression yourself. The function runs when the query is built, with the value as written in the query:

var queryBuilder = new ExpressionQueryBuilder<Customer>();
queryBuilder.AddHandler("is", value => value.Equals("adult", StringComparison.OrdinalIgnoreCase)
    ? customer => customer.Age >= 18
    : customer => false);

var customers = new[]
{
    new Customer("John Doe", "john@example.com", 30, new DateTime(2024, 1, 1)),
    new Customer("Johnny Smith", "johnny@example.com", 16, new DateTime(2024, 1, 1)),
};

var query = queryBuilder.Build("is:adult");
Assert.Equal(["John Doe"], query.Apply(customers.AsQueryable()).Select(customer => customer.FullName));

// <> negates the handler of ':'
query = queryBuilder.Build("is<>adult");
Assert.Equal(["Johnny Smith"], query.Apply(customers.AsQueryable()).Select(customer => customer.FullName));

Write expressions the provider can translate: Entity Framework Core cannot translate a call to a method of your own.

Free text and unknown keys

The predicate

Predicate returns the expression the query built, to combine it with your own filters or to compile it for objects in memory. It is null for an empty query, which matches everything:

var queryBuilder = new ExpressionQueryBuilder<Customer>();
queryBuilder.AddHandler<int>("age", customer => customer.Age);

// An empty query has no predicate: Apply returns the source unchanged
Assert.Null(queryBuilder.Build("").Predicate);

var predicate = queryBuilder.Build("age:18..65").Predicate;
Assert.NotNull(predicate);

var isWorkingAge = predicate.Compile();
Assert.True(isWorkingAge(new Customer("John Doe", "john@example.com", 30, new DateTime(2024, 1, 1))));

Build throws a QueryTooComplexException for a query too complex to evaluate, as QueryBuilder<T>.Build does; see Queries from users.