CodeFirst : not support adding a constraint

[Inetsis]

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

[Inetsis]

I use v5.1.36.6 of provider
And EF6

[Inetsis]

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

[Shalex]

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.

[Inetsis]

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

[Shalex]

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.

[Shalex]

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.