EF 4.1 Code First and dotConnect for SQLite

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
jrlewis
Posts: 8
Joined: Fri 18 Nov 2011 15:29

EF 4.1 Code First and dotConnect for SQLite

Post by jrlewis » Fri 18 Nov 2011 17:55

I have run into a couple issues using the sqlite provider and code first. The code below shows a simple model with 2 objects. The Entity object has a collection of EntityObjects.

The fist issue is related to primary keys and autoincrement. In the case of primary keys that are int's the database being generated isn't creating these using AUTOINCREMENT (equivalent of Identity for Sql Server) even if specifying it explicitly using the fluent api. The second issue is related to cascade on delete for a one to many relationship. The foreign key relationship in the EntityObject table is not being created with ON DELETE CASCADE even if specifying it explicitly. In both cases when omitting the directives using the fluent api shouldn't the default conventions of StoreGeneratedIdentityKeyConvention and OneToManyCascadeDeleteConvention be followed? Using other data providers (e.g., Sql Server Compact Edition) with the same model gives the correct result.

Code: Select all

public class Entity
{
    public Entity()
    {
        this.Name = "";
    }

    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection Objects { get; set; }   
}

public class EntityConfiguration
   : EntityTypeConfiguration
{
    internal EntityConfiguration()
    {
        // Keys/Relationships
        this.HasKey(e => e.Id);
        this.Property(e => e.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        this.HasMany(e => e.Objects)
            .WithRequired()
            .HasForeignKey(o => o.EntityId)
            .WillCascadeOnDelete();

        // Properties
        this.Property(e => e.Name).HasMaxLength(200).IsRequired();
    }
}

public class EntityObject
{
    public int Id { get; set; }
    public string Name { get; set; }

    public int EntityId { get; set; }
}

public class EntityObjectConfiguration
    : EntityTypeConfiguration
{
    internal EntityObjectConfiguration()
    {
        // Keys/Relationships
        this.HasKey(o => o.Id);
        this.Property(o => o.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            
        // Properties
        this.Property(o => o.Name).HasMaxLength(200).IsRequired();
    }
}
SQL statements from created tables

CREATE TABLE Entities (
Id INTEGER NOT NULL,
Name varchar(200) NOT NULL,
PRIMARY KEY (Id)
)

CREATE TABLE EntityObjects (
Id INTEGER NOT NULL,
Name varchar(200) NOT NULL,
EntityId int32 NOT NULL,
PRIMARY KEY (Id),
FOREIGN KEY (EntityId) REFERENCES Entities (Id)
)

Shalex
Devart Team
Posts: 8033
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 22 Nov 2011 12:44

We have reproduced the described behaviour. We will investigate the issue and notify you about the results.

Shalex
Devart Team
Posts: 8033
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 24 Nov 2011 09:46

jrlewis wrote:The fist issue is related to primary keys and autoincrement. In the case of primary keys that are int's the database being generated isn't creating these using AUTOINCREMENT (equivalent of Identity for Sql Server) even if specifying it explicitly using the fluent api.
This is a designed behaviour. The INTEGER primary column in SQLite is always AUTOINCREMENT.
jrlewis wrote:The second issue is related to cascade on delete for a one to many relationship. The foreign key relationship in the EntityObject table is not being created with ON DELETE CASCADE even if specifying it explicitly.
The bug with generating ON DELETE CASCADE in SQL when using Code First is fixed. We will post here when the corresponding build of dotConnect for SQLite is available for download.

jrlewis
Posts: 8
Joined: Fri 18 Nov 2011 15:29

Post by jrlewis » Mon 28 Nov 2011 14:04

jrlewis wrote:
The fist issue is related to primary keys and autoincrement. In the case of primary keys that are int's the database being generated isn't creating these using AUTOINCREMENT (equivalent of Identity for Sql Server) even if specifying it explicitly using the fluent api.
shalex wrote:
This is a designed behaviour. The INTEGER primary column in SQLite is always AUTOINCREMENT.
That is not correct. The only way an INTEGER field is created as AUTOINCREMENT in sqlite is if it is specified in the creation of the table as INTEGER PRIMARY KEY AUTOINCREMENT. If you don't then when you have an INTEGER column and you insert a record without a value for the pk it will assign a value of 1 greater than the largest in the table at the time. It doesn't keep track of the most rececnt value that was assigned in the sqlite_sequence table. An INTEGER specified as a autoincrement does.

Shalex
Devart Team
Posts: 8033
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 30 Nov 2011 10:59

Thank you for the correction (indeed, explicit setting AUTOINCREMENT leads to a better performance).

The bug with generating AUTOINCREMENT in DDL for key columns when using Identity in Code First is fixed. We will post here when the corresponding build of dotConnect for SQLite is available for download.

jrlewis
Posts: 8
Joined: Fri 18 Nov 2011 15:29

Post by jrlewis » Wed 30 Nov 2011 14:21

Thanks for your prompt response on this. Greatly appreciated.

Shalex
Devart Team
Posts: 8033
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 09 Dec 2011 16:33

New version of dotConnect for SQLite 3.60 is released!
It can be downloaded from http://www.devart.com/dotconnect/sqlite/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=22840 .

jrlewis
Posts: 8
Joined: Fri 18 Nov 2011 15:29

Post by jrlewis » Tue 27 Dec 2011 15:35

This version did fix the issue when using int's, but it created a breaking change if you were using a long for the primary key. With version 3.50.250 when using a long instead of an int for the Id's you would get the same results as above. With version 3.60.258 you get what is shown below. Note that it is no longer creating a Primary Key constraint even though HasKey is being specified. Aside from that both int's and long's should logically be treated the same if marked as a primary key and be created as an Autoincrement unless the HasDatabaseGeneratedOption(DatabaseGeneratedOption.None) is used in which case the field will only be created with a Primary Key constraint.

CREATE TABLE Entities (
Id integer NOT NULL,
Name varchar(200) NOT NULL
)

CREATE TABLE EntityObjects (
Id integer NOT NULL,
Name varchar(200) NOT NULL,
EntityId integer NOT NULL,
FOREIGN KEY (EntityId) REFERENCES Entities (Id) ON DELETE CASCADE
)

Shalex
Devart Team
Posts: 8033
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 29 Dec 2011 13:28

We are investigating the problem.

Shalex
Devart Team
Posts: 8033
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 17 Jan 2012 12:09

The problem is fixed in the current (3.60.283) build of dotConnect for SQLite. Please try it and notify us about the results.

Post Reply