SQLite is an embedded SQL database that can keep data in memory or in a local file. With Entity Framework Core, it provides SQL behavior that EF Core’s InMemory provider does not. The in-memory mode is useful while developing an application or running integration tests; file storage lets the same application retain data between runs. section 5.3 section 5.6
Listing 5.17 Configuring CatalogContext to use the Sqlite database
using Microsoft.EntityFrameworkCore;
namespace ManningBooks;
public class CatalogContext : DbContext
{
public const string ConnectionString = #1
"DataSource=manningbooks;mode=memory;cache=shared"; #2
public DbSet<Book> Books { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options
.UseSqlite(ConnectionString); #3
}
Configure a DbContext with UseSqlite. The connection string selects a named, shared-cache in-memory database; switching modes is a connection-string decision. section 5.3
An in-memory SQLite database disappears when its last connection closes. EF Core does not hold a connection open just to preserve it, so open a separate SqliteConnection for the database’s intended lifetime. Create and dispose DbContext instances for individual requests rather than keeping one as a singleton. section 5.3
Listing 5.18 Using Sqlite in Program.cs
using ManningBooks;
using Microsoft.Data.Sqlite; #1
using var keepAliveConnection = new SqliteConnection( #2
CatalogContext.ConnectionString); #3
keepAliveConnection.Open(); #4
using var dbContext = new CatalogContext(); #5
dbContext.Database.EnsureCreated(); #6
dbContext.Add(new Book(".NET in Action"));
dbContext.Add(new Book("API Design Patterns"));
dbContext.Add(new Book("Grokking Simplicity"));
dbContext.Add(new Book("The Programmer's Brain"));
dbContext.SaveChanges(); #7
foreach (var book in
dbContext.Books.OrderByDescending(b => b.Id)) #8
{
Console.WriteLine($"Book \"{book.Title}\" has id {book.Id}");
}
The keep-alive connection stays open while a separate context creates the schema, inserts books and queries them. EnsureCreated does nothing if any tables already exist, even tables outside that context’s schema. section 5.3
Query without blocking a request
Seed books once, then create a fresh CatalogContext for each title lookup. Await FirstOrDefaultAsync, include ratings when they are needed, and handle a missing book. Database requests are candidates for async code because access can involve disk work; SQLite supplies SQL behavior for practicing this pattern. section 5.3 section 5.6
For integration tests, SQLite can supply a lightweight in-memory database while an HTTP message handler fakes external calls. Give test classes different DataSource names if they run in parallel: classes using the same name can operate on the same in-memory database. section 8.4 section 8.6
