Devart Forums

Posted: **Thu 21 Nov 2013 13:10**

Hello,

I just started a new project and I evaluate the code first approach with Sqlite.
I managed to create my tables to save data and read it with your provider : good !

So I go to step 2 : have a relationship between two tables.
And here is my problem : SQLite does not support adding a constraint.

Error happen when I use the Add-Migration command
My sample code :

Code: Select all

    public class BaseLocaleContext : DbContext
    {
        public DbSet<Article> Articles { get; set; }
        public DbSet<Groupe> Groupes { get; set; }
    }
    public class Article
    {
        public int Id { get; set; }
        public string Reference { get; set; }
        public int GroupeId { get; set; }
        public Groupe Groupe { get; set; }
    }
    public class Groupe
    {
        public int Id { get; set; }
        public string Nom { get; set; }
    }

And the stack :

Applying explicit migration: 201311211250531_AddGroupe.
System.NotSupportedException: SQLite does not support adding a constraint.
à Devart.Data.SQLite.Entity.x.a(be A_0)
à Devart.Common.Entity.be.a(a2 A_0)
à Devart.Common.Entity.k.a(List`1 A_0)
à Devart.Common.Entity.Migrations.a.a(IEnumerable`1 A_0, String A_1)
à Devart.Data.SQLite.Entity.Migrations.SQLiteEntityMigrationSqlGenerator.Generate(IEnumerable`1 migrationOperations, String providerManifestToken)
à System.Data.Entity.Migrations.DbMigrator.GenerateStatements(IList`1 operations, String migrationId)
à System.Data.Entity.Migrations.Infrastructure.MigratorBase.GenerateStatements(IList`1 operations, String migrationId)
à System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, Boolean downgrading, Boolean auto)
à System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
à System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
à System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
à System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
à System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration)
à System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClassc.<Update>b__b()
à System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
à System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
à System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
à System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
à System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.Run()
à System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
à System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
à System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner)
à System.Data.Entity.Migrations.Design.ToolingFacade.Update(String targetMigration, Boolean force)
à System.Data.Entity.Migrations.UpdateDatabaseCommand.<>c__DisplayClass2.<.ctor>b__0()
à System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command)

Sqlite seem to support FK : http://www.sqlite.org/foreignkeys.html

What did I do wrong?
Thanks

Posted: **Thu 21 Nov 2013 13:20**

I use v5.1.36.6 of provider
And EF6

Posted: **Thu 21 Nov 2013 14:36**

Ok I did some research and it seem a bug.
Indeed when I set the FK at the first migration, it's done

FK on table creation = Work

Code: Select all

CreateTable(
    "dbo.Articles",
    c => new
        {
            Id = c.Int(nullable: false, identity: true),
            Reference = c.String(),
            GroupeId = c.Int(nullable: false),
        })
    .PrimaryKey(t => t.Id)
    .ForeignKey("dbo.Groupes", t => t.GroupeId)
    .Index(t => t.GroupeId);
            
CreateTable(
    "dbo.Groupes",
    c => new
        {
            Id = c.Int(nullable: false, identity: true),
            Nom = c.String(),
        })
    .PrimaryKey(t => t.Id);

FK on table update = Don't work

Code: Select all

public override void Up()
{
    AddColumn("dbo.Articles", "GroupeId", c => c.Int(nullable: false, defaultValue: 1));
    CreateIndex("dbo.Articles", "GroupeId");
    AddForeignKey("dbo.Articles", "GroupeId", "dbo.Groupes", "Id");
}

Thanks for your reply

Posted: **Fri 22 Nov 2013 15:00**

Inetsis wrote:SQLite does not support adding a constraint.

This limitation of SQLite engine is described in our blog article: http://blog.devart.com/entity-framework ... ionSupport.

We will investigate the possibility of supporting AddForeignKey constraint operation by adding an extra column with a REFERENCES clause in the ALTER TABLE statement: http://www.sqlite.org/lang_altertable.html. But rolling back (removing column) will not be possible in this scenario due to the another limitation of SQLite engine. We will post here about the result of our investigation.

Posted: **Tue 26 Nov 2013 08:48**

finally I chose SQL Ce 4, it work fine on this senerio

Posted: **Wed 27 Nov 2013 09:28**

The AddForeignKey constraint operation for one-column FK is supported in Code-First Migrations for the case when a new column for FK is created. We will post here when the corresponding build of dotConnect for SQLite is available for download.

Posted: **Thu 28 Nov 2013 14:22**

New build of dotConnect for SQLite 5.1.45 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/sqlite/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=29&t=28405.

Devart Forums

CodeFirst : not support adding a constraint

CodeFirst : not support adding a constraint

Re: CodeFirst : not support adding a constraint

Re: CodeFirst : not support adding a constraint

Re: CodeFirst : not support adding a constraint

Re: CodeFirst : not support adding a constraint

Re: CodeFirst : not support adding a constraint

Re: CodeFirst : not support adding a constraint