Code-First Migrations and EF Core Support Improvements in dotConnect for SQLite 5.18

The new version of Devart dotConnect for SQLite contains significant improvements of Entity Framework Core support, improving Entity Framework Core Code-First Migrations a lot and adding support for previously unsupported operations. Besides, we supported mapping of more .NET data types and extended capabilities of LINQ query translation to SQL.

LINQ to Entities Improvements

dotConnect for SQLite now supports translation of the following LINQ features to SQL for both EF Core 3 and EF Core 5:

• The static IsNullOrWhiteSpace() method of the String class
• The static Today property and instance DayOfWeek and Ticks properties of the DateTime class
• The following static methods of the Math class: Abs(), Round(), Truncate(), Floor(), Ceiling(), Max(), Min(), Pow(), Sqrt(), Log(), Log10(), Sin(), Cos(), Tan(), Asin(), Acos(), Atan()

Uri Data Type Mapping

For Entity Framework Core 3 and 5, dotConnect for SQLite now supports mapping the internet/intranet System.Uri type to SQLite ‘text’ data type.

public class Blog {
  public int Id { get; set; }
  public Uri Url { get; set; }
  public List<Post> Posts { get; set; }
}

CREATE TABLE Blog ( 
  Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  Url text NULL,
)

IPAddress and PhysicalAddress Type Mapping

For Entity Framework Core 5, dotConnect for SQLite supports mapping network types System.Net.IPAddress and System.Net.NetworkInformation.PhysicalAddress to SQLite ‘text’ data type.

public class AccessLog {
  public int Id { get; set; }
  public Uri Url { get; set; }
  public IPAddress IP { get; set; }
  public DateTime Timestamp { get; set; }
}

CREATE TABLE AccessLog ( 
  Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  Url text NULL,
  IP text NULL,
  Timestamp datetime NULL
)

Code-First Migrations Improvements

SQLite database engine has significant architectural limitations for the ALTER TABLE operation. This is why dotConnect for SQLite didn’t have support for a number of EF Core Code-First Migrations operations. The new dotConnect for SQLite version provides support for more operations via a workaround with creating a new table and copying data from the old table to it:

• AlterColumn
• RenameColumn (for SQLite 3.24 or lower)
• DropColumn (for SQLite 3.34 or lower)
• AddForeignKey
• DropForeignKey
• AddPrimaryKey
• DropPrimaryKey

Two of the new operations, RenameColumn and DropColumn, are supported in two different modes:

• If SQLite is of version 3.25/3.35 or higher, the native SQLite ALTER TABLE RENAME COLUMN and ALTER TABLE DROP COLUMN commands, which are supported since these versions, are used.
• If SQLite version is lower, the workaround with re-creating a table is used.

Let’s consider the following example with Dept and Emp classes:

  public class Dept {
    public int Deptno { get; set; }
    public string Dname { get; set; }
    public virtual ICollection<Emp> Emps { get; set; }
  }
 
  public class Emp {
    public int Empno { get; set; }
    public string Ename { get; set; }
    public int Deptno { get; set; }
    public virtual Dept Dept { get; set; }
  }

The following mapping is used:

  protected override void OnModelCreating(ModelBuilder modelBuilder) {
      modelBuilder.Entity<Dept>().ToTable("Dept");
      modelBuilder.Entity<Dept>().HasKey(p => p.Deptno);
      modelBuilder.Entity<Dept>().Property(p => p.Deptno).ValueGeneratedNever();
      modelBuilder.Entity<Dept>().Property(p => p.Dname).HasMaxLength(50);
      modelBuilder.Entity<Emp>().ToTable("Emp");
      modelBuilder.Entity<Emp>().HasKey(p => p.Empno);
      modelBuilder.Entity<Emp>().Property(p => p.Empno).ValueGeneratedNever();
      modelBuilder.Entity<Emp>().Property(p => p.Ename).HasMaxLength(100).IsRequired(true);
      modelBuilder.Entity<Emp>().HasIndex(p => p.Ename);
      modelBuilder.Entity<Emp>().HasOne(e => e.Dept).WithMany(d => d.Emps).HasForeignKey(e => e.Deptno);
  }

After we add the migration and apply it, the following DDL is generated:

CREATE TABLE IF NOT EXISTS Dept ( 
  Deptno integer NOT NULL,
  Dname varchar(50) NULL,
  PRIMARY KEY (Deptno)
);

CREATE TABLE IF NOT EXISTS Emp ( 
  Empno integer NOT NULL,
  Ename varchar(100) NOT NULL,
  Deptno integer NOT NULL,
  PRIMARY KEY (Empno),
  FOREIGN KEY (Deptno) REFERENCES Dept (Deptno) ON DELETE CASCADE
);

CREATE INDEX IX_Emp_Deptno ON Emp (Deptno);

CREATE INDEX IX_Emp_Ename ON Emp (Ename);

Let’s increase a string field length from 100 to 200.

  modelBuilder.Entity<Emp>().Property(p => p.Ename).HasMaxLength(200).IsRequired(true);

Let’s add a migration with a single AlterColumn operation.

  migrationBuilder.AlterColumn<string>(
      name: "Ename",
      table: "Emp",
      type: "varchar(200)",
      maxLength: 200,
      nullable: false,
      oldClrType: typeof(string),
      oldType: "varchar(100)",
      oldMaxLength: 100);

After we apply the migration, the following DDL is generated:

PRAGMA foreign_keys = 0;

CREATE TABLE __ef_temporary_Emp__ ( 
  Empno integer NOT NULL,
  Deptno integer NOT NULL,
  Ename varchar(200) NOT NULL,
  PRIMARY KEY (Empno),
  FOREIGN KEY (Deptno) REFERENCES Dept (Deptno) ON DELETE CASCADE
);

INSERT INTO __ef_temporary_Emp__ (Empno, Deptno, Ename)
SELECT Empno, Deptno, Ename
  FROM Emp;

DROP TABLE Emp;

ALTER TABLE __ef_temporary_Emp__
RENAME TO Emp;

PRAGMA foreign_keys = 1;

CREATE INDEX IX_Emp_Deptno ON Emp (Deptno);

CREATE INDEX IX_Emp_Ename ON Emp (Ename);

Conclusion

We are glad to present the updated dotConnect for SQLite with new features, and we are going to further improve Entity Framework Core support in the provider. We are waiting for your feedback and suggestions on the further improvements and new features. Meanwhile, our top priority feature is Entity Framework Core 6 support with all its new features and support for new .NET 6 types, like DateOnly and TimeOnly.