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.

Pet Exception

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.

Here is my PetsDbContext.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

public class PetsDbContext : DbContext
{
    // Rest of code omitted for brevity
    public DbSet<Pet> Pets { get; set; }

    public PetsDbContext()
    {

    }

    public PetsDbContext(DbContextOptions<PetsDbContext> options) : base(options)
    {

    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Rest of code omitted for brevity
        modelBuilder.ApplyConfiguration(new PetConfiguration())
    }
}

and my entity model for Pet.

1
2
3
4
5

public class Pet
{
    public int Id { get; set; }
    public string Name { get; set; }
}

and finally, here is my entity type configuration class for Pet.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

public class PetModelConfiguration : IEntityTypeConfiguration<Pet>
{
    public void Configure(EntityTypeBuilder<Pet> builder)
    {
        builder.HasNoKey();

        builder.ToView("vPet");

        builder.Property(p => p.Id)
            .IsRequired()
            .HasMaxLength(50);
    }
}

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.

EFCore ToView Note

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

[Fact]
public async 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]
public async 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();
    }
}