EF 6 code first with X# (or C#) does not create a database

Question

I am struggling with EF 6 (for .NET 4.x), code first and SQLite.

I am using X# instead of C# but it does only affect the syntax.

The problem is that the SQLite database with the table is not created so SaveChanges() throws a "DbUpdateException" exception since the table does not exist (the db3 file will be created with 0 bytes):

Inner Exception 1: UpdateException: An error occurred while updating the entries. See the inner exception for details.

Inner Exception 2: SQLiteException: SQL logic error
no such table: Blogs

The logging output only revealed that there is no table __MigrationHistory but this should not matter.

Here is the complete code:

USING System
USING System.Collections.Generic
USING System.Linq
USING System.Data.Entity

PUBLIC CLASS CustomIntializer INHERIT CreateDatabaseIfNotExists<BloggingContext>
    
    PROTECTED OVERRIDE METHOD Seed(context AS BloggingContext) AS VOID
        VAR Blogs := List<Blog>{} {;
            Blog {} {Name := "Blog 1"},;
            Blog {} {Name := "Blog 2"};
        }
        Blogs:ForEach({b => context:Blogs:Add(b)})
        context:SaveChanges()
    END METHOD
END CLASS

PUBLIC CLASS Blog
    PUBLIC PROPERTY BlogId AS INT AUTO
    PUBLIC PROPERTY Name AS STRING AUTO
    PUBLIC VIRTUAL PROPERTY Posts AS List<Post> AUTO
END CLASS

PUBLIC CLASS Post
    PUBLIC PROPERTY PostId AS INT AUTO
    PUBLIC PROPERTY Title AS STRING AUTO
    PUBLIC PROPERTY Content AS STRING AUTO
    PUBLIC PROPERTY BlogId AS INT AUTO
    PUBLIC PROPERTY Blog AS Blog AUTO
END CLASS

PUBLIC CLASS BloggingContext INHERIT DbContext
    PUBLIC PROPERTY Blogs AS DbSet<Blog> AUTO
    PUBLIC PROPERTY Posts AS DbSet<Post> AUTO
    
    CONSTRUCTOR()
        SUPER("DataContext")
        SELF:Database:Log := Console.WriteLine

END CLASS

FUNCTION Start() AS VOID STRICT
    Database.SetInitializer<BloggingContext>(CustomIntializer{})
    BEGIN USING VAR db := BloggingContext{}
        db:database:Initialize(force := TRUE)
        db:Blogs.Add(Blog{} {Name := "Blog 1"})
        db:SaveChanges()
    END USING
    Console.WriteLine("*** Database updated ***")
    Console.ReadLine()

And the app.config:

<configuration>
    <configSections>
        <section name="entityFramework" 
                 type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 
                 requirePermission="false" />
    </configSections>
    <entityFramework>
        <defaultConnectionFactory
            type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
        </defaultConnectionFactory>
        <providers>
            <provider invariantName="System.Data.SQLite" 
                      type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
        </providers>
    </entityFramework>
    <system.data>
        <DbProviderFactories>
            <remove invariant="System.Data.SQLite.EF6" />
            <add name="SQLite Data Provider (Entity Framework 6)"
                 invariant="System.Data.SQLite.EF6"
                 description=".NET Framework Data Provider for SQLite (Entity Framework 6)"
                 type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
            <remove invariant="System.Data.SQLite" />
            <add name="SQLite Data Provider"
                 invariant="System.Data.SQLite"
                 description=".NET Framework Data Provider for SQLite"
                 type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
        </DbProviderFactories>
    </system.data>
    <connectionStrings>
        <add name="DataContext"
             connectionString="Data Source=pemo.db3;Version=3"
             providerName="System.Data.SQLite.EF6" />
    </connectionStrings>
</configuration>

What am I missing, or does code-first with SQLite not work with EF 6?

Answer 1

The solution is to create the tables directly through SQL statements in the overriden OnModelCreating() method like so:

PROTECTED OVERRIDE METHOD OnModelCreating(modelBuilder As DbModelBuilder) As Void
    SELF:CreateTables()
    SELF:OnModelCreating(modelBuilder.Entity<TitleInfo>())
    SELF:OnModelCreating(modelBuilder.Entity<ArtistInfo>())
    SUPER.OnModelCreating(modelBuilder)
    RETURN

The CreateTables() method does all the work:

/// <summary>
/// Create the tables of the database
/// </summary>
METHOD CreateTables() AS VOID
    VAR sqlText := "CREATE TABLE IF NOT EXISTS Titles (Id INTEGER PRIMARY KEY AUTOINCREMENT, Title TEXT NOT NULL, "
    sqlText += "ArtistID TEXT, Year INTEGER, Album TEXT, Length REAL, Rating INTEGER, Disco INTEGER,"
    sqlTExt += "AlbumCover BLOB, Remark TEXT);"
    VAR connectionString := Database:Connection:ConnectionString
    BEGIN USING VAR dbConnection := SQLiteConnection{connectionString}
        dbConnection:Open()
        BEGIN USING VAR dbCommand := dbConnection:CreateCommand()
            dbCommand:CommandText := sqlText
            dbCommand:ExecuteNonQuery()
        END USING
        sqlText := "CREATE TABLE IF NOT EXISTS Artists (Id TEXT PRIMARY KEY NOT NULL, Name TEXT NOT NULL);"
        BEGIN USING VAR dbCommand := dbConnection:CreateCommand()
            dbCommand:CommandText := sqlText
            dbCommand:ExecuteNonQuery()
        END USING
    END USING
    END METHOD

Another important point is not to create the database file by calling the SetInitializer() method:

// Don't create the database automatically
METHOD SetInitializeNoCreate() AS VOID
    Database:SetInitializer<MusicDbContext>(NULL)
END METHOD

Just for sake of completeness the two methods for setting the constraints for each of the two tables are:

// Define constraints for the Titles table
METHOD OnModelCreating(Titles AS EntityTypeConfiguration<TitleInfo>) AS VOID
    Titles:ToTable(NAMEOF(MusicDbContext:Titles)).HasKey({Title => Title.Id})
    Titles:Property({Title => Title:Title}):IsRequired()
    Titles:Property({Title => Title:ArtistId}):IsRequired()
    RETURN

// Define constraints for the Artists table
METHOD OnModelCreating(Artists AS EntityTypeConfiguration<ArtistInfo>) AS VOID
    Artists:ToTable(NAMEOF(MusicDbContext:Artists)).HasKey({Artist => Artist.Id})
    Artists:Property({Artist => Artist:Name}):IsRequired()
    RETURN

And the base class constructor should not be called with a named argument but without one:

Constructor()
    // Call the base class constructor with the connection string name
    // Don't use the name= for SQLite
    Super("80MusicCon") 

With that the db3 database file as stated in the connection string was created:

<connectionStrings>
  <add  
      name="80MusicCon"  
      connectionString="Data Source=./output/80musics.db3"  
      providerName="System.Data.SQLite" 
  />
</connectionStrings>

A migration was not necessary.

Although the recommend way is to use Nuget packages of course, the needed assemblies in the output directory are:

• EntityFramework.dll
• System.Data.Sqlite.dll
• System.Data.SQLite.EF6.dll
• SQLite.Interop.dll