Entity Framework tables are not generated using in memory SQLite-CodePudding

We are trying to move to using an in-memory SQLite instance for our unit test automation, instead of SQL Server or SQL Express. We use Entity Framework Core.

I think I have everything configured correctly, but it's still failing, so I must be missing a step, but I'm not sure what it is.

In our test project's app.config, I've specified:

  <connectionStrings>
    <add name="BusinessDb" providerName="System.Data.SQLite.EF6" connectionString="data source=:memory:"/>
  </connectionStrings>

Our production concrete class is a bit more complex (it has many more modelBuilder calls in the OnModelCreating() method and many more DbSet objects, but it is basically like this:

namespace Business.Base.Concrete
{
    public class SqlBusinessDb 
        : DbContext
        , IBusinessDb        
    {
        public string ConnectionString { get; set; }

        public SqlBusinessDb(string connectionString)
        {
            ConnectionString = connectionString;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);

            if (ConnectionString.Contains("memory"))
            {
                optionsBuilder
                    .UseLazyLoadingProxies()
                    .UseSqlite(ConnectionString,
                        options =>
                            options.CommandTimeout(SqlSettings.s_CommandTimeoutInSec.CurrentValue)
                                .MigrationsHistoryTable("_BusinessDB_Migrations"))
                    .AddInterceptors(new Deals.Base.SQL.SqlPerfCounterInterceptor());
            }
            else
            {
                optionsBuilder
                    .UseLazyLoadingProxies()
                    .UseSqlServer(ConnectionString,
                        options =>
                            options.CommandTimeout(SqlSettings.s_CommandTimeoutInSec.CurrentValue)
                                .MigrationsHistoryTable("_BusinessDB_Migrations")
                                .EnableRetryOnFailure())
                    .AddInterceptors(new Deals.Base.SQL.SqlPerfCounterInterceptor());
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Has<BillingPlan>()
                .HasManyToOne(p => p.Companies, a => a.BillingPlan, a => a.BillingPlan_Id)
         }

        public int ExecuteStoreCommand(string commandText, params object[] parameters)
        {
            return Database.ExecuteSqlRaw(commandText, parameters);
        }

        public DbSet<Features.FeatureOverride_Plan> FeaturesPlan { get; set; }
        public DbSet<Business> Businesses { get; set; }
    }
}

In our test project we call it like so:

public static TestBusinessDb GetInstance()
{
    SqlBusinessDb realRepository = new SqlBusinessDb();
    if (!_hasBeenMigrated)
    {
        _hasBeenMigrated = true;
        DatabaseFacade dbf = realRepository.Database;

        var issqlite = dbf.IsSqlite();
        var tables = dbf.ExecuteSqlRaw("SELECT * FROM information_schema.tables;");

        // for the Test Repository, we migrate once when we first try and connect.
        realRepository.Database.Migrate();
    }
}

This code fails on the "dbf.ExecuteSqlRaw()" line with:

Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'no such table: information_schema.tables'.

If I remove that line, it fails on: realRepository.Database.Migrate(); with

Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'no such table: _BusinessDB_Migrations'.

When debugging it successfully ran the OnConfiguring and OnModelCreating methods and I watched it execute a SQL command that created that table. dbf.ProviderName returns "Microsoft.EntityFrameworkCore.Sqlite". So, why aren't the tables being found? Is there something else that needs to be in place that I'm missing?

CodePudding user response：

It turns out that SQLite is unable to handle migrations anyway, so it is not a viable option.