CODEWITHSUNDEEP
c#sqliteentity-framework-core
Toad
Toad

Reputation: 388

context.EnsureCreated() throwing exception - already a table named

Creating unit tests with Sqlite InMemory is giving exceptions with and without EnsureCreated(). I'm testing on the MS Northwind database.

If I call ensureCreated() is throws an exception "Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'there is already a table named Region'."

My understanding is that EnsureCreated() will only attempt to create the database if it is not already created.

It looked like it had therefore already created the DB, so I tried removing that call which resulted in an exception later because the tables didn't exist. So, I get exceptions with EnsureCreated() and no tables without it.

I have already checked the context.OnModelCreating and the table 'Region' is only defined once.

Note that this works fine running on an existing SQL database and also allowing EFCore to create a new SQL database. The problem only exists with sqlite in-memory mode.

public async Task UpdateCategoryWithCorrectEtag(Category expectedCategory)
{
       var connection = new SqliteConnection("DataSource=:memory:");
       connection.Open();
       var builder = new DbContextOptionsBuilder<NorthwindContext>().UseSqlite(connection);
       var context = new NorthwindContext(builder.Options);

       try
       {
           context.Database.EnsureCreated();
           var x = context.Categories.Count();
       }
       finally
       {
           context.Database.EnsureDeleted();
           connection.Close(); 
       }
}

EDIT: The 'Region' entity defined as follows. I also note that this seems to be the only table where the Entity name already matches the table name.

modelBuilder.Entity<Region>(entity =>
{
    entity.ToTable("Region");
    entity.HasKey(e => e.RegionId)
        .ForSqlServerIsClustered(false);

    entity.Property(e => e.RegionId)
        .HasColumnName("RegionID")
        .ValueGeneratedNever();

    entity.Property(e => e.RegionDescription)
        .IsRequired()
        .HasMaxLength(50);
});

UPDATE: I already tried removing the line entity.ToTable("Region"); to see if that was causing the table to be created twice, however, this still has the same problem. Currently I've removed all tables except for the 2 I absolutely need to run the test and it works. Putting them back in one by one to see what breaks. Will report back.

Upvotes: 4

Views: 2330

Answers (1)

Toad
Toad

Reputation: 388

I have the answer after spending more time removing all tables and then adding items until the problem returned.

The problem was caused by indexes on tables being the same as indexes on other tables, or as a table name. With SQL Server and EF Core in-memory, this was OK because it saw them as indexes on the table. But, with Sqlite, it wasn't happy about different objects having the same name, regardless of their apparent scope.

The solution was to ensure that all objects' names were unique so nothing clashed at all. In this case, the name 'Region' was the name of a table and the name of an index on a different table.

Upvotes: 7

Related Questions