C# Application Moves from Database Connections to Polyglot Persistence

Table of Contents

1. Challenge

   Step 1: Meet Globomantics single-connection problem

   Globomantics: from one connection to polyglot persistence

   OrbitCommerce is an order and inventory service developed by Globomantics for small online retailers. Right now its entire persistence layer is one hand-rolled SqliteConnection, opened wherever code needs it, doing catalog reads, stock writes, and reporting queries alike. That single connection has no notion that reporting should be read-only, no schema evolution story, no seam for testing the order logic without a live database, and no cheap path for the summaries and audit trails customers and support staff look up over and over.

   Over the next four steps, you will replace that one connection, store by store:

   • A provider-matched secure connection selector that opens the right kind of SQLite connection for the workload asking for it.
   • An EF Core catalog that models products, seeds them, logs the SQL it generates, and migrates its own schema.
   • Raw ADO.NET reporting and audit writes, with disposal that holds up even when a query throws.
   • A repository and unit of work that hide EF Core behind an interface a test can replace with a fake, so order logic is testable without a database.
   • HybridCache and a partition/row-keyed store feeding a single order-placement call that exercises every store you built. ## How the steps fit together

   Each step adds a new capability to Globomantics, and step 5 brings everything together into a single order-placement operation. By the end, PlaceOrderAndSummarizeAsync will:

   • Check and reserve stock through the repository and unit of work from step 4.
   • Record an audit entry in the table-storage-style store from step 5.
   • Return a cached order summary using HybridCache from step 5.

   The EF Core catalog from step 3 supports the repository by handling the underlying database reads and writes.

   How to run the project

   The starter project already builds, but every method you will complete is a stub that throws NotImplementedException. The application catches these exceptions and displays a not built yet message for each incomplete task. Each task's validation check will fail until you replace the corresponding stub with working behavior.

   You can run the application at any point to see which tasks are still reported as not implemented. In the Terminal tab, run:

   dotnet run --project src/Lab.csproj
   

   Each task goes beyond creating a type or interface. You'll implement functionality that you can validate and observe in action.

2. Challenge

   Step 2: Build the secure, provider-matched connection selector

   OrbitCommerce's catalog workload needs to write, and its reporting workload should never be able to. A single connection string can't express that difference, but Microsoft.Data.Sqlite's SqliteConnectionStringBuilder can: its Mode setting controls whether a connection can create, write, or only read a database file, and its ForeignKeys setting turns on SQLite's foreign-key enforcement.

   src/ConnectionSelector.cs already declares a WorkloadKind enum and a SelectConnection method that routes a workload to the right factory method -- you're about to make those factory methods actually match their workload's security needs. Catalog writes now open a connection with foreign keys enforced. Reporting shouldn't inherit that same write-enabled connection just because it reads from the same file. It needs a builder configured for ReadOnly mode instead, so a reporting query can never accidentally mutate stock or prices.

3. Challenge

   Step 3: Create and evolve the catalog with EF Core

   The catalog needs more than a connection. It needs a schema, and a way to get real rows into it before anything can query them.

   src/CatalogContext.cs defines a Product entity and a DbContext that exposes it as a Products set.

   Database.EnsureCreated() turns that model into an actual SQLite schema, and SaveChanges() persists whatever you've added to the context's change tracker. You'll use both to get the catalog off the ground. With products seeded, you need to see what SQL EF Core actually runs when you query them, not to change the query's result, but to trace it.

   OnConfiguring already wires up LogTo so EF Core log messages are written to CapturedSql. The property stores the most recent log message, which can include generated SQL when a query executes. The LINQ query you write must be materialized to execute and generate a SQL command. Schemas change. Rather than reaching for EF Core's full migration tooling, you can evolve a SQLite table directly with Database.ExecuteSqlRaw, which runs a raw SQL string against the context's connection. This lets you add a column and then backfill it for every row that already exists.

4. Challenge

   Step 4: Query with ADO.NET and isolate order logic behind a repository

   Reporting queries don't need EF Core's change tracking. They need a fast, parameterized read. The methods in src/InventoryReports.cs will open a SqliteConnection and a SqliteCommand directly.

   Both are IDisposable, and a using statement guarantees Dispose runs whether the code inside it returns normally or throws. This matters the moment a malformed query or a locked database causes ExecuteReader to fail partway through. Audit writes have the same disposal problem as reads, but with an extra twist: the insert itself might be the thing that throws, and the command still has to be disposed afterward.

   A try/finally around the command (or a using declaration, which desugars to the same thing) disposes it on every exit path, successful or not. Order logic shouldn't know it's talking to EF Core. It should talk to an interface, so a test can substitute something that isn't a real database.

   src/InventoryRepository.cs declares IInventoryRepository and an InventoryRepository implementation backed by the CatalogContext from step 3.

   AdjustStockAsync is already wired up; GetStockAsync is the read side of that same interface. With reads and writes both behind IInventoryRepository, the unit of work in src/OrderUnitOfWork.cs can enforce a business rule, never oversell stock, without touching SQL at all.

   Because it depends only on the interface, a test can hand it a hand-written fake of IInventoryRepository and assert exactly which methods got called, with what arguments, for a given stock level.

5. Challenge

   Step 5: Add NoSQL paths and combine every store in order placement

   Order summaries are read far more often than they change, which makes them a good fit for a cache instead of a repeated query.

   HybridCache.GetOrCreateAsync takes a key and a factory delegate: on a cache hit it returns the stored value without calling the factory at all, and on a miss it calls the factory, stores the result, and returns it.

   src/OrderSummaryCache.cs wraps that behind GetSummaryAsync, backed by a computeSummary delegate the constructor already holds. Audit trails fit a different shape: many independent rows, looked up by a coarse partition and a fine-grained row identifier, the way table storage organizes data.

   src/AuditTableStore.cs simulates this behavior using an in-memory dictionary keyed by a (PartitionKey, RowKey) tuple. Unlike a persistent table-storage service, the dictionary does not retain data across application restarts.

   GetPartition already reads every row for a given partition. Upsert is the write operation that stores rows and replaces earlier values at the same keys. Every store you've built now has one job: the unit of work reserves stock, the audit store records that it happened, and the cache serves the summary.

   src/OrderPlacementFacade.cs holds all three through the interfaces they implement: IOrderUnitOfWork, IAuditStore, and IOrderSummaryCache. Just as the unit of work depends only on IInventoryRepository, that lets a test swap in a stand-in for each one.

   PlaceOrderAndSummarizeAsync is the single call that has to sequence them correctly, including refusing to audit or summarize an order that never got placed.

6. Challenge

   Step 6: Watch the persistence layer at work

   After completing and validating all tasks, run the application to see how the components work together. Then modify three examples to verify their behavior.

   1. Run the tour

   In the Terminal tab, run:

   dotnet run --project src/Lab.csproj
   

   src/Program.cs runs the prewritten application workflow, calling the methods you completed throughout the lab. The application deletes and recreates the database each time, so you can rerun it to verify your implementations.

   The next three exercises involve small edits to src/Program.cs. ### 2. Try to write through the reporting connection

   The reporting connection is meant to be read-only. To prove it, find this line in src/Program.cs:

   Console.WriteLine($"Reporting connection open, state = {reportingConnection.State}");
   

   Add this directly below it:

   try
   {
       using var write = reportingConnection.CreateCommand();
       write.CommandText = "CREATE TABLE Probe (Id INTEGER)";
       write.ExecuteNonQuery();
       Console.WriteLine("Reporting connection wrote to the database!");
   }
   catch (SqliteException ex)
   {
       Console.WriteLine($"Reporting write refused: {ex.Message}");
   }
   

   Rerun the tour. Instead of the write going through, you should see Reporting write refused: SQLite Error 8: 'attempt to write a readonly database'. That refusal comes from the Mode = SqliteOpenMode.ReadOnly setting you added in Task 2.2, not from any check in your own code. ### 3. See the SQL EF Core generated

   Find this line:

   Console.WriteLine($"Products at or above 20.00: {expensive.Count}");
   

   Add this below it:

   Console.WriteLine(catalog.CapturedSql);
   

   Rerun the tour. Your one-line LINQ Where from Task 3.2 appears as a real SELECT ... FROM "Products" ... WHERE statement.

   Notice that the price threshold arrives as the parameter @minimumPrice rather than as a number pasted into the SQL. EF Core gives you the same protection that you added by hand to the ADO.NET query in Task 4.1. ### 4. Catch the cache serving a stored value

   Find this line:

   Console.WriteLine($"Cached summary for order 42: {summary}");
   

   Add a second lookup for the same order below it:

   await Task.Delay(100);
   var again = await summaryCache.GetSummaryAsync(42);
   Console.WriteLine($"Second lookup: {again}");
   

   Rerun the tour. Each summary records the moment it was computed, and both lines show the same timestamp even though the second lookup ran 100 ms later.

   HybridCache returned the stored summary without calling the factory again. Change the second lookup to GetSummaryAsync(43) and the timestamps will differ, because a new key is a cache miss.

   That's the lab: the single connection you started with is now a store matched to each workload, and you've watched three of them do the job they were built for.