Use SQLite In-Memory Database in ASP.NET Core
In the previous
article you learned to use Entity Framework Core InMemory provider to store
and retrieve your application data for testing and demoing purposes. An
alternative (and a recommended option) is to use SQLite database in in-memory
mode. To that end this article discusses just that.
To get started, create a new ASP.NET Core web application using empty project
template. You could have also use Razor Pages project template but we will use
empty project to skip the default project items that get added.
Then add NuGet package for the SQLite data provider for EF Core.
Next, add a folder named DataAccess in the project root and add two class
files namely Customer.cs and AppDbContext.cs.
The Customer class is EF Core entity class and looks like this:
public class Customer
{
public string CustomerID { get; set; }
public string CustomerName { get; set; }
}
The AppDbContext class is a custom DbContext class and looks like this:
public class AppDbContext:DbContext
{
public AppDbContext(DbContextOptions<AppDbContext>
options):base(options)
{
}
public DbSet<Customer> Customers { get; set; }
}
Notice that the AppDbContext class inherits from DbContext base class and
contains Customers DbSet.
When you use SQLLite in in-memory mode a database is created and deleted with
a connection open and close respectively. This will be problematic when using EF
Core because a DbContext automatically opens and closes a connection when a
query is executed. As a remedy we need to pass an open connection to EF Core so
that it won't resort to the default way of working and use our connection instead.
Of course, we now need to close the connection ourselves because EF Core won't
do that anymore.
To do this, open Program.cs and register the AppDbContext with the DI container.
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddRazorPages();
var cnn = new SqliteConnection("Filename=:memory:");
cnn.Open();
builder.Services.AddDbContext
<AppDbContext>(o => o.UseSqlite(cnn));
var app = builder.Build();
app.MapRazorPages();
app.Run();
Note that we registered Razor Pages related services using AddRazorPages()
and AppDbContext using AddDbContext() call. We have also called MapRazorPages()
to configure Razor Pages routing.
Also note the code marked in bold letters. It creates an SqliteConnection
object by specifying in-memory mode. We then open the connection using Open()
method. The opened connection is passed to the UseSqlite() method while
registering the AppDbContext.
Our AppDbContext is ready to be injected in the other parts of the
application. But we will also seed a few sample Customer entries into the
in-memory database. To do this we will write a helper method as shown below.
static void AddCustomerData(WebApplication app)
{
var scope = app.Services.CreateScope();
var db = scope.ServiceProvider.GetService<AppDbContext>();
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
var customer1 = new Customer
{
CustomerID = "Customer ID 1",
CustomerName = "Customer Name 1"
};
var customer2 = new Customer
{
CustomerID = "Customer ID 2",
CustomerName = "Customer Name 2"
};
var customer3 = new Customer
{
CustomerID = "Customer ID 3",
CustomerName = "Customer Name 3"
};
db.Customers.Add(customer1);
db.Customers.Add(customer2);
db.Customers.Add(customer3);
db.SaveChanges();
}
The AddCustomerData() method should be placed immediately after the app.Run()
call.
The AddCustomerData() method accepts a WebApplication parameter. Inside, it
retrieves an instance of AppDbContext from the DI container. This is done using
the CreateScope() and the GetService() methods.
Notice the calls to EnsureDeleted() and EnsureCreated() that ensure that
SQLite in-memory database is created for us.
Then we create three Customer entities and add them to the Customers DbSet.
Finally, we call SaveChanges() to save the data.
We will call the AddCustomerData() method below the builder.Build() line.
...
...
var app = builder.Build();
AddCustomerData(app);
app.MapRazorPages();
app.Run();
...
...
Now that we seeded some test data, let's create a Razor Page that displays
and modifies this data.
Add a new folder named Pages in the project root and add a Razor Page named
Index.cshtml in it using Add New Item dialog.
Open the IndexModel page model class and add the following code to it.
public class IndexModel : PageModel
{
public readonly AppDbContext db;
public List<Customer> Customers { get; set; }
public IndexModel(AppDbContext db)
{
this.db = db;
}
public void OnGet()
{
Customers = db.Customers.ToList();
}
public void OnPostUpdate()
{
Customers = db.Customers.ToList();
foreach (var item in Customers)
{
item.CustomerName += " Changed";
}
db.SaveChanges();
}
public void OnPostClose()
{
var cnn = db.Database.GetDbConnection();
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
cnn.Dispose();
}
}
}
We added a variable for storing the injected AppDbContext. We also added
Customers property that stores a List of Customer entities.
The constructor of the class accepts the injected AppDbContext and stores it
in the db local variable.
The OnGet() page handler is called when the page is initially loaded in the
browser. It simply retrieves a List of Customer object using the ToList() method
and stores it in the Customers property.
The OnPostUpdate() page handler is called when a POST request is made using
the Update Customers button (more on that shortly). Inside, we fetch the
Customers data again and modify CustomerName of each Customer entity. This is
done just to check whether the in-memory database remembers our changes
successfully.
The OnPostClose() page handler is called when we click on the Close
Connection button. Inside, we get the EF Core's underlying database connection
using GetDbConnection() method. We check the State of the connection. If it is
Open we close and dispose the connection. This way we ensure that the
SqlliteConnection opened in the Program.cs is closed when not needed.
Now open the Index.cshtml file and add the following markup in it:
@page
@model InMemoryDbDemo.Pages.IndexModel
@addTagHelper *,Microsoft.AspNetCore.Mvc.TagHelpers
<h1>List of Customers</h1>
<table border="1" cellpadding="10">
<tr>
<th>Customer ID</th>
<th>Customer Name</th>
</tr>
@if (Model.Customers != null)
{
@foreach (var item in Model.Customers)
{
<tr>
<td>@item.CustomerID</td>
<td>@item.CustomerName</td>
</tr>
}
}
</table>
<br /><br />
<form method="post">
<button type="submit" asp-page-handler="Update">
Update Customers
</button>
<button type="submit" asp-page-handler="Close">
Close Connection
</button>
</form>
Notice the code shown in bold letters. We enable tag helpers for this page
using @addTagHelper directive.
We render a list of customers in a table. Before rendering individual rows,
we check if Model.Customers property is null or not. If it has some data we run
a foreach loop and render table rows.
At the bottom of the page we have a form with two buttons - Update
Customers and Close Connection. The asp-page-handler attribute of the Update
Customers button element is set to Update
indicating that our OnPostUpdate() page handler should be called upon the form
submission. Similarly, the asp-page-handler attribute of the Close Connection button element is set to
Close indicating that our OnPostClose() page handler should be called upon the form
submission.
Save all the files and run the application. If all goes well you will see a
table as shown below.
Now click on the Update Customers button and see whether the changes are
reflected on the UI as shown below.
Also try clicking on the Close Connection button and see what happens. You
will find that no data is displayed indicating that the database is deleted once
the connection is closed.
If you stop the application and run it again you will find the original data
(that we seeded in Program.cs) again indicating that the changes are in-memory
and non persistent between multiple application runs.
You may read more about EF Core SQLite provider in the official
documentation
here and
here.
That's it for now! Keep coding!!