Devart：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.

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.

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:

The following mapping is used:

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

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

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

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

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.