msallin/sqlitecodefirst

Creates a SQLite Database based on a EdmModel by using Entity Framework CodeFirst.

SQLite CodeFirst

Release Build

CI Build

Creates a SQLite Database from Code, using Entity Framework CodeFirst.

Support the project

To support this project you can: star the repository, report bugs/request features by creating new issues, write code and create PRs or donate. Especially if you use it for a commercial project, a donation is welcome. If you need a specific feature for a commercial project, I am glad to offer a paid implementation.

Project Status

This project was started when there was .NET Full Framework and EF 6. EF 6 does not offer code first for SQLite and this library fills this gab. Nowadays there is .NET Core (or now just called .NET) and EF Core. EF Core supports code first and migrations for SQLite. If you use .NET Core 3 or above together with EF Core, there is no need for this library. If you use EF 6 (either with .NET Full Framework or with .NET Core 3 or above), this library is an option for you to get code first for SQLite. I'm going to maintain this library as long as it is useful for some people (see History & Project Status and Release Schedule).

Features

This project ships several IDbInitializer classes. These create new SQLite Databases based on your model/code.

The following features are supported:

• Tables from classes (supported annotations: Table)
• Columns from properties (supported annotations: Column, Key, MaxLength, Required, NotMapped, DatabaseGenerated, Index)
• PrimaryKey constraint (Key annotation, key composites are supported)
• ForeignKey constraint (1-n relationships, support for 'Cascade on delete')
• Not Null constraint
• Auto increment (An int PrimaryKey will automatically be incremented and you can explicit set the "AUTOINCREMENT" constraint to a PrimaryKey using the Autoincrement-Attribute)
• Index (Decorate columns with the Index attribute. Indices are automatically created for foreign keys by default. To prevent this you can remove the convention ForeignKeyIndexConvention)
• Unique constraint (Decorate columns with the UniqueAttribute, which is part of this library)
• Collate constraint (Decorate columns with the CollateAttribute, which is part of this library. Use CollationFunction.Custom to specify a own collation function.)
• Default collation (pass an instance of Collation as constructor parameter for an initializer to specify a default collation).
• SQL default value (Decorate columns with the SqlDefaultValueAttribute, which is part of this library)

Install

Either get the assembly from the latest GitHub Release Page or install the NuGet-Package SQLite.CodeFirst (PM> Install-Package SQLite.CodeFirst).

The project is built to target .NET framework versions 4.0 and 4.5 and .NET Standard 2.1. You can use the SQLite CodeFirst in projects that target the following frameworks:

• .NET 4.0 (uses net40)
• .NET 4.5-4.8 (uses net45)
• .NET Core 3.0-3.1 (uses netstandard2.1)
• .NET 5-8 (uses netstandard2.1)

How to use

The functionality is exposed by using implementations of the IDbInitializer<> interface. Depending on your need, you can choose from the following initializers:

• SqliteCreateDatabaseIfNotExists
• SqliteDropCreateDatabaseAlways
• SqliteDropCreateDatabaseWhenModelChanges

If you want to have more control, you can use the SqliteDatabaseCreator (implements IDatabaseCreator) which lets you control the creation of the SQLite database. Or for even more control, use the SqliteSqlGenerator (implements ISqlGenerator), which lets you generate the SQL code based on your EdmModel.

When you want to let the Entity Framework create database if it does not exist, just set SqliteDropCreateDatabaseAlways<> or SqliteCreateDatabaseIfNotExists<> as your IDbInitializer<>.

Initializer Sample

public class MyDbContext : DbContext
{
    public MyDbContext()
        : base("ConnectionStringName") { }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<MyDbContext>(modelBuilder);
        Database.SetInitializer(sqliteConnectionInitializer);
    }
}

Notice that the SqliteDropCreateDatabaseWhenModelChanges<> initializer will create a additional table in your database. This table is used to store some information to detect model changes. If you want to use an own entity/table you have to implement the IHistory interface and pass the type of your entity as parameter to the constructor of the initializer.

In a more advanced scenario, you may want to populate some core- or test-data after the database was created. To do this, inherit from SqliteDropCreateDatabaseAlways<>, SqliteCreateDatabaseIfNotExists<> or SqliteDropCreateDatabaseWhenModelChanges<> and override the Seed(MyDbContext context) function. This function will be called in a transaction, once the database was created. This function is only executed if a new database was successfully created.

public class MyDbContextInitializer : SqliteDropCreateDatabaseAlways<MyDbContext>
{
    public MyDbContextInitializer(DbModelBuilder modelBuilder)
        : base(modelBuilder) { }

    protected override void Seed(MyDbContext context)
    {
        context.Set<Player>().Add(new Player());
    }
}

SqliteDatabaseCreator Sample

public class MyContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var model = modelBuilder.Build(Database.Connection);
        IDatabaseCreator sqliteDatabaseCreator = new SqliteDatabaseCreator();
        sqliteDatabaseCreator.Create(Database, model);
    }
}

SqliteSqlGenerator Sample

public class MyContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var model = modelBuilder.Build(Database.Connection);
        ISqlGenerator sqlGenerator = new SqliteSqlGenerator();
        string sql = sqlGenerator.Generate(model.StoreModel);
    }
}

.NET Core example

Add the following package references.

<PackageReference Include="System.Data.SQLite" Version="1.0.112.2" />
<PackageReference Include="System.Data.SQLite.EF6" Version="1.0.112.2" />

Add the following class.

public class MyConfiguration : DbConfiguration, IDbConnectionFactory {
    public MyConfiguration()
    {
        SetProviderFactory("System.Data.SQLite", SQLiteFactory.Instance);
        SetProviderFactory("System.Data.SQLite.EF6", SQLiteProviderFactory.Instance);

        var providerServices = (DbProviderServices)SQLiteProviderFactory.Instance.GetService(typeof(DbProviderServices));

        SetProviderServices("System.Data.SQLite", providerServices);
        SetProviderServices("System.Data.SQLite.EF6", providerServices);

        SetDefaultConnectionFactory(this);
    }

    public DbConnection CreateConnection(string connectionString)
        => new SQLiteConnection(connectionString);
    }
}

Also, make sure you specify the DbConfigurationType on the DBContext class as well

[DbConfigurationType(typeof(MyConfiguration))]
public class Context: DbContext {
    //... DBContext things
}

Structure

The code is written in an extensible way. The logic is divided into two main parts, Builder and Statement. The Builder knows how to translate the EdmModel into statements where a statement class creates the SQLite-DDL-Code. The structure of the statements is influenced by the SQLite Language Specification. You will find an extensive usage of the composite pattern.

Hints

If you try to reinstall the NuGet-Packages (e.g. if you want to downgrade to .NET 4.0), the app.config will be overwritten and you may getting an exception when you try to run the console project. In this case please check the following issue: #13

Recognition

I started with the code from flaub.

sqlitecodefirst's Issues

Multiple sqlite database files

Hi,

Lets assume I want to use a sqlite database file per user. i.e. user1.db and user2.db files, both with the same schema.
I have created a context with the following constructor

public FootballDbContext(string dbFilePath)
            : base(new SQLiteConnection { ConnectionString = new SQLiteConnectionStringBuilder { DataSource = dbFilePath, ForeignKeys = true, DefaultTimeout = 2 }.ConnectionString }, true)
{
    Configuration.ProxyCreationEnabled = true;
    Configuration.LazyLoadingEnabled = true;
}

This will allow me to pass the filePath to the constructor of the context.
The problem I'm facing is that both contexts are referring to the same file although they use different connection strings.

What should I do? Is there a workaround or is this a limitation of EF?

UNC paths crash Entity Framework

Some highlights in part of the error message ...
SQLite error (14): os_win.c:40135: (3) winOpen(C:\CADBLOKE-8560W\CodezOnD\tvCADtest\XXX.tvCADdb) - The system cannot find the path specified. SQLite error (14): cannot open file at line 40144 of [92dc59fd5a]

Note the C: at the start of the path? The actual path starts with \\CADBLOKE-8560W\CodezOnD\tvCADtest but something has re-rooted the path to C:\

I think it is due to the rather unique way SQLite does UNC paths as per: http://sqlite.1065341.n5.nabble.com/System-Data-SQLite-and-UNC-Paths-td72920.html
...more info at ...
http://stackoverflow.com/a/18506097/492 and note the comment
so with the escaping notation in c# you need to write 8 backslashes: \\\\\\\\network\\share\\file.db

I wish I knew where to look for that and fix i properly - this would be a pull-request instead of an issue if I was that clever.

I worked around it with ...

string uncString = Regex.Replace(DbPath, @"^\\\\(?!\\)", @"\\\\\\\\",
                        RegexOptions.IgnoreCase | RegexOptions.Singleline);

and used uncString for DbPath in the DbContext constructor, namely ...

public TvCadDbContext(string DbPath)
            : base(
                new SQLiteConnection
                {
                    ConnectionString =
                        new SQLiteConnectionStringBuilder {DataSource = DbPath, ForeignKeys = true}.ConnectionString
                }, contextOwnsConnection: true)
        { }

the TLDR; error message

System.Data.Entity.Core.EntityException was unhandled by user code
  HResult=-2146233087
  Message=The underlying provider failed on Open.
  Source=EntityFramework
  StackTrace:
       at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
       at System.Data.Entity.DbContextTransaction.EnsureOpenConnection()
       at System.Data.Entity.DbContextTransaction..ctor(EntityConnection connection)
       at System.Data.Entity.Database.BeginTransaction()
       at SQLite.CodeFirst.SqliteInitializerBase`1.InitializeDatabase(TContext context) in C:\Codez\tvCAD\z.libraries\sqlitecodefirst\sqlite.codefirst\DbInitializers\SqliteInitializerBase.cs:line 76
       at SQLite.CodeFirst.SqliteDropCreateDatabaseWhenModelChanges`1.InitializeDatabase(TContext context) in C:\Codez\tvCAD\z.libraries\sqlitecodefirst\sqlite.codefirst\DbInitializers\SqliteDropCreateDatabaseWhenModelChanges.cs:line 82
       at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)
  InnerException: 
       ErrorCode=14
       HResult=-2147467259
       Message=unable to open database file
       Source=System.Data.SQLite
       StackTrace:
            at System.Data.SQLite.SQLite3.Open(String strFilename, String vfsName, SQLiteConnectionFlags connectionFlags, SQLiteOpenFlagsEnum openFlags, Int32 maxPoolSize, Boolean usePool)
            at System.Data.SQLite.SQLiteConnection.Open()
            at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
            at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext)
            at System.Data.Entity.Core.EntityClient.EntityConnection.Open()

Thanks again for creating and maintaining this project

[Question] DotNetCore Support

Any plans on DotNetCore support?

Model Creation Bug When Using an Entity with a Name Less than 4 Characters

I get the following error because I have an entity called "Set" (like a music set) in my model.

StartIndex cannot be less than zero. Parameter name: startIndex at System.String.Remove(Int32 startIndex, Int32 count) at SQLite.CodeFirst.Utility.SqliteAssociationType.IsSelfReferencing(AssociationType associationType) at SQLite.CodeFirst.Utility.SqliteAssociationType..ctor(AssociationType associationType, EntityContainer container) ...

This code throws the exception. I would check the ToRole.Name length first. If less than 4, return false;

private const string SelfReferencingPostfix = "Self";
private static bool IsSelfReferencing(AssociationType associationType)
{
            return associationType.Constraint.ToRole.Name.Remove(associationType.Constraint.ToRole.Name.Length - SelfReferencingPostfix.Length, SelfReferencingPostfix.Length) == associationType.Constraint.FromRole.Name;
}

Maybe change to:

private static bool IsSelfReferencing(AssociationType associationType)
{
    var toRoleName = associationType.Constraint.ToRole.Name;
    if (toRoleName.Length < 4)
        return false;

    return toRoleName.Remove(toRoleName.Length - SelfReferencingPostfix.Length, SelfReferencingPostfix.Length) == associationType.Constraint.FromRole.Name;
}

Demo Project Doesn't Run

I downloaded your master branch and tried to run the console demo. Just wanted to note I had to make a few changes to make it run.

1. I had to remove the sqlite nuget packages and re-add them (not sure why ?) It was crashing otherwise during DbContext constructor.

2. In the App.config I had to modify my <system.data> section to contain both Sqlite.EF6 and SQLite references like so:

  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite.EF6" />
      <remove invariant="System.Data.SQLite" />
      <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" />
      <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>

Without this modification it was crashing during the console demo when displaying db contents. The above modification is documented on the SQLite site.

Unable to create two child tables from a single parent table with one-to-many relation on both

Here's the error:

Test 'WhenPersistingSQLite' failed: System.Data.SQLite.SQLiteException : SQL logic error or missing database
index IX_Branch_ID already exists
    at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
    at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
    at System.Data.SQLite.SQLiteDataReader.NextResult()
etc.

And here's the code to reproduce (uses F# to declare types, NUnit to drive test):

open System.Collections.Generic
open System.Linq

type Leaf() =
   member val ID = -1 with get,set

type Bud() =
   member val ID = -1 with get,set

type Branch() =
   member val ID = -1 with get,set
   member val Leaves = List(Enumerable.Empty<Leaf>()) with get,set
   member val Buds = List(Enumerable.Empty<Bud>()) with get,set

=======
   [TestFixture]
   public class Tests
   {
      [Test]
      public void WhenPersistingSQLite()
      {
         using (var dbContext = new TreeSqliteDBContext())
            dbContext.TreeDbSet.Add(new Branch());
      }
   }

   public class TreeSqliteDBContext : DbContext
   {
      public DbSet<Branch> TreeDbSet { get; set; }

      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
         var connString = Database.Connection.ConnectionString;
         Database.SetInitializer(new SqliteDropCreateDatabaseAlways<TreeSqliteDBContext>(
            connString, modelBuilder));
      }
   }

Incorrect Self-Referencing FK created in special case entity names

This is a weird one in version 1.2.3.15. If you have an entity that begins with the plural of another entity, and it has a navigation property to that other entity, the FK is created incorrectly. Try the following:

public class Foo
{
    public int FooId { get; set; }
    public string Name { get; set; }

    private ICollection<FooStep> _FooSteps;
    public virtual ICollection<FooStep> FooSteps
    {
        get { return _FooSteps ?? (_FooSteps = new HashSet<FooStep>()); }
        set { _FooSteps = value; }
    }
}

public class FooStep
{
    public int FooStepId { get; set; }
    public int FooId { get; set; }
    public int Number { get; set; }
    public virtual Foo Foo { get; set; }
}

When the database is generated, instead of a FK to FooId being created in table FooSteps, it's created on the Foos table instead (so it ends up with a self-referenced FK). Weird!

Note: I did not encounter this behavior in 1.2.2.14. It looks like the patch to support small entity names introduced this new bug.

If you change FooStep to anything else that doesn't have an "S" after Foo, then it generates fine. This generates the FK as expected on the FoobSteps table:

public class FoobStep
{
    public int FoobStepId { get; set; }
    public int FooId { get; set; }
    public int Number { get; set; }
    public virtual Foo Foo { get; set; }
}

NuGet package incorrect net45

Good stuff, but I have to recompile your source code for .net 4.5.
It seems both net45 and net452 in your package are targeting .net 4.5.2.
Hopefully you can fix this at your earliest convenience.

Thanks so much.

Composite indexes support

Hei,

I recently changed from using sqlServer to Sqlite, and just found that composite index generation has stopped working.

Example:
I have a class where I want the combination of name and a foreign object to be unique but the index is not being generated in the DB:

public class foo {
        public int? Id { get; set; }

        [Index("IX_Name_OtherObject", 1, IsUnique = true)]
        public string Name { get; set; }

        [Index("IX_Name_OtherObject", 1, IsUnique = true)]
        public OtherObject otherObject { get; set; }
}

Thanks for your great work with bringing code first to SQLite 👍

Possibility to detect an empty database file and run code first

See rbrus@3e1df74

FKs for self-referencing tables not created

Foreign keys for self-referencing tables are not created.

For example, have a look at this entity:

public class EventInfo
{
    public int Id { get; set; }
    public virtual ICollection<EventInfo> ChildEventInfos { get; set; }
    public int? ParentEventInfoId { get; set; }
    public virtual EventInfo ParentEventInfo { get; set; }
}

and this configuration of it:

modelBuilder.Entity<EventInfo>()
    .HasOptional(x => x.ParentEventInfo)
    .WithMany(x => x.ChildEventInfos)
    .HasForeignKey(x => x.ParentEventInfoId);

Created table lacks FK ParentEventInfoId REFERENCES EventInfo (Id).

The problem seems to be in CreateDatabaseStatementBuilder.GetCreateTableStatements, where respective association type is filtered out since its Constraint.RoleTo.Name is "EventInfoSelf", not "EventInfo".

Project does not compile

Hi,

When getting latest version the code does not compile due to what I believe was a rename of SqliteDropCreateDatabaseAlwaysBaseInitializer to SqliteDropCreateDatabaseAlways.

Feature: DropCreateDatabaseAlways

I would like to include the ability to delete the database each time. Certainly a good feature during development!

Please support unique attribute

Can you please support unique attribute on properties?

Generates wrong foreign key constraint

Hello,

for 2 classes below the SqliteCreateDatabaseIfNotExists initializer creates wrong foreign key constraint (ignores table name in TableAttribute):

CREATE TABLE Routes (
Id INT NOT NULL,
Name NVARCHAR NOT NULL,
Number NVARCHAR NOT NULL,
TransportId INT NOT NULL,
PRIMARY KEY (
Id
),
FOREIGN KEY (
TransportId
)
REFERENCES Transport (Id) ON DELETE CASCADE
);

---

[Table("Transports")]
public class Transport
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Key]
    public int Id
    {
        get;
        set;
    }

    [Required]
    public string Name
    {
        get;
        set;
    }

    [InverseProperty("Transport")]
    public virtual ICollection<Route> Routes
    {
        get;
        set;
    }
}

[Table("Routes")]
public class Route
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Key]
    public int Id
    {
        get;
        set;
    }

    [Required]
    public string Name
    {
        get;
        set;
    }

    public int TransportId
    {
        get;
        set;
    }

    [ForeignKey("TransportId")]
    public virtual Transport Transport
    {
        get;
        set;
    }
}

---

Thanks,
Anton Ryzhov

.Net 4 does not work.

First of all great project, This is very much needed to SQLite-EF users!
As mentioned in other issues when downgrading to .Net 4 the code breaks due to App.config changes.
However, using the same App.config from here will not work either.
After a few trials and errors I have managed to make it work.
Below is the final app.config that worked for me.
Can you add steps to overcome this issue in the Readme.md file so that other may use it as well.
(I think the only difference is in the connections string)

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="footballDb" connectionString="data source=.\footballDb.sqlite" providerName="System.Data.SQLite.EF6" />
  </connectionStrings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0" />
  </startup>
  <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" />
    </DbProviderFactories>
  </system.data>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>
</configuration>

Thanks,
Harel M.

Timespan properties

I want to use Timespan properties with EF6 without mapping them to int64, as with SqlServer DB, but get There is no store type corresponding to the EDM type 'Edm.Time' of primitive type 'Time'. Found this SO question about problem.

Any solutions?

CreateDrop database multiple times

Hi,
I want to use SQLiteCodefirst to create my unist test database to test my EF6 domain.
Problem is that OnModelCreating is only called once per app domain, and you cant disable model caching.

Is there a way to use SQLLite code first for setting up the database for each test?

Feature suggestion: Index Support

https://msdn.microsoft.com/en-us/data/jj591583.aspx#Index should be supported.

PluralizingTableNameConvention

PluralizingTableNameConvention is not applied on table creation.
Table names are always same as entity name which generally singular(not plural).

Additional annotations

MinLength, NotMapped, Timestamp and DatabaseGenerated would be nice.

Feature suggestion : Add support for Optimistic Concurrency Token / Row Version

It would be nice to be able to map a Byte[] property that is marked as Row version in the model into the DB.

This should probably be mapped

• to an integer column, with a 0 or 1 default value
• with a Trigger to update it on update... Something along the line:
  CREATE TRIGGER UpdateMyObjectRowVersion AFTER UPDATE ON MyObject FOR EACH ROW WHEN NEW.RowVersion = OLD.RowVersion
  BEGIN
  UPDATE MyObject SET RowVersion = OLD.RowVersion + 1 WHERE Id = OLD.Id;
  END;

Feature Suggestion: Provide signed NuGet package

Excellent package but would be a great improvement if a signed NuGet package could be provided to allow the package to be used without modification in solutions that require strong named assemblies.

Feature suggestion: SqliteDropCreateDatabaseIfChanged

There are times when you want to know the database had changed only to delete it in order to create a new one with the new tables scheme.
I would like to use this sqlite database as some kind of cache - If all tables and columns are exactly as the last time I used this database I want to keep using it, however if the scheme had changed since last usage I want to delete the database as It does no longer reflect the code that is currently running.

Thoughts are welcomed :-)
Thanks,
Harel M.

Creating DB for ASP.NET Identity 2 failed

I had tried with MSSQL and MySql, both OK.
` public class ApplicationDbContext : IdentityDbContext
{
public ApplicationDbContext()
: base("DefaultConnection", throwIfV1Schema: false)
{
}

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var sqliteConnectionInitializer = new SqliteDropCreateDatabaseWhenModelChanges<ApplicationDbContext>(modelBuilder);
        Database.SetInitializer(sqliteConnectionInitializer);
    }
}`

`System.Data.Entity.ModelConfiguration.ModelValidationException was unhandled
HResult=-2146233088
Message=One or more validation errors were detected during model generation:

Fonlow.TraceHub.Security.IdentityUserRole: : EntityType 'IdentityUserRole' has no key defined. Define the key for this EntityType.
Fonlow.TraceHub.Security.IdentityUserLogin: : EntityType 'IdentityUserLogin' has no key defined. Define the key for this EntityType.
IdentityUserRoles: EntityType: EntitySet 'IdentityUserRoles' is based on type 'IdentityUserRole' that has no keys defined.
IdentityUserLogins: EntityType: EntitySet 'IdentityUserLogins' is based on type 'IdentityUserLogin' that has no keys defined.

Source=EntityFramework
StackTrace:
at System.Data.Entity.Core.Metadata.Edm.EdmModel.Validate()
at System.Data.Entity.DbModelBuilder.Build(DbProviderManifest providerManifest, DbProviderInfo providerInfo)
at System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection)
at System.Data.Entity.Internal.LazyInternalContext.CreateModel(LazyInternalContext internalContext)
at System.Data.Entity.Internal.RetryLazy2.GetValue(TInput input) at System.Data.Entity.Internal.LazyInternalContext.InitializeContext() at System.Data.Entity.Database.Initialize(Boolean force) at AuthDbCreator.Program.Main(String[] args) in c:\VsProjects\FonlowWebLogging\AuthDbCreator\Program.cs:line 23 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException:

I guess that Identity models has multiple inheritances with generic, particularly for the Key type, and currently implementation of SqliteCodeFirst could not pick this up.

IndexAttribute for ForeignKeyColumns

The name of an index for foreign key column is always the default. Can you please provide the possibility that the index-name of a foreign will be used, when it's defined?

Ignoring InvariantCultureIgnoreCase

The following code does work correct when Name = "a" and otherName = "A":

dbContext.Table.FirstOrDefault(x => x.Name.Equals(otherName, StringComparison.CurrentCultureIgnoreCase));

I have found this when looking in the google:
http://stackoverflow.com/questions/5080727/string-equals-not-working-as-intended
I'm guessing that one should build the string columns with NOCASE by default and have an attribute to change this behavior?
Thought are welcomed.

Annotation tablename

It would be nice if the tablebuilder recognizes the table-annotation!

Fix the release Buildjob

The release build is broken.
GitHub release publishing is not working correct because of conflicting file names.

The release build should create an ZIP-Archive artifact (which will be pushed to the GitRelease).
-\Assemblies.zip
--\net40
---\SQLite.CodeFirst.dll
--\net45
---\SQLite.CodeFirst.dll

The SQLite.CodeFirst.dll's must be the builded with the release configuration.
In fact to only thing to do is: Zip content of the "Release" as "Assemblies.zip".

Can't make it work with In-Memory database

Hi, first thank you sir for your effort.
but here is my situation :
1- I couldn't make it work with in-memory database ( to unit test my Entity Framework logic ) !!
pseudo code :

    var myInMemoryDbConnection = GetInMemoryConnection();
    var dbContext1 = new MyDbContext(myInMemoryDbConnection, ownsConnection: true);
    // do some work with dbContext1
    var dbContext2 = new MyDbContext(myInMemoryDbConnection, ownnsConnection: false);
    // use dbContext2 to verify that dbContext1 did the expected updates to the database
    // but the code throws exceptions as it can't find tables

2- when I choose to generate a file on disk instead of in-memory database, the code works fine and the tests pass if run individually but fail if run in sequence even when I made sure they're isolated and a new database file is generated for each test !! ( man, I hate nothing more than flaky tests).

I tried with different DbInitializers with no success
Did I miss something ?? or did someone have more success with this ?, please share some insights :)

Unit Tests

Unit Tests which should test the following:
DbModel -> SQLite Statements
SQLite Statements -> SQLite DDL-SQL
Public API (as the console application does now)

Annotation for Key an MaxLength

Something like that should work:

[Key]
[MaxLength(31)]
public string Artikelnummer { get; set; }

duplicate entry when creating mixed relationship

I have two entity: Employee and Division
A Division can have many Employees -- 1:n
A Division has one Employee as Supervisor -- 1:1
So I want to create a mixed relationship.

Code Models Below:

    public class Employee
    {
        [Key]
        public long EmployeeId { get; set; }
        [Required]
        public virtual Division Division { get; set; }
    }
    public class Division
    {
        [Key]
        public long DivisionId { get; set; }
        [Required]
        public virtual Employee Manager { get; set; }
        public virtual ICollection<Employee> Employees { get; set; }
            = new List<Employee>();
    }

This will generate two entries in Employee tables, Division_DivisionId and Division_DivisionId1. Add a [Required, ForeignKey("Manager")] public long ManagerID { get; set; } in Division class doesn't help.

Remove the [Required] public virtual Division Division { get; set; } can eliminate the duplication, but I want to decorate this foreign key as NOT NULL, and keep a navigation property.

Store enum columns (as integer)

Enum properties are not stored on the current implementation

[Flags]
public enum EAttributes : ulong
{
    // ...
}

public class MyTestTable
{
    public EAttributes Attributes1 { get; set; } // Column not created

    [Column("_attributes2")]
    public EAttributes Attributes2 { get; set; } // Column not created

    [Column("_attributes3", TypeName="integer")]
    public EAttributes Attributes3 { get; set; } // Column not created
}

doesn't seem to need EntityFramework.SqlServer

I may be missing something but I removed the EntityFramework.SqlServer reference in SQLite.CodeFirst and it still builds just fine. I suppose it comes with the Nuget package anyway. Just an FYI - thanks for building this.

{"SQL logic error or missing database\r\nno such table: Servers"}

I'm trying to use this great library and I just cannot get the tables created at startup. The SQLite file is created, but with a 0 size.

Any ideas why?

This is my DB context code:

        public DB()

        : base("DB")
        {
            //Configuration.ProxyCreationEnabled = true;
            //Configuration.LazyLoadingEnabled = true;
        }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            //modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

            ConfigureCredentialEntity(modelBuilder);
            ConfigurePlatformEntity(modelBuilder);
            ConfigureServerEntity(modelBuilder);

            var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<CloudMoveyEntities>(modelBuilder);

            Database.SetInitializer(sqliteConnectionInitializer);
        }

and

    public partial class DB : DbContext
    {
        private static void ConfigureCredentialEntity(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Credential>().HasKey(p => p.id);
        }
        private static void ConfigurePlatformEntity(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Platform>().HasKey(p => p.id);
        }
        private static void ConfigureServerEntity(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Server>().HasKey(p => p.id);
        }
    }

Initialize the database when the individual can only create or delete a table?

Hi,

You add a new initialization function "SqliteDropCreateDatabaseWhenModelChanges" Very Good!

Please can further refine this feature, go to "table-level"?

Logic is as follows,

1. When the time change Model (Model name does not change, but changes in the field), do not delete the database, but only delete the table, then rebuild.
2. If a new Model, the Model corresponding to the new table.
3. If the Model delete, delete the Model corresponding table.

thank you very much!

可不可以加入迁移(Migration)功能？

谢谢你的程序，让我们非常方便的使用CodeFirst功能，但是另一个很常见的工能Migration我不知道怎么使用，能不能给点提示，再次感谢。

Seeding with ContextInitializer

Unfortunately, the seeding does not work - but no exception and also no data.
Suggestions?

EF6 mono on linux

Hi

I get the following error when using sqlite:

at (wrapper managed-to-native) System.Data.SQLite.UnsafeNativeMethods.sqlite3_config_none (System.Data.SQLite.SQLiteConfigOpsEnum) <IL 0x00011, 0x000fb>
at System.Data.SQLite.SQLite3.StaticIsInitialized () <IL 0x0001e, 0x000eb>
at System.Data.SQLite.SQLiteLog.Initialize () <IL 0x00000, 0x00043>
at System.Data.SQLite.SQLiteConnection..ctor (string,bool) <IL 0x00021, 0x000b7>
at System.Data.SQLite.SQLiteConnection..ctor (string) <IL 0x00003, 0x00043>
at System.Data.SQLite.SQLiteConnection..ctor () <IL 0x00002, 0x0003b>
at (wrapper remoting-invoke-with-check) System.Data.SQLite.SQLiteConnection..ctor () <IL 0x00019, 0x00083>
at System.Data.SQLite.EF6.SQLiteProviderFactory.CreateConnection () <IL 0x00006, 0x0006b>
at System.Data.Entity.Internal.LazyInternalConnection.CreateConnectionFromProviderName (string) <IL 0x0000e, 0x000a1>
at System.Data.Entity.Internal.LazyInternalConnection.InitializeFromConnectionStringSetting (System.Configuration.ConnectionStringSettings) <IL 0x0003d, 0x001b7>
at System.Data.Entity.Internal.LazyInternalConnection.TryInitializeFromAppConfig (string,System.Data.Entity.Internal.AppConfig) <IL 0x0000d, 0x00093>
at System.Data.Entity.Internal.LazyInternalConnection.Initialize () <IL 0x0005b, 0x0022b>
at System.Data.Entity.Internal.LazyInternalConnection.get_ProviderName () <IL 0x00001, 0x0003b>
at System.Data.Entity.Internal.LazyInternalContext.get_ProviderName () <IL 0x00006, 0x0004b>
at System.Data.Entity.Internal.DefaultModelCacheKeyFactory.Create (System.Data.Entity.DbContext) <IL 0x0002b, 0x001fd>
at System.Data.Entity.Internal.LazyInternalContext.InitializeContext () <IL 0x0009b, 0x003fe>
at System.Data.Entity.Internal.InternalContext.Initialize () <IL 0x00001, 0x0003f>
at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType (System.Type) <IL 0x00001, 0x00043>
at System.Data.Entity.Internal.Linq.InternalSet`1<HesteNettet.Hest>.Initialize () <0x0008b>
at System.Data.Entity.Internal.Linq.InternalSet`1<HesteNettet.Hest>.GetEnumerator () <0x0003d>
at System.Data.Entity.Infrastructure.DbQuery`1<HesteNettet.Hest>.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator () <0x00070>
at System.Collections.Generic.List`1<HesteNettet.Hest>..ctor (System.Collections.Generic.IEnumerable`1<HesteNettet.Hest>) <0x00373>
at System.Linq.Enumerable.ToList<HesteNettet.Hest> (System.Collections.Generic.IEnumerable`1<HesteNettet.Hest>) <0x0009b>
at HesteNettet.Controllers.HesteController.Index () [0x0000c] in /home/davse/gits/HesteNettet/HesteNettet/Controllers/HesteController.cs:15
at (wrapper dynamic-method) object.lambda_method (System.Runtime.CompilerServices.Closure,System.Web.Mvc.ControllerBase,object[]) <IL 0x00006, 0x00080>
at System.Web.Mvc.ActionMethodDispatcher.Execute (System.Web.Mvc.ControllerBase,object[]) <IL 0x00008, 0x00054>
at System.Web.Mvc.ReflectedActionDescriptor.Execute (System.Web.Mvc.ControllerContext,System.Collections.Generic.IDictionary`2<string, object>) <IL 0x00073, 0x0031b>
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod (System.Web.Mvc.ControllerContext,System.Web.Mvc.ActionDescriptor,System.Collections.Generic.IDictionary`2<string, object>) <IL 0x00003, 0x00067>
at System.Web.Mvc.Async.AsyncControllerActionInvoker/ActionInvocation.InvokeSynchronousActionMethod () <IL 0x00018, 0x00057>
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39 (System.IAsyncResult,System.Web.Mvc.Async.AsyncControllerActionInvoker/ActionInvocation) <IL 0x00002, 0x0003b>
at (wrapper delegate-invoke) System.Web.Mvc.Async.EndInvokeDelegate`2<System.Web.Mvc.Async.AsyncControllerActionInvoker/ActionInvocation, System.Web.Mvc.ActionResult>.invoke_TResult_IAsyncResult_TState (System.IAsyncResult,System.Web.Mvc.Async.AsyncControllerActionInvoker/ActionInvocation) <IL 0x0005c, 0x002b7>
at System.Web.Mvc.Async.AsyncResultWrapper/WrappedAsyncResult`2<System.Web.Mvc.ActionResult, System.Web.Mvc.Async.AsyncControllerActionInvoker/ActionInvocation>.CallEndDelegate (System.IAsyncResult) <IL 0x0000d, 0x000a2>
at System.Web.Mvc.Async.AsyncResultWrapper/WrappedAsyncResultBase`1<System.Web.Mvc.ActionResult>.End () <0x000fe>
at System.Web.Mvc.Async.AsyncResultWrapper.End<System.Web.Mvc.ActionResult> (System.IAsyncResult,object) <0x0008b>
at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod (System.IAsyncResult) <IL 0x00006, 0x00053>
at System.Web.Mvc.Async.AsyncControllerActionInvoker/AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d () <IL 0x00021, 0x000d3>
at System.Web.Mvc.Async.AsyncControllerActionInvoker/AsyncInvocationWithFilters/<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f () <IL 0x00008, 0x0006d>

My connection string:

  <connectionStrings>
    <add name="sqliteconn" connectionString="Data Source=|DataDirectory|hest.db;version=3;" providerName="System.Data.SQLite.EF6"/>
  </connectionStrings>

I was in doubt regarding the providername.

Mono:

Mono JIT compiler version 4.0.2 (Stable 4.0.2.5/c99aa0c Wed Jun 24 10:04:37 UTC 2015)
Copyright (C) 2002-2014 Novell, Inc, Xamarin Inc and Contributors. www.mono-project.com
    TLS:           __thread
    SIGSEGV:       altstack
    Notifications: epoll
    Architecture:  amd64
    Disabled:      none
    Misc:          softdebug 
    LLVM:          supported, not enabled.
    GC:            sgen

Asp.net version 5.2

Can not configure the table name.

The following code,

protected override void OnModelCreating (DbModelBuilder modelBuilder)
{
modelBuilder.Entity () ToTable ("Base.DictionaryTimestamp");

var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists (modelBuilder);
Database.SetInitializer (sqliteConnectionInitializer);
}

Table name and the entity name mapping is not the same, but still with the name of the table created entity name, is still a "DictionaryTimestamp", rather than "Base.DictionaryTimestamp"
Is there a solution?

Thank you very much!

use initializer through config file

Hi,

Thanks a lot for creating this project, it's very helpful.

At the moment I have a db initializer that inherits from SqliteCreateDatabaseIfNotExists. If I create this custom initializer in db context OnModelCreating() and pass in DbModelBuilder, everything's working fine. However, if I specify this initializer in app.config:

<databaseInitializer type="..." />

It throws exception, since SqliteCreateDatabaseIfNotExists doesn't have default constructor. Is there anyway to config this in config file? I like the flexibility of config file approach, since I can change initializer without re-compiling the code

Thanks

InMemoryConnectionString throwing exception

When I am trying to run the project with in memory connection string the following exception is threw：“The given key was not present in the dictionary.”

My connection string like:

Possible to make the code work with in memory connection string?

Cheers
D

Error occurred with a console app in VS2015 SP1

I tried SQLite.CodeFirst with the following settings.

  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=db.sqlite;" providerName="System.Data.SQLite.EF6"/>
  </connectionStrings>

  <entityFramework>
    <providers>
      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>

This caused an error below.

No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SQLite'. Make sure the provider is registered in the 'entityFramework' section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

I managed to solve with the invariant name "System.Data.SQLite".

  <entityFramework>
    <providers>
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>

Which is correct? System.Data.SQLite or the original one?

Duplicate Index Error in SQL

In some situations, there is the possibility for the generated sql to contain more than one index with the same name which generates an error.

In my case, I have a table that relates one entity to another and some fields in other tables with the name CaseUpdateId that participates in a foreign key relationship \ entity relationship and the code creates and index for them all with the same IX_CaseUpdateId name.

Here one situation where it happens (I originally created the db on Sql Server and moved it to SQLite)

This is the model builder statement that creates the relationships
modelBuilder.Entity()
.HasMany(e => e.CaseUpdateBatches)
.WithMany(e => e.CaseUpdates)
.Map(m =>m.ToTable("BatchCaseUpdates").MapLeftKey("CaseUpdateId").MapRightKey("BatchId"));

modelBuilder.Entity()
.HasMany(e => e.CaseFieldUpdates)
.WithRequired(e => e.CaseUpdate)
.WillCascadeOnDelete(false);

This will result in indexes with the same name.

Not sure how you want to fix it. In my case, I modified CreateIndexStatementBuilder to add the table name to the index name. The result is something like IX_TableName_FieldName

Write XML comments for the public API

All members which are exposed from the SQLite.CodeFirst Library must be documented with XML comments.

Index naming change to IX_table_column

two table has the same column with [Index] will failed when create databases: the index is alread exsist

class a
{
[Index]
public string c {get;set;}
}
class b
{
[Index]
public string c {get;set;}
}

Database is not created if it is in an folder which doesn't exists

Hi,

thanks for the great tool.

I noticed, that if the connection string contains folder, which doesn't exist, the database is not created if you use SqliteCreateDatabaseIfNotExists or SqliteDropCreateDatabaseAlways.
For example the connstring maybe .\data\user.sqlite.

r Michael

Feature suggestion: Migrations

What do you think about migrations-support?

Error when using FK Constraints with PluralizingTableNameConvention