We have a .NET 7 application, that uses SQL Server and EF 7.
We have a memory cache that we read a large part of the database into (An “Item”-table with ~20 Includes), and refresh at regular intervals.
Because of the many includes, we need to use .UseSplitQuery() or the cartesian explosion would eat up all memory, causing the query to eventually fail.
This setup has worked fine for many years.

The last few months we’ve had a problem that this read operation might return objects with missing data.
But it’s only one to many-relationships, on the many-side that fail, and only if we update or insert an item at the same time.
There are no exceptions thrown.
It appears regardless of whether I use Azure SQL Server or a locally hosted one.

Most commonly it’s the item that has been updated that gets delivered without for instance “Multimedia”-rows. But it can also cut off so almost no of the items gets and “Multimedia” rows.
And it it fails at multimedia, all other one-to-many relationships after that will loose all their data for all items.

So if we say we have this code:

Ctx.Item
.Include(b => b.Multimedia)
.Include(b => b.ItemContact).ThenInclude(bk => bk.Contact)
.Include(b => b.Subtypes)
.Include(b => b.EnvironmentalCertificates)
...

if it fails halfway through reading ItemContact, then all Subtypes and EnvironmentalCertificates will be empty as well.

All changes are written to the DB using a single SaveChanges(), which means it should all be done in a single transaction.

Any idea what might be causing this, or what I might test? I’m starting to run out of ideas.

We’ve tried:

• Calling different SQL Servers (Both local and Azure)
• Downgrading to .NET / EF 6
• Upgrading to .NET / EF 8
• Extra transactions around the insert/update operations (in case EF:s SaveChanges didn’t properly protect it)
• Removing all but one include
• and lots and lots of other things.

For now, the problem has been patched by writing an “insanity detector” that notices if it suddenly looses alot of one-to-many-rows from one data fetch to another. But that’s not really a “solution” to the problem.