More on our continuous improvement series with some performance optimizations by making the Marten LINQ provider "know" how to generate more efficient SQL for some types of LINQ queries

There's a quiet performance cliff hiding in every LINQ provider, and Marten is no exception. When you write a Where or Select clause, Marten tries to translate that expression tree into PostgreSQL SQL and JSONPath so the database does the filtering for you. When it can translate the expression, PostgreSQL evaluates it, uses your indexes, and ships back only the documents you actually asked for. When it can't, Marten falls back to client-side evaluation -- which means pulling more rows across the wire and doing the filtering in your .NET process. Same query, wildly different cost.

So the single most impactful thing we can do for LINQ performance usually isn't making the SQL faster. It's making more of your query translate in the first place. A recent batch of quick-win work did exactly that: it widened the set of expressions Marten can push into Postgres, and it made a couple of existing translations cheaper while we were in there. Let me walk through the improvements, because most of them are the kind of thing you'd never notice until you profiled a hot endpoint and wondered why so much data was crossing the wire.

CompareTo() finally works across the board

IComparable.CompareTo() shows up constantly in real code -- sometimes because you wrote it, and sometimes because a library or generated comparison put it there. Historically Marten only handled a subset of these, so an expression like this would quietly fall back to client-side filtering:

query.Where(x => x.Number.CompareTo(10) > 0);

That's now generalized to translate for all of the primitive types Marten actually stores: int, long, double, decimal, float, DateTime, DateTimeOffset, string, and Guid. marten#4920 shipped the generalization along with the Guid case, and test coverage confirms the rest. It also handles a constant receiver, so this translates too:

query.Where(x => 10.CompareTo(x.Number) > 0);

The nice part is that x => x.Number.CompareTo(10) > 0 is just a more roundabout way of writing x => x.Number > 10, and now PostgreSQL gets to treat it that way instead of you paying to materialize documents and compare them in memory.

Regex.IsMatch() translates now too

Pattern matching against document fields is another one people reach for and then get surprised when it drags. Regex.IsMatch() in a Where clause now translates instead of falling back:

query.Where(x => Regex.IsMatch(x.Name, "^A"));

That's a straightforward win: PostgreSQL is perfectly happy doing regular expression matching, so there's no reason to drag every candidate document back to the client just to run Regex over it there.

IsOneOf() on string collections got cheaper

This one is less about widening translation and more about making an existing translation smarter. When you use IsOneOf(...) against a string collection:

query.Where(x => x.Tags.IsOneOf("red", "green", "blue"));

Marten used to rebuild a typed PostgreSQL array per row and lean on the && array-overlap operator. It now renders as:

CAST(... as jsonb) ?| :values

The ?| operator means "does any of these keys exist," and it's served by the default GIN jsonb_ops operator class -- so it's cheaper at query time and plays nicely with the indexing you already have on jsonb documents. Non-string element types keep the array-overlap translation, since that's still the right tool for those; this optimization is specifically for the string case.

Nested "is empty" checks collapse into one JSONPath filter

Here's my favorite of the batch, because it takes something that used to be genuinely expensive and reduces it to a single database-side check. Consider a query looking for any order whose lines have empty sub-collections:

query.Where(x => x.Lines.Any(l => l.Subs.IsEmpty()));

CollectionIsEmpty is now collection-aware, and this whole thing reduces to one JSONPath filter:

d.data @? '$.Lines[*] ? (@.Subs == null || @.Subs.size() == 0)'

instead of pulling documents back and doing the nested Any/IsEmpty work client-side. That's a nested collection predicate -- exactly the kind of expression that used to be a reliable fallback -- now running entirely inside PostgreSQL.

Now the honest caveat, because I'd rather you trust the tool than be surprised by it: the "not empty" nested case deliberately does not flatten the same way. "Some parent with a non-empty nested collection" can't be reduced to a single total-length check without quietly changing what the query means, so we left it alone. Correctness over cleverness -- every time.

A per-statement win for multi-tenant RLS

Not every improvement is about LINQ translation. If you run Marten's conjoined tenancy backed by PostgreSQL Row-Level-Security, there's a nice one for you here too. The generated RLS policy now wraps current_setting(...) so PostgreSQL evaluates it once per statement instead of once per row. On a large tenant table, that per-row evaluation adds up fast, and moving it to once-per-statement is a real speedup with no change to behavior or the query you write. You get it for free the next time your schema is generated.

What still falls back -- because I promised transparency

I'm not going to pretend everything translates now. Dictionary.Values.Any(predicate) still uses its existing behavior and will fall back to client-side evaluation. It's on the list as a follow-up, and I'd rather tell you plainly than have you find out with a profiler. The whole point of caring about translation-vs-fallback is knowing where the line is, so here's one edge of it.

Tie it together

If this is your kind of problem -- shaving data movement and pushing work down into Postgres -- pair this with our earlier post The Fastest Possible HTTP Queries with Marten. That one is about getting query results onto the wire with as little ceremony as possible; this one is about making sure the database, not your app server, decided what belonged on the wire in the first place. Together they cover both ends of the same hot path.

If you want the full picture of what does and doesn't translate, the Marten LINQ documentation is the canonical reference, and it's kept honest about the fallbacks.

And if your team is leaning on Marten for the kind of workloads where these differences actually show up in production, we do this for a living -- JasperFx support plans get you direct access to the people who wrote this code, and you're always welcome to come talk shop with us on Discord.