Efcore: [In Memory SQLite] "EnsureCreated" Call Fails When Using Indexes With the Same Name on Different Tables

[conor-joplin]

I was following the guide on testing with SQLite when I ran into some exceptions. Indexes with the same name, but on different tables, fail when calling "EnsureCreated" while using an in memory SQLite database with an error claiming that the index already exists.

I only make the call to "EnsureCreated" in unit test code as I'm using an existing SQL Server database in my production code, and I was able to come up with a workaround that is suitable for this purpose. I've included it in my repro code in case it could be helpful to anyone else.

Exception Information

Exception message: Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'index Index_AccountId already exists'..
Stack trace:

   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.<PrepareAndEnumerateStatements>d__62.MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.CreateTables()
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreated()
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureCreated()

Steps to reproduce

```c#
using System;
using System.Linq;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace SqliteDatabaseCreationFailure
{
class Program
{
static void Main(string[] args)
{
var options = new DbContextOptionsBuilder()
.UseSqlite(new SqliteConnection("DataSource=:memory:"))
.Options;

        RunExampleSqliteFailue(options);


        RunExampleWorkaround(options);
    }

    private static void RunExampleSqliteFailue(DbContextOptions<ExampleDbContext> options)
    {
        using (var context = new ExampleDbContext(options))
        {
            context.Database.OpenConnection();

            try
            {
                context.Database.EnsureCreated();

                throw new Exception("Failure was expected");
            }
            catch (SqliteException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                context.Database.CloseConnection();
            }
        }
    }

    private static void RunExampleWorkaround(DbContextOptions<ExampleDbContext> options)
    {
        // Example of discovered workaround that succeeds
        using (var context = new WorkaroundExampleDbContext(options))
        {
            context.Database.OpenConnection();
            context.Database.EnsureCreated();

            context.Foos.Add(new Foo { FooId = Guid.NewGuid(), AccountId = Guid.NewGuid() });
            context.Bars.Add(new Bar { BarId = Guid.NewGuid(), AccountId = Guid.NewGuid() });

            context.SaveChanges();
            context.Database.CloseConnection();

            Console.WriteLine("Workaround succeeded in creating the in memory SQLite database.");
        }
    }
}

public partial class Foo
{
    public Guid FooId { get; set; }
    public Guid AccountId { get; set; }
}

public partial class Bar
{
    public Guid BarId { get; set; }
    public Guid AccountId { get; set; }
}

public partial class ExampleDbContext : DbContext
{
    public virtual DbSet<Foo> Foos { get; set; }
    public virtual DbSet<Bar> Bars { get; set; }

    public ExampleDbContext(DbContextOptions<ExampleDbContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Follows entity builder pattern as generated by Scaffold-DbContext
        modelBuilder.Entity<Foo>(entity =>
        {
            entity.HasKey(e => e.FooId);

            entity.HasIndex(e => e.AccountId)
                .HasName("Index_AccountId");
        });

        modelBuilder.Entity<Bar>(entity =>
        {
            entity.HasKey(e => e.BarId);

            entity.HasIndex(e => e.AccountId)
                .HasName("Index_AccountId");
        });
    }
}

public partial class WorkaroundExampleDbContext : ExampleDbContext
{
    internal WorkaroundExampleDbContext(DbContextOptions<ExampleDbContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        foreach (var entityType in modelBuilder.Model.GetEntityTypes())
        {
            RemoveIndexes(entityType);
        }
    }

    private void RemoveIndexes(IMutableEntityType entityType)
    {
        foreach (var index in entityType.GetIndexes().ToList())
        {
            entityType.RemoveIndex(index.Properties);
        }
    }
}

}
```

Further technical details

EF Core version: 2.1.0
Database Provider: Microsoft.EntityFrameworkCore.Sqlite
Operating system: Windows 10 Enterprise 1809
IDE: Visual Studio Enterprise 2017 15.9.4

[bricelam]

modelBuilder.Entity<Foo>(entity =>
{
    entity.HasIndex(e => e.AccountId)
        .HasName("Index_AccountId");
});

modelBuilder.Entity<Bar>(entity =>
{
    entity.HasIndex(e => e.AccountId)
        .HasName("Index_AccountId");
});

Index names (like table names) need to be unique within a database. Name them something like Index_Foo_AccountId and Index_Bar_AccountId.

[conor-joplin]

This is not the case for SQL Server; according to the documentation:

Index names must be unique within a table or view but do not have to be unique within a database.

Does SQLite require unique index names within a database? I had a hard time finding documentation on this issue. If so, can the documentation on testing with SQLite highlight this (and other) potential pitfalls of testing with in memory SQLite database while using DbContexts generated from a SQL Server database?

[bricelam]

AFAIK, SQL Server is the only database that allows this. SQLite and PostgerSQL treat them as schema-level objects not owned by the table.

[ajcvickers]

@conor-joplin Whenever a different database is used for testing it will likely have some behaviors that are different. Agreed that the docs should be updated since people seem to be surprised by this.