Supported LINQ Operators

Query operators

Operator	Supported
Where	yes
OrderBy / OrderByDescending / ThenBy / ThenByDescending	yes
Take / Skip	yes — limit m offset n
Select	yes — see projections
Distinct / DistinctBy	yes, with restrictions
GroupBy + Select	yes — see grouping
Join / GroupJoin + SelectMany	yes — see joins
Count / Any / First / Single / Last	yes, with predicate overloads
Sum / Min / Max / Average	yes — see aggregates
Include	yes — see including related documents
Search / PlainTextSearch / PhraseSearch / WebStyleSearch / PrefixSearch / NgramSearch	yes — see full-text search

Comparisons

.Where(x => x.Name == "Frodo")
.Where(x => x.Age > 30)
.Where(x => x.Age >= 30 && x.Age < 65)
.Where(x => x.Name != null)
.Where(x => x.Internal)
.Where(x => !x.Internal)

Collections

A member that is a collection — a List<T>, an array, anything IEnumerable<T>-shaped except strings, byte arrays and dictionaries — is stored as a JSON array and queried through a correlated sub-query over SQLite's json_each table-valued function:

// scalar elements — string, number, Guid, enum
.Where(x => x.Tags.Contains("urgent"))
// exists (select 1 from json_each(data, '$.tags') as each_1
//         where each_1.key is not null and each_1.value = @p0)

.Where(x => x.Tags.Any())                       // holds anything at all
.Where(x => x.Tags.Count() > 2)                 // also .Count property and array .Length
.Where(x => x.Stops.Count(s => s.Days < 5) == 2)

// child objects — member predicates on the element
.Where(x => x.Stops.Any(s => s.Port == "Oslo"))
.Where(x => x.Stops.Any(s => s.Days > 5 && s.Resupplied))
.Where(x => x.Stops.Any(s => s.Cargo.Contains("fuel")))   // nests, with a fresh alias per depth
.Where(x => x.Stops.All(s => s.Days < 5))

// membership the other way round — a value set, not a collection member
.Where(x => names.Contains(x.Name))          // in (…)
.Where(x => x.Name.IsOneOf("a", "b", "c"))   // the same, from the other direction
.Where(x => x.Name.In(allowed))

.Where(x => x.Tags.IsEmpty())

Element values go through the same conversion as a document member of the same type, so an enum element honours EnumStorage and the serializer's naming policy, a Guid element matches its lowercase canonical text, and a bool element matches the stored 1/0.

The degenerate shapes are handled honestly. An absent member, an empty array and a member stored as JSON null all hold no elements: Any() is false, Contains matches nothing, Count() compares as zero (where in-memory LINQ over a null collection would throw), and All(...) is vacuously true, matching Enumerable.All over an empty sequence. The key is not null guard in the generated SQL is what keeps a null member from matching — json_each over JSON null yields one phantom row, and that row is the only one whose key is NULL.

Refused rather than mis-translated, each with a BadLinqExpressionException naming the problem:

• A predicate referencing anything outside the element's own scope — the outer document (x.Stops.Any(s => s.Port == x.Name)), or an enclosing lambda's element. Compare against locals or constants instead.
• A member access on a scalar element (x.Tags.Any(t => t.Length > 3)) — the elements are plain values with no members to extract.
• A bare element comparison (x.Tags.Any(t => t == "urgent")) — use Contains.
• Contains against another document member, and Contains over child-object elements — use Any(c => …) with a predicate on the element's members.

TIP

Predicates inside Any/All/Count follow SQL null semantics, consistently with the rest of the provider: an element for which the predicate is NULL (say a null Port compared with !=) has not satisfied it, so it fails All and is not counted — where C# would call null != "Oslo" true.

TIP

IsEmpty() has to test null as well as length. json_extract yields SQL NULL for an absent key and json_array_length(null) is NULL rather than 0, so a bare = 0 would leave the row out instead of matching. A caller asking "is this empty" means "is there anything in it", and "the key is not there" is an honest yes.

TIP

array.Contains(x) binds to MemoryExtensions.Contains(ReadOnlySpan<T>, T) rather than Enumerable.Contains, so Fisher matches on the call's shape rather than its declaring type — and unwraps the span back to the array first, since a ReadOnlySpan<T> is a ref struct that cannot be returned as object.

Strings

See Searching on String Fields — the short version is that Fisher uses instr/substr rather than LIKE, because SQLite's LIKE is case-insensitive for ASCII while = is case-sensitive.

Timestamps

A DateTimeOffset member is compared through SQLite's date parser, not against the raw JSON:

strftime('%Y-%m-%dT%H:%M:%f', json_extract(data, '$.landedAt'))

That folds the trailing offset into UTC and renders fixed-width to the millisecond. Without it, the comparison is against the text System.Text.Json wrote, which is not order-preserving twice over: trailing fractional zeros are trimmed, and the original offset is kept — so 12:34:56-05:00 sorts before 12:34:56.789+00:00 while being five hours later.

Equality goes through the same normalisation as ordering. Two spellings of one instant must not be equal for >= and unequal for ==, which costs sub-millisecond discrimination on == — as it does on both siblings.

TIP

A null test stays on the raw JSON, because it asks whether the member is present, not whether it parses.

DateOnly and TimeOnly need none of this: a DateOnly is fixed-width with no offset and no fraction, and a TimeOnly's optional fraction is a strict suffix — so trimming shortens the string without changing which of two values compares smaller.

Decimals

A decimal member is stored as an ordinary JSON number and json_extract hands it back as REAL, so comparisons, IsOneOf, Contains, HAVING and arithmetic on one all work and all compare numerically:

.Where(x => x.Total > 100m)
.Where(x => x.Total == 250.75m)
.Where(x => x.Total.IsOneOf(50m, 250.75m))
.Where(x => x.Total + 60m > 200m)
.OrderByDescending(x => x.Total)

Nothing to configure. Fisher normalises the comparison value to double where the query binds it, because Microsoft.Data.Sqlite binds a raw decimal as TEXT and SQLite orders every numeric value below every TEXT one — see SQLite differences for what that means if you ever write the statement yourself.

WARNING

The comparison is floating point, so SumAsync and AverageAsync over a decimal member are accurate to double precision rather than to decimal precision. That is SQLite's arithmetic, not a Fisher choice; it is the same reason a duplicated decimal field is declared REAL.

Enums

Under the default EnumStorage.AsInteger everything works. Under AsString, range comparison and ordering are refused by name, because the stored value is the member's name and would sort alphabetically rather than by the enum's declared order. Equality still works. See JSON Serialization.

Strong-typed identifiers

A wrapper such as readonly record struct CustomerId(Guid Value) can be queried like the value it holds, provided it is registered:

opts.RegisterValueType<CustomerId>();

session.Query<Order>().Where(x => x.CustomerId == customerId);
session.Query<Order>().Where(x => x.CustomerId.Value == guid);
session.Query<Order>().Where(x => ids.Contains(x.CustomerId));
session.Query<Order>().Where(x => x.Lines.Contains(lineId));
session.Query<Order>().OrderBy(x => x.CustomerId).Select(x => x.CustomerId);

Registration makes Fisher store the wrapper in the JSON as its primitive. An unregistered wrapper is stored as an object, {"value":…}, which SQLite cannot compare against a value. The one exception is the document's identity: Where(x => x.Id == id) and Select(x => x.Id) work whether or not the id type is registered, because the id column always holds the inner value. See Strong-typed identifiers for what registering changes about rows already written.

Metadata operators

.Where(x => x.ModifiedSince(cutoff))
.Where(x => x.ModifiedBefore(cutoff))

These compare last_modified as text with no strftime wrapper, because the column already holds the fixed-width UTC form — the same asymmetry DeletedSince / DeletedBefore have.

WARNING

CreatedSince / CreatedBefore are deliberately absent. There is no created_at column to answer from unless you enable it, and answering from last_modified would be a different question asked with a straight face. Use Where(x => x.CreatedAt > cutoff) against a mapped metadata member instead.

Soft delete operators

.Where(x => x.MaybeDeleted())
.Where(x => x.IsDeleted())
.Where(x => x.DeletedSince(cutoff))
.Where(x => x.DeletedBefore(cutoff))

See Deleting Documents.

Tenancy operators

session.Query<Order>().AnyTenant()
session.Query<Order>().TenantIsOneOf("acme", "globex")

Both replace the tenant term rather than composing with it, and both are refused against a type that is not MultiTenanted() — there is no column to have an opinion about.

Raw SQL inside a Where

.Where(x => x.MatchesSql("json_extract(data, '$.weight') > ?", 5))
.Where(x => x.MatchesSql('^', "json_extract(data, '$.tag') = ^", tag))   // SQL with a literal ?

For the predicate the translator cannot express. Unlike AdvancedSql, which replaces the whole query, this is one term among the others — so the ordering, the paging, the projection and all three implicit filters still apply, and the fragment is bracketed for you so an or inside it cannot swallow them.

Columns are the physical ones. session.ToSql(...) over an ordinary query shows the spellings.

DANGER

The SQL is yours and is not inspected. Fisher parameterizes everything it composes itself; here the text is the caller's by contract, so it is the caller's job not to concatenate untrusted input into it.

Pass values as parameters. They are bound, never interpolated, and go through the same conversions raw SQL applies — a Guid to lowercase canonical text, a timestamp to the fixed-width UTC form, a decimal to REAL. Interpolating them instead is not only the injection risk: all three of those bind to something Fisher never wrote and match nothing, silently, because no rows is an ordinary answer.

A placeholder/value count mismatch is refused by name rather than becoming an index error in one direction and silence in the other.

A total alongside a query

var page = await session.Query<Order>()
    .Where(x => x.Open)
    .Stats(out var stats)
    .OrderBy(x => x.Placed).Skip(20).Take(10)
    .ToListAsync();

stats.TotalResults;   // how many matched, ignoring Take/Skip

The total is a second statement rather than count(*) over (), because a window function returns no row at all when the page is past the end — which is when a caller most needs the real total.

Honoured by the terminals that return rows. A scalar terminal — CountAsync, AnyAsync, the aggregates — refuses it by name, because it already is the number and a TotalResults left at zero would be a wrong answer the caller cannot see.

Streaming

await foreach (var order in session.Query<Order>().Where(x => x.Open).ToAsyncEnumerable())
{
    // …
}

For a result set large enough that holding it is the problem. Everything ToListAsync does still applies — the implicit filters, the identity map under a tracking session, a hierarchy resolving to its real sub-classes — and a Select projection streams too. A join is refused by name: its rows are stitched from both sides by the join plan, and ToListAsync is the operator for those.

TIP

This is cheaper here than the raw-SQL IAdvancedSql.StreamAsync, which has to run outside the resilience pipeline and says so. The LINQ path never ran inside it, so this operator forfeits nothing ToListAsync has.

Explaining a query

var plan = await session.Query<Catch>().Where(x => x.Species == "Pike").ExplainAsync();

plan.UsesIndex;   // did the planner reach the index you declared?
plan.Steps;       // SQLite's own rows, in order
plan.Sql;         // the exact statement that was explained

SQLite's EXPLAIN QUERY PLAN, over the exact statement the query would run. It answers the question a declared index otherwise leaves unanswerable: is it being used? The planner reaches an expression index only when the query's expression matches the index's, so an index built from a hand-written json_extract is created without error, never used, and reports nothing anywhere.

It plans; it does not execute. Nothing is read and nothing is written.

TIP

There is no Marten-portable shape here and none is invented. PostgreSQL's EXPLAIN (FORMAT JSON) returns a costed, nested tree with an optional execution pass; SQLite's returns four columns of prose with no costs and no ANALYZE. Steps is what SQLite said, in the order it said it, and UsesIndex is an honest reading of that prose rather than a structured field.

Waiting for projections

.QueryForNonStaleData(TimeSpan.FromSeconds(5))

It is a wait rather than SQL, and it survives being wrapped for a count, a page, an aggregate or a reversal — the timeout is read by walking the subquery chain, so each wrap site does not have to remember it.

What is refused

Each of these throws a BadLinqExpressionException naming the operator, so you find out at the call rather than through a slow query:

• Client-side evaluation of anything untranslatable
• GroupBy with no Select, and the element/result-selector overloads
• Distinct() over whole documents; DistinctBy after a Select
• Ordering by a member of a shaped projection
• A second Select
• After a join: keyset paging, JSON reads, and Select / GroupBy / Distinct / DistinctBy
• Ordering or range-comparing a string-stored enum
• Inside a collection predicate: outer-scope references, member access on scalar elements, and bare element comparisons — see Collections
• A soft-delete or tenancy operator against a type that has no such column
• Include() combined with Select, GroupBy, a join, or any terminal that returns no documents — see including related documents
• A full-text operator against a type with no declared index, or against one whose tokenizer cannot serve it — see full-text search

Marten operators that are absent

None, as of the full-text operators landing. Every LINQ operator this page's Marten counterpart lists now exists here, so a ported query compiles — though several mean something narrower, and the migration guide is where those are named.

Compiled queries (ICompiledQuery<T>) remain absent, on a measurement rather than by omission — building the SQL is 4–11% of an ordinary Fisher query, so the absolute saving is around 2 µs.