Support use of catalogues for attached database in SQLite

[bairog]

EF Core introduced Entity splitting - the ability to map a single entity type to multiple tables. SQLite database has ATTACH DATABASE statement - the ability to attach an additional database to the current database connection (it will be reachable by database alias defined in statement). I use latest Microsoft.EntityFrameworkCore.Sqlite 7.0.2 nuget and I try to map some properties to a table in main database and other properties to a table with same name in attached database. My simple test entity:

public class StudyYear
{
     [Required]
     public Int64 Id { get; set; }        
     [Required]
     public Int32 Year { get; set; }
     public String Description { get; set; }
}

My DBContext:

public class MyDBContext : DbContext
{
        public MyDBContext()
        { }

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

        public DbSet<StudyYear> StudyYears { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                var connectionString = (new SqliteConnectionStringBuilder() { DataSource = $"D:\\test.db" }).ToString();
                optionsBuilder.UseSqlite(connectionString);
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //Entity splitting (StudyYear.Description is mapped to StudyYears table in test_text database)
            modelBuilder.Entity<StudyYear>(
                entityBuilder =>
                {
                    entityBuilder
                        .ToTable("StudyYears")
                        .SplitToTable(
                            "StudyYears",
                            "test_text",
                            tableBuilder =>
                            {
                                tableBuilder.Property(se => se.Description);
                            });
                });
        }
}

As far as I can see Microsoft SqliteConnectionStringBuilder class has no special property for attached databases. BTW Devart.Data.SQLite.SQLiteConnectionStringBuilder class has Attach property for attached databases, but Devart SQLite provider lacks EF Core 7 support for the moment(( So with Microsoft provider I need to do attach database by calling SQL script:

using (var contextSQLite= new MyDBContext())
{
     //migrate database if needed
     var migrations = contextSQLite.Database.GetPendingMigrations();
     if (migrations.Any())
     {
         var migrator = contextSQLite.Database.GetService<IMigrator>();
         foreach (var migration in migrations)
            migrator.Migrate(migration);
     }

     //attach database by calling SQL script         
     using (var connection = contextSQLite.Database.GetDbConnection())
     using (var command = connection.CreateCommand())
     {
         command.CommandText = $"ATTACH DATABASE 'D:\\test_text.db' AS test_text";
         command.ExecuteNonQuery();
      }

      //adding some data
      if (!contextSQLite.StudyYears.Any())
      {
          var add = new StudyYear() { Year = 2022, Description = "Год 2022" };
          contextSQLite.StudyYears.Add(add);
          contextSQLite.SaveChanges();
      }
}

The code above fails on migrator.Migrate(migration) line with

Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 1: 'table "StudyYears" already exists'.'

because OnModelCreating is called before attach SQL script is called (and therefore test_text schema name is not existing yet).

So howto use Entity splitting to map some properties to main SQLite database and some other to attached SQLite database? Maybe SqliteConnectionStringBuilder class has come property for SQL script that should be executed before OnModelCreating?

Provider and version information

EF Core version: EF Core 7.0 Database provider: Microsoft.EntityFrameworkCore.Sqlite 7.0.2 Target framework: .NET 7.0 Operating system: Windows 11 Pro 21H2 x64 IDE: Visual Studio 2022 17.4.4

[ajcvickers]

Attached database support is covered by #23587.

[bairog]

Attached database support is covered by #23587.

That issue is in a backlog for more than 2 years and it's unclear for me whether ATTACH is working somehow in underlying Microsoft.Data.Sqlite library or not. Regarding my issue is there a workaround or that is not possible at all with Microsoft.EntityFrameworkCore.Sqlite for the moment?

[ajcvickers]

@bairog I don't think it is possible to work with SQLite attached databases using EF Core with Microsoft.Data.Sqlite.

[bairog]

@ajcvickers Could you elaborate: is that a limitation of EF Core 7.0 itself or Microsoft.Data.Sqlite provider for the moment is not capable to work with SQLite attached databases via EF Core? If latter - is it planned to implement and in what estimated time? Asking because Devart.Data.SQLite.EF6 provider (via classic EntityFramework 6.4.4) is working with no problem is such scenario. But now I need a cross-platform version - so I'm trying to migrate..

[ajcvickers]

@bairog It's likely a combination of both--issues at the M.D.Sqlite level because its never really been tested/used, and no way to map catalogs in EF Core, as well as probably other things that need doing. We have no plans to work on this in the near future.

[bairog]

@bairog It's likely a combination of both--issues at the M.D.Sqlite level because its never really been tested/used, and no way to map catalogs in EF Core, as well as probably other things that need doing. We have no plans to work on this in the near future.

Devart.Data.SQLite.EFCore provider (EF Core 6) is working correctly with the following setup:

public DbSet<UserRole> UserRoles { get; set; }
public DbSet<User> Users { get; set; }
public DbSet<Post> Posts { get; set; }
private SQLiteConnectionStringBuilder connectionStringBuilder;

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
     if (!optionsBuilder.IsConfigured)
     {           
          connectionStringBuilder = new SQLiteConnectionStringBuilder() 
          { 
               DataSource = "d:\\test.db", 
               Attach = "d:\\test2.db;d:\\test3.db",  
               FailIfMissing = true
          };
          optionsBuilder.UseSQLite(connectionStringBuilder.ConnectionString);
     }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
     var attach = connectionStringBuilder.Attach.Split(';');
     var secondDbSchemaName = Path.GetFileNameWithoutExtension(attach[0]);
     var thirdDbSchemaName = Path.GetFileNameWithoutExtension(attach[1]);

     modelBuilder.Entity<UserRole>().ToTable("UserRole");
     modelBuilder.Entity<User>().ToTable("User", secondDbSchemaName);
     modelBuilder.Entity<Post>().ToTable("Post", thirdDbSchemaName);
}

That provider is not supporting EF Core 7 for the moment (they are working on it) so I cannot confirm that Entity splitting is working as well. But as you can see mapping whole entites to attached databases is "just working". So it is definitelly not the problem of EF Core itself - it just not implemented in Microsoft.EntityFrameworkCore.Sqlite provider. Also a long-running bug when using it via C++\CLI wrapper exists for almost 2 years and it looks like noone is interested in fixing it. It's very disappointing that SQLite provider supported by Microsoft doesn't even try to have the same features as its commercial competitor.

UPDATE: Devart team promised that Entity Splitting works in their internal build with EF Core 7 support and they are planning to release the public version in 2 weeks. So it is evident that Entity splitting is possible to implement in EF Core 7.

[bairog]

@ajcvickers Devart.Data.SQLite.EFCore 6.1.134.7 with EF Core 7.0 support was published yesterday. I can now confirm that my sample project with Entity splitting is working correctly with that provider.

[ajcvickers]

@bairog I don't think anyone is saying there is no way to implement this, only that it hasn't been implemented.