Pacem.Data.EntityFrameworkCore 1.1.1-cauchy

This is a prerelease version of Pacem.Data.EntityFrameworkCore.
There is a newer version of this package available.
See the version list below for details.
dotnet add package Pacem.Data.EntityFrameworkCore --version 1.1.1-cauchy
                    
NuGet\Install-Package Pacem.Data.EntityFrameworkCore -Version 1.1.1-cauchy
                    
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Pacem.Data.EntityFrameworkCore" Version="1.1.1-cauchy" />
                    
For projects that support PackageReference, copy this XML node into the project file to reference the package.
<PackageVersion Include="Pacem.Data.EntityFrameworkCore" Version="1.1.1-cauchy" />
                    
Directory.Packages.props
<PackageReference Include="Pacem.Data.EntityFrameworkCore" />
                    
Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.
paket add Pacem.Data.EntityFrameworkCore --version 1.1.1-cauchy
                    
#r "nuget: Pacem.Data.EntityFrameworkCore, 1.1.1-cauchy"
                    
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
#:package Pacem.Data.EntityFrameworkCore@1.1.1-cauchy
                    
#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package.
#addin nuget:?package=Pacem.Data.EntityFrameworkCore&version=1.1.1-cauchy&prerelease
                    
Install as a Cake Addin
#tool nuget:?package=Pacem.Data.EntityFrameworkCore&version=1.1.1-cauchy&prerelease
                    
Install as a Cake Tool

Pacem.Data.EntityFrameworkCore

Entity Framework Core utilities for the Pacem ecosystem:

  • Free-text search with SearchClause<T>: one search box over many fields, typed and parameterized, run by the database as a single WHERE, optionally ranked by relevance.
  • Auditing and soft deletion (BaseEntity, IAuditable, ISoftDeletable): audited saves stamp who and when, removals become soft deletions, and a query filter hides the deleted rows. See Auditing and soft deletion.
  • QueryFilter / Where(IEnumerable<IQueryFilter>), obsolete: kept for existing callers, see Migrating from QueryFilter.

Install

dotnet add package Pacem.Data.EntityFrameworkCore

Targets net10.0, on top of Microsoft.EntityFrameworkCore 10. The search translates on any relational provider that supports string.StartsWith/string.Contains, EXISTS and CASE (SQL Server, Sqlite, PostgreSQL...).

Quick start

using Pacem.Data.EntityFrameworkCore;

public sealed class OrderStore(OrdersContext db)
{
    // describe the search once: a clause is immutable, so it is safe to share
    private static readonly SearchClause<OrderEntity> OrderSearch = SearchClause.For<OrderEntity>()
        .Text(o => o.Key, TextMatch.Prefix)
        .Text(o => o.DisplayName)
        .Text(o => o.FullAddress);

    public Task<OrderEntity[]> FindAsync(string q, CancellationToken cancellationToken)
    {
        return db.Orders
            .AsNoTracking()
            .Search(OrderSearch, q)          // blank q: no filter at all
            .OrderBy(o => o.Key)
            .ToArrayAsync(cancellationToken);
    }
}

"rossi milano" finds the orders where both words appear, each in any of the fields: Mario Rossi living in Milano, not every Rossi and everyone in Milano.

How a query is matched

  1. Words. The query is split on whitespace. Symbols act as separators and get dropped; letters, digits, dashes and underscores are kept. "Rossi, Mario (ORD-12)" gives Rossi, Mario, ORD-12. A blank query, or one made only of symbols, filters nothing: Search returns the source query unchanged.
  2. Word × field. Each word is matched against every Text and Any field. With the default SearchMode.AllWords every word must match at least one field; with SearchMode.AnyWord one matching word is enough.
  3. Whole query. Exact fields are compared with the whole trimmed query, converted to the field type. An exact match is OR'ed with the word matches.
( word1 ~ field1 OR word1 ~ field2 OR ... )
AND ( word2 ~ field1 OR word2 ~ field2 OR ... )      -- OR between words with SearchMode.AnyWord
OR exactField1 = query OR exactField2 = query ...

Field kinds

Method Matches SQL (SQL Server)
.Text(o => o.Name) each word, TextMatch.Auto Name LIKE @w + '%' for words of up to 2 characters, Name LIKE '%' + @w + '%' otherwise
.Text(o => o.Key, TextMatch.Prefix) each word, as a prefix Key LIKE @w + '%' (can use an index)
.Text(o => o.Notes, TextMatch.Contains) each word, anywhere Notes LIKE '%' + @w + '%'
.Exact(o => o.Number) the whole query, converted Number = @q (skipped when q isn't a number)
.Any(o => o.Lines, l => l.Caption) each word, in any item EXISTS (SELECT 1 FROM Lines WHERE ... LIKE ...)

Auto matches short words as prefixes, so "mi" doesn't drag in every "Armini" and "Romilda". Words of three characters or more match anywhere.

Fields can go through reference navigations (o => o.Customer.Firm.Name). EF Core adds the joins, and a missing navigation simply doesn't match in SQL. Over in-memory collections only the last member is null-guarded, so a null navigation throws there.

Text
SearchClause<CustomerEntity> clause = SearchClause.For<CustomerEntity>()
    .Text(c => c.Code, TextMatch.Prefix)   // "C-12" finds "C-1234"
    .Text(c => c.Surname)
    .Text(c => c.Name)
    .Text(c => c.Address.City);            // through a reference navigation
Exact

Exact is for identifiers people paste or type in full: emails, usernames, numbers, codes, Guids. The query is converted to the field type with the invariant culture: numbers, enums (by name, case-insensitive), Guid, DateTime/DateTimeOffset and anything with a TypeConverter from string. When the conversion fails the field is skipped: no error, no match.

SearchClause<UserEntity> clause = SearchClause.For<UserEntity>()
    .Text(u => u.Surname)
    .Text(u => u.Name)
    .Exact(u => u.Username)
    .Exact(u => u.Email)
    .Exact(u => u.Uid);       // a pasted Guid finds the user; any other text skips this field

"mario@rossi.it" finds the user by email, even though its words (mario, rossi, it) wouldn't all match the text fields: the exact match is OR'ed in.

Any (collections)
SearchClause<InvoiceEntity> clause = SearchClause.For<InvoiceEntity>()
    .Text(i => i.Number, TextMatch.Prefix)
    .Text(i => i.Supplier.Firm.Name)
    .Text(i => i.Customer.Firm.Name)
    .Any(i => i.Lines, l => l.Caption);

Each word gets its own EXISTS, so different words may match different items: "boiler thermostat" finds an invoice with a Boiler service line and a Thermostat line.

What reaches the database

Every word and value is a query parameter, never an inlined literal, so SQL Server reuses one plan per search shape. LIKE wildcards in the words (e.g. _; % is a symbol and gets dropped anyway) are escaped by EF Core. A word shared by several fields is sent once per match kind. This is the SQL Server text for "rossi 1_0" over a prefix Key, a DisplayName and Any(Lines, Caption):

DECLARE @Value_startswith nvarchar(4000) = N'rossi%';
DECLARE @Value_contains nvarchar(4000) = N'%rossi%';
DECLARE @Value3_startswith nvarchar(4000) = N'1\_0%';
DECLARE @Value3_contains nvarchar(4000) = N'%1\_0%';

SELECT ...
FROM [Orders] AS [o]
WHERE (([o].[Key] IS NOT NULL AND [o].[Key] LIKE @Value_startswith ESCAPE N'\')
    OR ([o].[DisplayName] IS NOT NULL AND [o].[DisplayName] LIKE @Value_contains ESCAPE N'\')
    OR EXISTS (
        SELECT 1
        FROM [OrderLines] AS [o0]
        WHERE [o].[Id] = [o0].[OrderId] AND [o0].[Caption] IS NOT NULL AND [o0].[Caption] LIKE @Value_contains ESCAPE N'\'))
  AND (([o].[Key] IS NOT NULL AND [o].[Key] LIKE @Value3_startswith ESCAPE N'\')
    OR ...)

One WHERE, no UNION. Entities with columns SQL Server can't compare (xml, geography, text...) search fine, whereas a UNION of their rows fails.

Ranking

SearchRanked filters like Search and orders the results by a relevance score computed by the database. Each (word, field) match adds the field's weight (default 1), and so does each exact match. Chain ThenBy to break ties.

private static readonly SearchClause<OrderEntity> OrderSearch = SearchClause.For<OrderEntity>()
    .Text(o => o.Key, TextMatch.Prefix, weight: 3)
    .Text(o => o.DisplayName, weight: 2)
    .Text(o => o.FullAddress)
    .Exact(o => o.Number, weight: 10);

OrderEntity[] best = await db.Orders
    .SearchRanked(OrderSearch, q)
    .ThenByDescending(o => o.Created)
    .Take(20)
    .ToArrayAsync(cancellationToken);

For "milano", Milano Center, Piazza Duomo, Milano scores 3 (name 2 + address 1) and comes before Via Roma 1, Milano, which scores 1.

-- abridged
ORDER BY CASE WHEN [o].[Key] IS NOT NULL AND [o].[Key] LIKE @p_startswith ESCAPE N'\' THEN 3 ELSE 0 END
       + CASE WHEN [o].[DisplayName] IS NOT NULL AND [o].[DisplayName] LIKE @p_contains ESCAPE N'\' THEN 2 ELSE 0 END
       + ...
       DESC, [o].[Created] DESC
  • weight: 0 keeps a field in the filter but out of the score.
  • With a blank query every row scores zero, and the ThenBy alone decides the order.
  • The score repeats the match expressions. On large tables, rank the page you show (Take), not the whole set.

Recipes

IQueryable<OrderEntity> query = db.Orders
    .AsNoTracking()
    .Where(o => o.TenantKey == tenantKey)
    .Search(OrderSearch, q);

int total = await query.CountAsync(cancellationToken);
OrderEntity[] page = await query
    .OrderByDescending(o => o.Created)
    .Skip(skip)
    .Take(take)
    .ToArrayAsync(cancellationToken);

Search is an ordinary Where: tenant, status and permission filters compose with it in any order.

An endpoint with an optional q
app.MapGet("/orders", async (string? q, OrderStatus? status, OrdersContext db, CancellationToken cancellationToken) =>
{
    IQueryable<OrderEntity> query = db.Orders.AsNoTracking();
    if (status is not null)
    {
        query = query.Where(o => o.Status == status);
    }

    return await query
        .SearchRanked(OrderSearch, q)
        .ThenBy(o => o.Key)
        .Take(50)
        .ToArrayAsync(cancellationToken);
});
Replacing the union-per-field pattern

Before: one query per field, merged with UNION. That means N scans, any word matching, no ranking, and a failure on non-comparable columns.

QueryFilter byName = new() { Name = nameof(OrderEntity.DisplayName), Value = q, Operator = QueryFilterOperator.Fit };
QueryFilter byAddress = new() { Name = nameof(OrderEntity.FullAddress), Value = q, Operator = QueryFilterOperator.Fit };

var ids = query.Where(o => o.Key.StartsWith(q)).Select(o => o.Id)
    .Union(query.Where(new[] { byName }).Select(o => o.Id))
    .Union(query.Where(new[] { byAddress }).Select(o => o.Id));
query = query.Where(o => ids.Contains(o.Id));

After:

private static readonly SearchClause<OrderEntity> OrderSearch = SearchClause.For<OrderEntity>()
    .Text(o => o.Key, TextMatch.Prefix)
    .Text(o => o.DisplayName)
    .Text(o => o.FullAddress);

query = query.Search(OrderSearch, q);

o.Key.StartsWith(q) compared the whole query, while Text(..., TextMatch.Prefix) compares each word. For the old behavior on codes typed in full, use .Exact(o => o.Key) too, or instead.

Joined tables

Before: join, filter, and union per child table.

var firms = db.Firms.Where([firmFilter]).Select(f => new { f.Id });
var bySupplier = query.Join(firms, i => i.Supplier!.FirmId, f => f.Id, (i, f) => i);
var byCustomer = query.Join(firms, i => i.Customer!.FirmId, f => f.Id, (i, f) => i);
var byLine = query.Join(db.InvoiceLines.Where([lineFilter]).Select(l => new { l.InvoiceId }), i => i.Id, l => l.InvoiceId, (i, l) => i);
query = bySupplier.Union(byCustomer).Union(byLine);

After: navigations and Any.

private static readonly SearchClause<InvoiceEntity> InvoiceSearch = SearchClause.For<InvoiceEntity>()
    .Text(i => i.Supplier!.Firm.Name)
    .Text(i => i.Customer!.Firm.Name)
    .Any(i => i.Lines, l => l.Caption);

query = query.Search(InvoiceSearch, q);
Building on a shared clause

Clauses are immutable, so extending one never changes the original:

private static readonly SearchClause<UserEntity> NameSearch = SearchClause.For<UserEntity>()
    .Text(u => u.Surname, weight: 2)
    .Text(u => u.Name);

// back-office: names, plus identifiers
private static readonly SearchClause<UserEntity> AdminSearch = NameSearch
    .Exact(u => u.Email, weight: 10)
    .Exact(u => u.Username, weight: 10);

Things to know

  • Case and accents follow the column collation. SQL Server's default *_CI_AS collations are case-insensitive and accent-sensitive; pick an *_AI collation on the column for accent-insensitive search. On Sqlite, EF Core's Contains is case-sensitive.
  • Indexes. TextMatch.Prefix (LIKE 'word%') can seek an index; Contains (LIKE '%word%') scans. Prefer Prefix for codes and keys, and keep Contains for names and descriptions.
  • Null columns never match: every text match is guarded by IS NOT NULL. The same guard keeps the predicate safe for in-memory IQueryables, e.g. unit tests over List<T>.AsQueryable().
  • Very large tables. LIKE '%word%' across many columns gets costly. A full-text index (CONTAINS/FREETEXT) is the next step there; it isn't part of this package yet.

Auditing and soft deletion

Derive from BaseEntity, or implement IAuditable and/or ISoftDeletable, then save with the executor's name:

public class OrderEntity : BaseEntity
{
    public int Id { get; set; }

    public string TenantKey { get; set; }

    public string DisplayName { get; set; }
}

public class OrdersContext(DbContextOptions<OrdersContext> options, ITenantAccessor tenant) : DbContext(options)
{
    public DbSet<OrderEntity> Orders => Set<OrderEntity>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<OrderEntity>().HasQueryFilter(o => o.TenantKey == tenant.Key);

        // last, after the entities' own filters
        modelBuilder.AddSoftDeletionFilter();
    }
}
db.Orders.Add(newOrder);                 // CreatedOn/By = LastUpdatedOn/By = now, userName
existingOrder.DisplayName = "Mario Rossi"; // LastUpdatedOn/By = now, userName (CreatedOn/By untouched)
db.Orders.Remove(obsoleteOrder);         // UPDATE: Deleted = 1, DeletedOn = now, DeletedBy = userName

await db.SaveChangesAsync(userName, cancellationToken);
  • Soft deletion turns the DELETE of an ISoftDeletable entity into an UPDATE of its deletion columns only. Removing a stub by key (db.Orders.Remove(new OrderEntity { Id = id })) is safe: the other columns aren't touched.

  • Updating a detached entity (db.Orders.Update(entity)) never rewrites CreatedOn/CreatedBy.

  • AddSoftDeletionFilter() hides the deleted rows, whatever filters the entities already declare:

    The entity already has... Resulting filter
    no filter IsDeleted != true
    an anonymous filter (HasQueryFilter(o => ...)) existing && IsDeleted != true
    named filters (HasQueryFilter("Tenant", o => ...)) one more named filter, ModelBuilderExtensions.SoftDeletionFilterKey ("SoftDeletion")
    // deleted rows too, other filters kept (named filters only)
    db.Orders.IgnoreQueryFilters([ModelBuilderExtensions.SoftDeletionFilterKey]);
    
    // everything, every filter off
    db.Orders.IgnoreQueryFilters();
    

    Hierarchies are filtered through their root entity type, the only one EF Core accepts a filter on.

  • The audited saves accept the changes on success, as EF Core does: pass acceptAllChangesOnSuccess: false to keep the entries' state (then call db.ChangeTracker.AcceptAllChanges() yourself).

Structured filters

For filters that aren't free text (status, ranges, flags, ownership), write typed LINQ. It is checked by the compiler, parameterized, and refactoring-safe:

IQueryable<OrderEntity> query = db.Orders.AsNoTracking();

if (status is not null)
{
    query = query.Where(o => o.Status == status);
}

if (from is not null)
{
    query = query.Where(o => o.Due >= from);
}

if (onlyOpen)
{
    query = query.Where(o => o.ClosedOn == null);
}

query = query.Search(OrderSearch, q);

Migrating from QueryFilter

QueryableExtensions.Where(IEnumerable<IQueryFilter>) is [Obsolete] and will be removed. It no longer depends on Dynamic LINQ (see the CHANGELOG), so existing callers keep working in the meantime.

QueryFilter Replace with
Fit + string on a text property SearchClause.For<T>().Text(x => x.Property) + .Search(clause, q, SearchMode.AnyWord) for the exact old semantics, or the default AllWords for the better one
several Fit filters merged with Union one SearchClause with one .Text(...) per property
Equal on the search text (email, username) .Exact(x => x.Email) in the same clause
Fit + date (±1 day) query.Where(x => x.Due > day.AddDays(-1) && x.Due < day.AddDays(1))
Equal, NotEqual, GreaterThan... query.Where(x => x.Property == value) and friends
Null / NotNull query.Where(x => x.Property == null) / != null

The obsolete call and its replacement, side by side:

// before
QueryFilter bySurname = new() { Name = nameof(UserEntity.Surname), Value = q, Operator = QueryFilterOperator.Fit };
QueryFilter byEmail = new() { Name = nameof(UserEntity.Email), Value = q, Operator = QueryFilterOperator.Equal };
IQueryable<UserEntity> users = db.Users.Where([bySurname]).Union(db.Users.Where([byEmail]));

// after
private static readonly SearchClause<UserEntity> UserSearch = SearchClause.For<UserEntity>()
    .Text(u => u.Surname)
    .Exact(u => u.Email);

IQueryable<UserEntity> users = db.Users.Search(UserSearch, q);

QueryFilter reference (obsolete)

For the code that still uses it. Filters combine with AND; each one is turned into a typed, parameterized predicate.

  • Name is a public instance property path, dotted for navigations (Customer.Name), matched case-insensitively. Methods and fields aren't allowed. An unknown segment throws ArgumentException.
  • Value is converted to the property type: numbers between numeric types, enums by name or number, Guid and dates from strings, DateTime ↔ DateTimeOffset. A value that can't be converted throws ArgumentException.
Operator On Predicate
Fit string value, text property any word matches (words of up to 2 characters as prefixes, longer ones anywhere); no words → no filter
Fit date value, date property x > value - 1 day && x < value + 1 day
Fit anything else x == value; null value → no filter
Equal, NotEqual any x == value, x != value (null allowed)
GreaterThan, GreaterThanEqual, LessThan, LessThanEqual numbers, dates, enums, strings relational comparison (strings through string.Compare, enums through their numeric value)
Null, NotNull nullable property x == null, x != null
Null, NotNull non-nullable property matches nothing / filters nothing
Product Compatible and additional computed target framework versions.
.NET net11.0 is compatible. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (1)

Showing the top 1 NuGet packages that depend on Pacem.Data.EntityFrameworkCore:

Package Downloads
Pacem.Id.EntityFrameworkCore

Provider-neutral EntityFrameworkCore layer (entities, stores, DbContext) shared by the SqlServer- and Sqlite-backed Pacem ID Provider implementations.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last Updated
1.1.1 63 11/18/2026
1.1.1-davinci 87 11/15/2026
1.1.1-cauchy 88 11/15/2026
1.1.1-bessel 88 11/13/2026
1.1.0 133 11/11/2026
1.1.0-archimedes 84 11/11/2026
1.0.2 127 11/9/2026
1.0.1 83 11/9/2026
1.0.0 123 11/9/2026
1.0.0-tartaglia 124 11/5/2026
1.0.0-stokes 84 11/4/2026
1.0.0-saint-venant 92 11/4/2026
1.0.0-ricci 146 10/28/2026
1.0.0-peano 156 10/27/2026
1.0.0-maiorana 95 10/27/2026
0.10.19 101 10/27/2026
0.10.19-galilei 92 10/27/2026
0.10.19-fermi 98 10/27/2026
0.10.19-degiorgi 94 10/26/2026