DEV Community: Agave Information Soultions, LLC

Shipping an MCP Server: Desktop App vs Hosted Web App

Agave Information Soultions, LLC — Sat, 26 Sep 2026 02:29:00 +0000

We ship two Model Context Protocol servers. One runs inside a desktop application, on the machine where the data already lives. The other runs as a hosted service behind an account, in front of a multi-tenant database. They speak the same protocol and share almost none of the same decisions.

Most writing about MCP stops at the hello-world tutorial: register a tool, return a string, done. The interesting part starts after that, when you have to decide where the server runs, how it reaches users, how you update it, what it is allowed to touch, and what happens when the assistant calling it is confidently wrong. Those answers are not a matter of taste. They fall out of the deployment model almost mechanically.

Here is the comparison, decision by decision.

The two products

Hiberden is desktop software for LTO tape archiving. It keeps a local catalog of everything it has archived: every project, every copy on tape, disk, NAS and cloud, every file path, every checksum, and every overnight job. The MCP server ships inside the application.

Easy2257 is a hosted service that keeps 18 U.S.C. 2257 records for producers in a federally regulated vertical: who appeared in a scene, that identity was verified, which paperwork is signed, and a certificate proving the record existed at a point in time. Its MCP server runs as part of the service. Nothing is installed.

One catalog sits on your desk. The other sits in a database shared by every customer. Nearly everything below follows from that one difference.

1. Where the server runs decides everything else

The desktop server is a local process. The transport is stdio: the assistant launches the server as a subprocess on the same machine as the data. There is no network hop, no listening port, and no inbound surface to attack. The catalog it reads is an ordinary local file the user already owns.

The hosted server is a network service. The assistant connects across the internet to something holding many companies' records at once. Every single request has to answer "who is asking, and what are they entitled to see" before it can answer anything else.

The consequence is not subtle. On the desktop, authentication is mostly solved before you write a line of server code: the person is sitting at the machine, and the operating system already decided they can open that file. On a hosted server, authentication is the product surface. It is the first thing you build and the thing you keep rebuilding.

2. Distribution: an installer problem or a documentation problem

A desktop server has to physically arrive on the machine. A well-built one ships inside the installer, and it is also published to the official MCP registry as a versioned, checksummed bundle running over stdio, plus third-party directories like Glama, which is increasingly how assistants and their users discover servers at all.

A hosted server has nothing to install. The endpoint exists the moment the account exists. Distribution stops being an engineering problem and becomes a documentation problem: the work is making the connection instructions short enough that somebody finishes them.

Each model quietly inherits a different environment. Ship on the desktop and you inherit the user's machine: operating system versions, assistant clients that each store their config somewhere different, antivirus software with opinions about unsigned subprocesses. Ship hosted and you inherit the network: timeouts, retries, and clients that will absolutely call the same tool four times in a row.

3. Versioning: pinned forever, or changed for everyone at once

This is where the two models diverge hardest, and it is the one people underestimate.

On the desktop, the server version is pinned to the application version. A customer running last spring's build is running last spring's tools, and they may run them for years. You cannot fix a tool for everybody at once, and you cannot remove one either, because somebody who never updates will keep calling it forever. Deprecation is a release-notes problem measured in years, not sprints.

On the hosted service there is exactly one version and everyone is on it. A fix reaches every customer the moment it deploys. The flip side is that a breaking change breaks every customer simultaneously, and the software calling your tools is not your software. It is somebody's assistant, wired up months ago, that you cannot test against.

What that works out to in practice: on the desktop, make changes additive and never alter a tool signature that already shipped. On hosted, you can iterate faster, but treat the tool list as a published API contract, because that is what it is.

4. Credentials: keep them out of the conversation

The desktop application already holds credentials it needs for its own work, such as the keys for the customer's own cloud storage account. Those live in the operating system keyring. The rule we settled on is simple and worth stating plainly: credentials never pass through the assistant. The assistant asks the catalog questions and the catalog answers them. Keys are not part of the conversation, are never returned by a tool, and are never needed to call one.

The hosted server has the opposite problem, because the connection itself needs a credential. There are two routes in. A customer can generate an API key, which is hashed at rest and displayed exactly once, capped at ten per account, with every key revoked automatically if the account password is reset. Or they can sign in interactively and connect with short-lived tokens, which can be disconnected in one click. Either way, a credential is scoped to exactly one account, and attempts to look up records belonging to somebody else are throttled rather than merely refused.

5. What the tool surface can afford to do

Now the interesting part. The two servers made opposite choices, and both are right.

The desktop server exposes about thirty tools, and a good number of them write. They can create a project, assign a policy, move an archive between destinations, retire a destination, or delete a copy. That is defensible because the person driving the assistant already owns the data and the machine. Deleting a copy through an assistant is not more dangerous than deleting it through the application's own interface: same person, same computer, same file.

What it does require is that consent is explicit and the work leaves a record. Permissions are a setting inside the application rather than something an assistant can grant itself. Read-only is the default. Destructive operations sit behind their own separate switch. Every action is written to an audit trail.

The hosted compliance server exposes sixteen tools and none of them can create, change, sign, delete, or pay for anything. That is not timidity. On a shared compliance service, a write tool means an assistant can hallucinate a state change into somebody's federal records. The blast radius is not a ruined afternoon, it is a legal record about a real person. So the write path stays where it belongs: on the website, performed by a human being, on purpose.

The rule that falls out of the comparison: how much a tool is allowed to do should track who absorbs the damage when it is wrong.

6. What the tool surface is allowed to see

The second divergence is quieter and, we think, more important.

The desktop server returns file paths, file names, checksums, and tape barcodes. Every bit of that is already sitting on the user's own disk. Handing it to an assistant running as a subprocess on the same machine leaks nothing that was not already there to read.

The hosted compliance server deliberately returns none of the identity data it holds. No names, no dates of birth, no ID numbers, no images, no document contents, no file paths. What comes back is compliance state: record codes, statuses, counts, dates, and content hashes. Records are referred to by public codes rather than by the people in them.

That is not a gap in the feature set. It is the reason the surface is safe to point at a shared assistant at all. There is nothing in the payload worth stealing.

7. Discovery works differently too

A desktop server is discovered the way software is discovered: registries, directories, and the installer the user already ran. A hosted server is discovered the way an API is discovered: documentation, a setup page, and a connect button inside the product. If you are building one, budget real time for whichever of those applies. A server nobody can find is indistinguishable from a server that does not exist.

What we would tell someone starting

Decide where the server runs before you write a single tool. Transport, authentication, versioning strategy, and tool design all fall out of that one choice, and reversing it later means rewriting all four.
Know which set of hard problems you just signed up for. On the desktop they are distribution, versioning, and permission design. On hosted they are identity, tenancy, and blast radius.
Keep credentials out of the conversation. No tool should return one, and no tool should require the assistant to hold one.
Treat the tool list as a public API contract either way. On the desktop because you can never take a tool back. On hosted because when you take it back, you take it back from everyone at once.
Let the blast radius decide the capability. Not the other way around, and not whatever the framework made easy.

Both servers are live. The desktop connector is documented at hiberden.app/mcp, including exactly what it can and cannot do. If you are building something similar and want a second pair of eyes on the architecture, that is the kind of work we do in custom development and local AI integration.

Originally published at agaveis.com.

LTFS and LTO Across Windows, macOS, and Two Flavors of Linux

Agave Information Soultions, LLC — Sat, 26 Sep 2026 00:29:14 +0000

LTFS sounds like a solved cross-platform problem. There is one specification, one reference implementation on GitHub, and the same three letters appear in every vendor's datasheet. Write to the standard once and ship everywhere.

That is not how it goes. We build archiving software that targets Windows, macOS, and Linux from one Rust workspace, and the tape layer is the single place where the three operating systems refuse to converge. Not because of small API differences, but because the word LTFS names two genuinely different architectures depending on which machine you are standing at.

This post is what actually differs, written from the platform backends rather than from the specification. It includes a macOS build defect that is not documented anywhere we could find, and which will cost you a day the first time you meet it.

One name, two architectures

On Linux and macOS, LTFS is a userspace filesystem. You install a binary called ltfs, it links against FUSE, and you mount a cartridge at a path the same way you would mount anything else. The tape becomes a directory. Everything downstream is ordinary file I/O.

On Windows, LTFS is a command suite plus a filesystem driver. IBM ships a family of executables, and you drive the tape by calling them in sequence:

LtfsCmdDrives.exe      enumerate attached drives
LtfsCmdAssign.exe      bind a drive to a letter
LtfsCmdLoad.exe        load the cartridge
LtfsCmdFormat.exe      write a fresh LTFS volume
LtfsCmdEject.exe       take the cartridge offline
LtfsCmdUnassign.exe    release the letter

The cartridge does not appear at a path you chose. It appears at a drive letter, and the thing you check to confirm you are looking at tape rather than at somebody's USB stick is the volume's reported filesystem name.

That difference propagates through everything. A mount on Linux is a path that either has a marker in it or does not. A mount on Windows is a letter that may or may not currently be bound to what you think it is bound to. Any abstraction over both has to be honest that it is hiding two different failure modes, not one.

The tool inventory, side by side

Here is what each backend actually shells out to. This is the clearest single picture of the problem:

Windows   LtfsCmdDrives / Assign / Unassign / Load / Eject / Format
Linux     ltfs, mkltfs, umount, fusermount, mt, df
macOS     ltfs, mkltfs, umount, mount, df

Read the third line twice. macOS is missing two tools that Linux has, and one of them is load bearing.

macOS has no way to eject a tape

On Linux, taking a cartridge offline is mt -f /dev/nst0 offline. mt comes from mt-st, it is packaged everywhere, and it has been the answer for decades.

macOS ships no tape control tool at all. There is no mt, and no system equivalent. So the eject verb, which is trivial on the other two platforms, has no implementation available to it.

What you do about that is a design decision rather than a coding one. Our answer was to make the failure honest and typed rather than silent: unmount first, which flushes the index to tape and leaves the cartridge complete and readable on another machine, then return a specific "eject unsupported" result telling the operator the cartridge is safe to remove with the drive's own button. The caller that matters here is a spanning writer whose operator is already standing at the drive with the next cartridge in hand, so it needs to tell "I could not eject" apart from "something went wrong" without matching on error strings.

There is a second route. The FUSE layer can be asked at mount time to eject on unmount, using the IOKit backend and no external tool. But it is a mount-time option, which means committing at mount to whether the session ends in an eject. That turns eject from a verb into a mode, and it is a change to the whole spanning flow rather than a one-line swap. Worth knowing it exists before you design around its absence.

FUSE cleanup differs too

When an ltfs mount hangs and you kill it, you are left with a stale FUSE endpoint that will refuse the next mount attempt. Clearing it is not the same command on both unix platforms:

Linux    fusermount -u <mountpoint>
macOS    umount <mountpoint>     (or diskutil unmount force)

macOS has no fusermount. If your recovery path assumes it, your retry works in development and wedges on a Mac, and the symptom is a mount failure that looks like a hardware problem.

Timeouts are the other thing everyone gets wrong

Tape operations do not run on human timescales, and a single global timeout will either kill legitimate work or hide a wedged controller for hours.

Formatting a fresh LTO-9 cartridge is the extreme case. The first load of a new cartridge can take anywhere from forty minutes to two and a half hours before the drive will even talk to you, and the format itself runs long after that. A mount-class timeout of a few minutes will reliably kill a perfectly healthy format.

So the timeout has to be a property of the operation class, not of the session. The implementation here settles at three floors, applied identically on all three platforms:

probe             30 seconds     is the drive there
index flush        1 hour        unmount, writing the index to tape
long operations    4 hours       format, and first-load waits

The floor matters more than the number. A caller can configure a longer timeout, but it cannot configure one shorter than the class floor, because a caller that shortens a format timeout is not expressing a preference, it is expressing a misunderstanding.

Two flavors of Linux, and they are not the two you expect

Ask which Linux distributions a desktop application supports and you expect an answer like "Ubuntu and Fedora". For a tape tool the meaningful split is not by distribution brand. It is packaged versus portable, and each carries a different set of problems.

The .deb: integrated, and honest about it

A Debian package declares its dependencies, and the package manager enforces them. A well-built one names the GUI toolkit and the dialog helper explicitly, and CI fails the build if either declaration goes missing from the produced package. That is the useful property: on a Debian or Ubuntu machine, a missing dependency is a clear install-time error naming the package, not a mystery crash on first launch.

The cost is a hard glibc floor. Linux binaries get built on Ubuntu 22.04 rather than on whatever "latest" currently means, because a binary linked against a newer glibc simply will not start on an older machine, and archive operators run old machines on purpose. Building on latest is the single easiest way to ship something that works on every developer's laptop and on none of the customer's servers.

The AppImage: portable, and it moves the problem

An AppImage carries its own userland and runs on distributions you never tested. That is genuinely useful for a tool whose users are on RHEL derivatives, Arch, or something a facility built itself in 2019 and will not touch.

What it does not carry is the kernel side. FUSE is a kernel feature, the tape device nodes are the host's, and the permissions on them are the host's. So an AppImage moves the dependency problem from install time to run time, and converts a clear package-manager error into a runtime failure inside the sandbox. It broadens reach and narrows diagnosability at the same time. Ship both, and know which one a given support conversation is about before you start debugging.

Building LTFS on macOS, which is where the day goes

This section is the reason we wrote the post. Building the reference LTFS implementation on Apple Silicon is not hard in the sense of being intellectually difficult. It is hard in the sense that six separate things go wrong in sequence, each with an error message pointing somewhere other than the cause.

In order, on macOS 15 on an M2:

macFUSE needs Reduced Security. It is a third-party kernel extension, and Apple Silicon ships with Full Security, under which the kext cannot load at all. Changing that requires booting into Recovery. It cannot be done over SSH, it cannot be scripted, and it cannot be done for a customer. Budget a person at the keyboard.
autogen.sh skips libtool silently. No error, no warning, and configure then fails later for a reason that has nothing to do with libtool.
icu4c is keg-only and versioned. Plain icu4c now returns a 404 from the Homebrew API. You need the versioned formula, and because it is keg-only it never lands on PATH, so configure dies claiming a tool called genrb was not found. Nothing in that message mentions ICU.
configure demands a uuid pkg-config file. It asks for uuid >= 1.6. macOS does have UUID, in libSystem, with no .pc file anywhere, so the check fails on a platform that has the functionality. Installing ossp-uuid satisfies the check.
SNMP is on by default and cannot build. configure calls net-snmp-config --cflags, which is not there, and the resulting error is truncated in a way that makes it look like a FUSE problem. It is not. Pass --disable-snmp.
macFUSE's framework is not where the compiler looks. It lives under /Library/Filesystems/macfuse.fs/Contents/Frameworks/, which needs an explicit -F.

Work through all six and it compiles. Roughly thirty seconds on an M2. And then you run the one command that matters and it dies.

The link defect nobody documents

Enumerating drives is ltfs -o device_list. On a freshly built macOS LTFS with no drive attached, that segfaults. Exit 139, with the crash inside libsystem_malloc on what looks like recursive allocation.

Every instinct says hardware, or macFUSE, or the missing drive. None of those is it.

It is a link defect. Upstream's IOKit plugin leaves its IOKit and CoreFoundation symbols to dynamic lookup, and nothing in the build actually links the frameworks that define them. The binary links clean, loads, and then dies the moment it reaches for a symbol that was never bound. Adding the frameworks fixes it:

export LDFLAGS="-L$ICU/lib -framework IOKit -framework CoreFoundation"

With that in place, ltfs -o device_list behaves correctly: it exits 1 and prints an empty device list, which is the right answer when no drive is attached. A clean negative result instead of a crash.

The trap underneath the trap is what a caller does with the crash. Any code that treats "device_list failed" as "no drives present" will render an empty drive picker and tell the user they have no tape hardware, when the truth is the enumerator died. A segfault and an empty list are not the same fact and should never collapse into the same UI state.

What this costs you, and what to do about it

Five things we would tell anyone starting a cross-platform tape project:

Do not abstract over Windows and unix too early. They are a command suite against a drive letter and a FUSE mount against a path. An interface that pretends otherwise hides the failure modes you most need to see.
Make timeouts a property of the operation class, with floors a caller cannot lower. Formatting is a four-hour operation on hardware that is behaving perfectly.
Let a platform say "I cannot do this" in a typed way. macOS cannot eject. That is better expressed as a specific result the caller can branch on than as a generic failure it has to parse a string out of.
Never let a crashed probe read as an empty result. Enumeration failing and enumeration returning nothing are different, and only one of them should be shown to a user as "no hardware found".
Fix a build recipe everywhere at once. A typical recipe lives in a runbook, a knowledge base article, and a CI workflow. A fix that lands in one of three is a fix that will be un-learned by whoever reads the other two.

The honest scope of this post

Everything above is about the software layer: build systems, tool inventories, mount semantics, timeout policy, and packaging. It is drawn from platform backends we wrote and a macOS build we did on a real machine.

It is not a hardware compatibility report, and we are not going to imply otherwise. Our current Rust implementation has not yet run against a physical tape drive on any operating system. Coverage today comes from a virtual drive and a simulated backend, and the first run on real hardware is a scheduled validation window covering all three platforms. The cross-platform differences described here are real and cost real time, but the claim stops precisely at the software boundary, because a tape tool that overstates what it has tested is the exact thing this industry does not need more of.

If you are building on tape across operating systems and want to compare notes, or you want the archiving software this came out of, Hiberden is where that work lives. Adjacent problems we take on: NAS infrastructure, data archiving, and custom development when the tool you need does not exist yet.

Originally published at agaveis.com.

Permission Filters and pgvector: Why Your Most Restricted Users Get the Fewest Answers

Agave Information Soultions, LLC — Sat, 26 Sep 2026 00:15:23 +0000

If you build AI search over documents that not everyone may read, you filter by permission. In PostgreSQL with pgvector, that usually means a WHERE clause on the caller's groups and an ORDER BY on vector distance. With no vector index, that query is exact and always complete. Add an HNSW index for speed, and something quiet happens: the people allowed to read the least start getting the fewest answers. Nothing errors. Nothing leaks, because the filter still holds. The list just comes back short.

Here is the mechanism, measured, and the two fixes. Everything below ran on PostgreSQL 17.10 with pgvector 0.8.4, and the script is at the end.

The query

Chunks of documents carry embeddings. Documents carry the groups allowed to read them. A search for one caller looks like this:

SELECT c.id
FROM chunk c
JOIN document d ON d.id = c.document_id
WHERE d.allowed_principals && ARRAY['group:hr']
ORDER BY c.embedding <=> $1
LIMIT 20;

The && operator is array overlap: a document qualifies if the caller holds any one of its groups. With no vector index, pgvector does exact nearest neighbor search, which its documentation describes as providing perfect recall. This query returns 20 rows whenever 20 readable chunks exist.

What an HNSW index changes

pgvector's README says it plainly: with approximate indexes, filtering is applied after the index is scanned. The scan produces a fixed list of candidates, sized by hnsw.ef_search, which is 40 by default, and the WHERE clause runs on that list. If the caller may read 10 percent of the rows, about 4 of the 40 survive.

We measured it. 100,000 chunks across 10,000 documents: one document in ten readable by group:hr, the other nine by group:staff. Random 64-dimension vectors, an HNSW index with default settings, the same 40 random queries for each caller, LIMIT 20:

Caller	Can read	Rows returned, of 20
group:staff	90 percent	20 on every query
group:hr	10 percent	median 4, as few as 1, never more than 8

Same query, same index, same data. The only difference is who asked.

Why permissions make it worse

A category filter is the same for everyone, so a short result shows up the first time anyone tests it. A permission filter depends on who is asking. Developers test with broad accounts, and a broad account gets full results. The short list only appears for the restricted user: the new hire, the contractor, the three-person team with its own folder.

And a short list does not look like an error. An AI assistant handed 4 passages instead of 20 answers from the 4. If the passage that mattered was number 5, it can tell a new hire there is no policy on something that has one, and the new hire has no reason to doubt it.

Fix one: iterative index scans

pgvector 0.8.0 added iterative index scans. When the filter leaves too few rows, the scan keeps going through the index instead of stopping at the candidate list:

BEGIN;
SET LOCAL hnsw.iterative_scan = strict_order;
-- the search query
COMMIT;

SET LOCAL ends with the transaction that runs the search, so a pooled connection cannot carry the setting anywhere else. Outside a transaction it does nothing. The other mode, relaxed_order, can return rows slightly out of distance order in exchange for better recall, and pgvector's README shows a materialized CTE that puts them back in order.

On the same 40 queries, the HR caller got 20 of 20 every time, in either mode. It costs time, because the scan now looks at more of the index. The median query went from 1.7 ms to 27.8 ms with strict_order, and to 18.1 ms with relaxed_order, on one desktop. For comparison, exact search, with the index turned off, took 47.5 ms.

Fix two: give narrow permissions an exact path

Iterative scans have limits. A scan stops after visiting about hnsw.max_scan_tuples entries, 20,000 by default, or when it reaches a memory cap of work_mem times hnsw.scan_mem_multiplier. A caller who may read almost nothing can hit those limits before 20 matches turn up.

So we added a third group, group:legal, readable on one document in a thousand: 0.1 percent of the chunks. Left alone, the planner did the right thing. It used the GIN index on allowed_principals to find the ten readable documents, joined their chunks, and sorted them by exact distance: 20 of 20 on every query, at a median of 33 ms.

Then we forced the query through the HNSW index, to see what happens when the planner picks it. With iterative scans on and the default limits, the median was 16 of 20, and the worst query returned 10. Raising hnsw.max_scan_tuples to 100,000 alone changed nothing. Raising hnsw.scan_mem_multiplier to 4 alone changed nothing. Raising both got 20 of 20, at a median of 732 ms: about 22 times slower than the exact plan the planner had chosen on its own.

So the second fix is not a setting. It is an ordinary index on the permission column. pgvector's README calls an index on the filter column "a good place to start" and notes that exact indexes work well for conditions that match a low percentage of rows. With one in place, the planner has a fast, complete path for your narrowest callers. Confirm it with EXPLAIN as your most restricted real user, not as yourself.

The test that catches it

Every number in this post came from asking as a specific group. That is the test that finds this problem, and a test run as an administrator never will. If your retrieval has a test set, give it cases that run as a restricted group and expect a specific document back. When an index change starves that group, the case fails before a user notices.

The retrieval engine we build for clients works this way. Its vector search is exact, with no approximate index, until a corpus is large enough to need one and the recall cost has been measured. Its test sets run questions as a named group, and a case fails when an expected document is missing from the top five results or a forbidden one appears in them.

Measure your own documents

Random vectors keep the arithmetic clean: what a caller loses tracks what fraction of the rows they can read. Real embeddings cluster, and permissions often follow topic, so an HR user asking an HR question may lose less, and the same user asking about something outside their documents may lose more. The timings come from one desktop and will differ on yours. Both are reasons to measure your own documents, as your own restricted users.

If you are putting AI in front of documents that not everyone may read, this is the kind of detail we work through with organizations that keep their data in-house: private AI.

Run it yourself

Needs PostgreSQL 17 with pgvector 0.8.0 or later; run it with psql. Each count comes from one random query, so yours will differ from the medians above. The index build may print a notice that it no longer fits in maintenance_work_mem; that only makes the build slower.

-- PostgreSQL 17 with pgvector 0.8.0 or later. Run with psql.
CREATE EXTENSION IF NOT EXISTS vector;

-- 10,000 documents: one in ten readable by group:hr, one in a thousand also by group:legal.
CREATE TABLE document (id int PRIMARY KEY, allowed_principals text[] NOT NULL);
INSERT INTO document
SELECT g, CASE WHEN g % 1000 = 0 THEN ARRAY['group:hr', 'group:legal']
               WHEN g % 10 = 0 THEN ARRAY['group:hr']
               ELSE ARRAY['group:staff'] END
FROM generate_series(1, 10000) g;
CREATE INDEX ON document USING GIN (allowed_principals);

-- 100,000 chunks, ten per document, random 64-dimension embeddings.
CREATE TABLE chunk (id int PRIMARY KEY, document_id int NOT NULL REFERENCES document(id), embedding vector(64) NOT NULL);
INSERT INTO chunk
SELECT g, (g % 10000) + 1, (SELECT array_agg(random() + 0 * g + 0 * i) FROM generate_series(1, 64) i)::vector(64)
FROM generate_series(1, 100000) g;
ANALYZE document;
ANALYZE chunk;

-- One random query vector.
SELECT (SELECT array_agg(random()) FROM generate_series(1, 64))::vector(64)::text AS qv \gset

\echo 'No vector index (exact search). Rows returned of 20, for group:hr:'
SELECT count(*) FROM (SELECT c.id FROM chunk c JOIN document d ON d.id = c.document_id
  WHERE d.allowed_principals && ARRAY['group:hr'] ORDER BY c.embedding <=> :'qv' LIMIT 20) t;

-- Single process build, so it fits a small container's shared memory.
SET max_parallel_maintenance_workers = 0;
CREATE INDEX ON chunk USING hnsw (embedding vector_cosine_ops);

\echo 'HNSW index, defaults. group:staff (reads 90 percent), then group:hr (reads 10 percent):'
SELECT count(*) FROM (SELECT c.id FROM chunk c JOIN document d ON d.id = c.document_id
  WHERE d.allowed_principals && ARRAY['group:staff'] ORDER BY c.embedding <=> :'qv' LIMIT 20) t;
SELECT count(*) FROM (SELECT c.id FROM chunk c JOIN document d ON d.id = c.document_id
  WHERE d.allowed_principals && ARRAY['group:hr'] ORDER BY c.embedding <=> :'qv' LIMIT 20) t;

\echo 'Iterative scans on. group:hr again:'
SET hnsw.iterative_scan = strict_order;
SELECT count(*) FROM (SELECT c.id FROM chunk c JOIN document d ON d.id = c.document_id
  WHERE d.allowed_principals && ARRAY['group:hr'] ORDER BY c.embedding <=> :'qv' LIMIT 20) t;

Agave Information Solutions builds on-premises AI systems, data architecture, and custom software out of Scottsdale, Arizona. If your AI search has to respect who may read what, get in touch.

Originally published at agaveis.com.

EF6 to EF Core: 5 query translations that quietly break in production

Agave Information Soultions, LLC — Wed, 03 Jun 2026 05:08:00 +0000

Most teams plan an EF6 to EF Core migration as "swap the package, fix some namespaces, ship it." For straightforward CRUD codebases that's mostly true. For anything with real query complexity, you will hit a handful of translation differences that the upgrade tooling does not warn you about. The queries compile fine, the unit tests pass against an in-memory provider, and then production explodes the first time someone hits the page that runs the report.

These are the five that bit us hardest on real migrations. None of them are obvious from reading the official "what's new" page, and a couple of them are silent regressions in behavior rather than visible errors.

1. Client-side evaluation does not silently rescue you anymore

EF Core 1.x and 2.x had a feature called automatic client evaluation. If part of your LINQ query could not be translated to SQL, EF Core would happily run that part in memory after fetching the rest of the data from the database. It logged a warning, but the query worked.

EF Core 3.0 changed the default to throw instead of silently falling back. Code that ran fine for two years suddenly throws InvalidOperationException: The LINQ expression could not be translated.

The worst version of this is a query like:

csharp
var customers = ctx.Customers
    .Where(c => c.DisplayName.NormalizeForSearch() == query.NormalizeForSearch())
    .ToList();

NormalizeForSearch() is a custom C# extension method. EF6 happily fetched every row, ran the method on each one in memory, and filtered. The query was a full table scan and stayed quietly slow for years. After the EF Core upgrade it throws at runtime.

The remediation is not "add .AsEnumerable() before the Where" even though that "works." That just preserves the original full-table-scan behavior with the original O(n) memory cost. The right fix is to push the normalization into the query itself using EF.Functions.Like or a normalized column maintained at write time. Either of those produces real SQL with real index usage.

GroupBy translates much less aggressively

EF6 translated almost any GroupBy you threw at it. EF Core is far more conservative — for years, it required your GroupBy to fit a narrow shape (group key + aggregate functions) and would throw if you tried to project the group elements directly.

This works in both:


var totals = ctx.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new { CustomerId = g.Key, Total = g.Sum(o => o.Amount) })
    .ToList();

This worked in EF6 but did not in EF Core 3-5, and only partially works in EF Core 7+:


var groups = ctx.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new { CustomerId = g.Key, Orders = g.ToList() })
    .ToList();

The second one is asking for "give me each group along with the list of items in that gr# LINQ but hard to express in standard SQL. EF Core 7 added partial support via SQL Server window functions; older versions throw.

For migration purposes, audit every GroupBy in the codebase before you flip the switch. The shape that breaks most often is grouping followed by projecting the actual group elements rather than aggregations.

Restructuring usually means doing the grouping client-side after a flat query, or rewrifor aggregates.

String comparisons changed collation behavior

EF6 ran string comparisons through SQL Server with the database's default collation, whie accent-insensitive on most installations. EF Core leaves the comparison semantics up tothe provider, and the defaults are different across providers.

Where(c => c.Email == "foo@bar.com")

against a Latin1_General_CI_AS database returns rows where Email equals FOO@BAR.COM because the SQL collation is case-insensitive. The same query against PostgreSQL
returns zero rows because PostgreSQL is case-sensitive by default. If your application wver provision a PostgreSQL instance, you will spend a confused afternoon.

EF Core 5+ exposes EF.Functions.Collate for explicit collation control, and EF.Functionsttern matching that works the same across providers. Use those instead of relying on thedatabase default if your code might run against more than one provider.

A subtler form: string.Equals(c.Name, query, StringComparison.OrdinalIgnoreCase) translated to ordinary = in EF6 (the comparison enum was just ignored), but in EF Core it can throw "could not be translated" depending on version. Same shape of bug as #1.

DateTime.Now lands client-side in confusing ways

DateTime.Now evaluated in the context of a LINQ query does not have a consistent transla and providers.

var recent = ctx.Orders
    .Where(o => o.CreatedAt > DateTime.Now.AddDays(-7))
    .ToList();

EF6 translated this to WHERE CreatedAt > DATEADD(day, -7, GETDATE()) on SQL Server, whicery time. EF Core sometimes evaluates DateTime.Now once on the client at query construction time, then sends a constant to SQL. If your app builds the query at 12:00 and executes it at 12:30, EF6 uses 12:30, EF Core often uses 12:00.

For most queries this 30-minute drift does not matter. For session expiry, rate limiting, scheduled job pickups, or audit windows, it matters a lot.

The deterministic pattern: capture the time explicitly in a local variable and pass it as a parameter. Or use EF.Functions.GetUtcDate() to force server-side evaluation. Or move the time calculation into a
database function and call it explicitly. Any of those produces predictable behavior.

Lazy loading is off by default and the fix is not what you think

EF6 enabled lazy loading by default if your navigation properties were marked virtual. Ty fetches it.

EF Core does not enable lazy loading by default. The migration path that the documentatirosoft.EntityFrameworkCore.Proxies and call UseLazyLoadingProxies() on the options. That technically restores lazy loading, but it makes every navigation access into a separate database round trip — exactly the N+1 problem the rest of the EF Core team is trying to push you away from.

The better migration path is to audit where your code was relying on lazy loading and convert those sites to either explicit Include calls or projection into DTOs. It is more work, but the resulting queries
are faster, the N+1 patterns become visible at code review time instead of in production table.

If you do install the lazy loading proxies as a "get the migration unblocked" bridge, to put a real plan in place to remove it. The longer you ship on lazy proxies, the more code grows to depend on the implicit fetch behavior and the harder removal gets.

The pattern across all five

Every one of these breakages comes from the same root: EF6 was very permissive about hiding the gap between LINQ semantics and SQL semantics, and EF Core is less so. EF Core fails loudly (or silently
differently) in places where EF6 would have happily run something inefficient.

That sounds like a regression. It is actually the right tradeoff. The EF6 queries that "l table scans, N+1 patterns, or subtly wrong results that nobody noticed because the database was small enough that performance didn't expose them. EF Core's stricter posture forces those decisions to surface during migration instead of in production a year later when the data has grown.

The practical migration playbook:

Turn on LogTo for Microsoft.EntityFrameworkCore.Query.QueryCompilationStarting events during integration tests so you can see what each query actually translates to.
Run a load test against representative data volume before declaring the migration don the dev database often fail on production volume after the EF Core change.
Audit every GroupBy, every Where with custom methods, every navigation property access, and every DateTime.Now reference.
Treat Microsoft.EntityFrameworkCore.Proxies as a bridge, not a destination.

The "swap the package and ship it" plan turns into a multi-week project for non-trivial modernization is one of the things we work on most often at Agave Information Solutions(https://agaveis.com/dotnet-modernization). If you are heading into one of these migrations and want a sanity check on your pattern audit, the .NET Modernization (https://agaveis.com/dotnet-modernization)
page has more on how we approach it.

Agave Information Solutions

Agave Information Soultions, LLC — Wed, 03 Jun 2026 04:58:25 +0000

30 years on the Microsoft stack. Started writing .NET when the framework was still in beta in 2001.

Founded Tempest Telecom in 1996. We built the first Internet roaming service to provide access on all 7 continents, and a unified AAA platform that combined voice, dialup, WiFi, and satellite into one experience. The 2+ petabyte video platform that came out of that work is where I learned that query plans behave non-linearly at scale, and why backup strategy is architecture.

Founded Agave Information Solutions in Scottsdale in 2008. Today we modernize legacy .NET Framework applications onto modern .NET 10/9/8 with AI-ready architecture, design databases that need to handle real volume, and ship the kind of senior-engineering work most boutique shops outsource.

I write here about .NET migrations, EF Core gotchas, SQL Server query plans, AI integration into legacy stacks, and the lessons from running production systems at scale for a long time.

Find me elsewhere: