Efcore: A production aimed In-memory EF Core provider with transactional capabilities

[Larry57]

Hello,

I often use a memory object graph for my business objects, which is completely separate from the persistence layer that uses EF Core.

Since there is a background loop that performs high frequency operations, I cannot afford to read and write from a database all the time unless I need to.

However, the persistence layer with EF Core uses transactions to ensure data integrity in case of a problem. As my business layer also has a set of sequential operations, I would like to have the same transactional functionality to ensure the "all or nothing" capability.

I could implement the transactional behaviour in my business layer on my own, but this would require a lot of work (cloning objects, performing operations on the cloned objects, then hanging the new object on the graph if all goes well, or throwing it away if it doesn't).

Because EF Core entities are flexible enough, the idea would be to have a real "In memory" provider that would benefit from transactional functionality and would be very fast because the data would reside in memory.

I know an EF Core "In Memory" provider exists, but it is recommended for testing purposes only. That is why a real "In Memory" provider for production use with transactional capabilities would be great.

[roji]

@Larry57 a common way to avoid going to the database too much, is to employ some sort of caching mechanism; results are cached in memory (or in a distributed memory cache), and evicted based on some sort of policy. EFCoreSecondLevelCacheInterceptor is an example for such a thing.

I'm proposing this because I assume that in your scenario, like in almost all others, the source of truth still is the database, and you occasionally do need to turn to it for current information (not to mention updates). If that's the case, a caching layer is usually the better way to address perf issues than another in-memory provider (which would raise the question of how to sync that with the database, etc.).

[ajcvickers]

@Larry57 I agree with @roji. You might also want to take a look at SQLite, which is a production-quality database and has the ability to store data in memory. We're not planning on creating a production-quality in-memory database inside an EF Core provider.

[roji]

There's also memory-optimized tables in SQL Server in case that helps.

[Larry57]

Thank you for your advice! Using SQLite's in-memory mode is a great idea! How did I not think of it earlier? I will seriously consider this option with EF Core in my next "stand alone" application rather than manipulating a graph of in-memory objects.
I'm still wondering if it still makes sense to completely separate the persistence layer these days, as EF core is so flexible and would then offer transactional possibilities directly at the business layer.
I will also try the second level cache with SQL server in the same way. My fear of performance with SQL server is that our databases are heavily used by other "legacy" applications, so I try to rely on it as little as possible.