Using SQLite in-memory with EF Core - context.Database.EnsureCreated keeps throwing 'AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY'

Question

I am currently trying to write unit tests for a project in C# that relies heavily on Entity Framework Core. In order to do that I have decided to use a SQLite in-memory database, and taking inspiration from the example that can be found here, I have written the following class :

public class SqliteEFCoreContextInitializer<T> : IDisposable where T : DbContext
{
    private SQLiteConnection connection;
    private DbContextOptions<T> contextOptions;
    private Func<DbContextOptions<T>, T> contextInstantationFunction;

    // The parameter _contextInstantationFunction is there solely to get around the impossibility to directly call the context constructor from the generic type T
    public SqliteEFCoreContextInitializer(Func<DbContextOptions<T>,T> _contextInstantationFunction)
    {
        contextInstantationFunction = _contextInstantationFunction;
        connection = new SQLiteConnection("Data Source=:memory:");

        connection.Open();

        contextOptions = new DbContextOptionsBuilder<T>().UseSqlite(connection).Options;
        using T context = CreateContext();
        context.Database.EnsureCreated();
    }

    public void Dispose()
    {
        connection.Dispose();
    }

    public T CreateContext()
    {
        return contextInstantationFunction(contextOptions);
    }
}

This class is meant to be instantiated and then used like this :

// Instantiation
SqliteEFCoreContextInitializer<MyContext> sqliteContextInitializer = new(options => new MyContext(options));

//Getting the SQLite in-memory DbContext
MyContext context = sqliteContextInitializer.CreateContext();

The problem I have here is that, regardless of my database schema, I seem to always get the error

SQL logic error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

when executing context.Database.EnsureCreated();. After some experiments I have found out that this error is thrown even when my schema only includes a single table with no columns besides the integer primary key! What exactly am I doing wrong here?

Also, I don't know whether or not this is relevant but on this project I am forced to work with the now outdated EF Core 5.0.

Answer 1

After some more experiment I figured out that the error was caused by my primary key data types being set to NUMBER(10) when Sqlite requires them to be INTEGER. Changing the data types solved the problem. I must admit it didn't occur to me at first that the two data types would be treated in different enough ways to create this error (they're both meant to represent integers after all) but I now realize that I was being very naive.