Are you using SQLite as an in-memory provider for EF Core on your Unit/Integration test? If you are, you may come across the following exception when creating the in-memory database.
As you can see from the exception, the error is “SQLite Error 1: ’no such table vPet’” which is odd because vPet is defined as a SQL view on my DbContext, not a SQL table.
Notice the usage of .ToView() on my entity configuration class, per the EF Core documentation, the method .ToView assumes that the database object vPet has been created outside of the execution EnsuredCreated or EnsureCreatedAsync.
1
2
3
4
5
6
7
8
9
10
11
12
13
[Fact]publicasync Task Test_UsingSqliteInMemoryProvider()
{
var options = new DbContextOptionsBuilder<PetsDbContext>()
.UseSqlite("DataSource=:memory:")
.Options;
using (var context = new PetsDbContext(options))
{
// This fails to create vPet view.await context.Database.EnsureCreatedAsync();
}
}
In other words, any entity configuration that utilizes .ToView() on your DbContext will not generate a corresponding SQL view. This is why SQLite is throwing the error “SQLite Error 1: ’no such table vPets’”. To get around this problem, you can write a script that generates the missing View in SQLite, for example.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
[Fact]publicasync Task Test_UsingSqliteInMemoryProvider()
{
var options = new DbContextOptionsBuilder<PetsDbContext>()
.UseSqlite("DataSource=:memory:")
.Options;
using (var context = new PetsDbContext(options))
{
// vPet will now be created context.Database.ExecuteSqlRaw("CREATE VIEW vPets as p.Id, p.[Name] FROM Pet p");
await context.Database.EnsureCreatedAsync();
}
}