Pacem.Data.EntityFrameworkCore
1.1.1-bessel
See the version list below for details.
dotnet add package Pacem.Data.EntityFrameworkCore --version 1.1.1-bessel
NuGet\Install-Package Pacem.Data.EntityFrameworkCore -Version 1.1.1-bessel
<PackageReference Include="Pacem.Data.EntityFrameworkCore" Version="1.1.1-bessel" />
<PackageVersion Include="Pacem.Data.EntityFrameworkCore" Version="1.1.1-bessel" />
<PackageReference Include="Pacem.Data.EntityFrameworkCore" />
paket add Pacem.Data.EntityFrameworkCore --version 1.1.1-bessel
#r "nuget: Pacem.Data.EntityFrameworkCore, 1.1.1-bessel"
#:package Pacem.Data.EntityFrameworkCore@1.1.1-bessel
#addin nuget:?package=Pacem.Data.EntityFrameworkCore&version=1.1.1-bessel&prerelease
#tool nuget:?package=Pacem.Data.EntityFrameworkCore&version=1.1.1-bessel&prerelease
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 singleWHERE, 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...).
Free-text search
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
- 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)"givesRossi,Mario,ORD-12. A blank query, or one made only of symbols, filters nothing:Searchreturns the source query unchanged. - Word × field. Each word is matched against every
TextandAnyfield. With the defaultSearchMode.AllWordsevery word must match at least one field; withSearchMode.AnyWordone matching word is enough. - Whole query.
Exactfields 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: 0keeps a field in the filter but out of the score.- With a blank query every row scores zero, and the
ThenByalone decides the order. - The score repeats the match expressions. On large tables, rank the page you show (
Take), not the whole set.
Recipes
Paging a search
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_AScollations are case-insensitive and accent-sensitive; pick an*_AIcollation on the column for accent-insensitive search. On Sqlite, EF Core'sContainsis case-sensitive. - Indexes.
TextMatch.Prefix(LIKE 'word%') can seek an index;Contains(LIKE '%word%') scans. PreferPrefixfor codes and keys, and keepContainsfor 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-memoryIQueryables, e.g. unit tests overList<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
DELETEof anISoftDeletableentity into anUPDATEof 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 rewritesCreatedOn/CreatedBy.AddSoftDeletionFilter()hides the deleted rows, whatever filters the entities already declare:The entity already has... Resulting filter no filter IsDeleted != truean anonymous filter ( HasQueryFilter(o => ...))existing && IsDeleted != truenamed 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: falseto keep the entries' state (then calldb.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.
Nameis a public instance property path, dotted for navigations (Customer.Name), matched case-insensitively. Methods and fields aren't allowed. An unknown segment throwsArgumentException.Valueis converted to the property type: numbers between numeric types, enums by name or number,Guidand dates from strings,DateTime↔DateTimeOffset. A value that can't be converted throwsArgumentException.
| 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 | Versions Compatible and additional computed target framework versions. |
|---|---|
| .NET | net11.0 is compatible. |
-
net11.0
- Microsoft.EntityFrameworkCore (>= 11.0.0)
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 |