EF Core 6 New Features Summary (Part 4)

In this article, you will see improvements to EF Core for SQLite, In-memory provider, and the EF.Functions.Contains method.

This is the fourth article in the summary of new EF Core 6 features:

• EF Core 6 New Features Summary (1)
• EF Core 6 New Features Summary (2)
• EF Core 6 New Features Summary (3)
• EF Core 6 New Features Summary (4)

1 SQLite supports DateOnly and TimeOnly

In EF Core 6.0, the SQLite provider supports the new DateOnly and TimeOnly types. It stores them as TEXT.

using var context = new ExampleContext();

var query1 = context.People
    .Where(p => p.Birthday < new DateOnly(2000, 1, 1))
    .ToQueryString();

Console.WriteLine(query1);
// SELECT "p"."Id", "p"."Birthday", "p"."Name"
// FROM "People" AS "p"
// WHERE "p"."Birthday" < '2000-01-01'

var query2 = context.Notifications
    .Where(n => n.AllowedFrom >= new TimeOnly(8, 0) && n.AllowedTo <= new TimeOnly(16, 0))
    .ToQueryString();

Console.WriteLine(query2);
// SELECT "n"."Id", "n"."AllowedFrom", "n"."AllowedTo"
// FROM "Notifications" AS "n"
// WHERE("n"."AllowedFrom" >= '08:00:00') AND("n"."AllowedTo" <= '16:00:00')

class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateOnly Birthday { get; set; }
}
class Notification
{
    public int Id { get; set; }
    public TimeOnly AllowedFrom { get; set; }
    public TimeOnly AllowedTo { get; set; }
}
class ExampleContext : DbContext
{
    public DbSet<Person> People { get; set; }
    public DbSet<Notification> Notifications { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite(@"Data Source=Db\DateOnlyTimeOnly.db");
}

2 SQLite connections are pooled

SQLite databases are files. Therefore, in most cases, creating a connection is fast. However, opening a connection to an encrypted database can be very slow. Thus, in EF Core 6, SQLite connections are now pooled, similar to other database providers.

class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
}
class ExampleContext : DbContext
{
    public DbSet<Person> People { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=EncryptedDb.db;Mode=ReadWriteCreate;Password=password");
}

3 Command timeout in SQLite

In EF Core 6 for SQLite, you can add a Command Timeout command to the connection string, which you can use to specify the default timeout for SQLite.

class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
}
class ExampleContext : DbContext
{
    public DbSet<Person> People { get; set; }

    // 60 seconds as the default timeout for commands created by connection
    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=Test.db;Command Timeout=60");
}

4 Savepoints in SQLite

In EF Core 6.0, SQLite supports savepoints. You can save, roll back, and release savepoints.

var dbPath = Path.GetFullPath(Path.Combine(AppContext.BaseDirectory, "..\\..\\..\\Savepoints.db"));

using var connection = new SqliteConnection($"Data Source={dbPath}");
connection.Open();
using var transaction = connection.BeginTransaction();

// The insert is committed to the database
using (var command = connection.CreateCommand())
{
    command.CommandText = @"INSERT INTO People (Name) VALUES ('Oleg')";
    command.ExecuteNonQuery();
}

transaction.Save("MySavepoint");

// The update is not committed since savepoint is rolled back before committing the transaction
using (var command = connection.CreateCommand())
{
    command.CommandText = @"UPDATE People SET Name = 'Not Oleg' WHERE Id = 1";
    command.ExecuteNonQuery();
}

transaction.Rollback("MySavepoint");
transaction.Commit();

5 In-memory database validates required properties

In EF Core 6.0, the In-memory database validates required properties. If you try to save an entity with a null value for a required property, an exception is thrown. If necessary, you can disable this validation.

using var context = new ExampleContext();

var blog = new Blog();
context.Blogs.Add(blog);

await context.SaveChangesAsync();
// Unhandled exception. Microsoft.EntityFrameworkCore.DbUpdateException:
// Required properties '{'Title'}' are missing for the instance of entity
// type 'Blog' with the key value '{Id: 1}'.

class Blog
{
    public int Id { get; set; }
    [Required]
    public string Title { get; set; }
}
class ExampleContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options
        .EnableSensitiveDataLogging()
        .LogTo(Console.WriteLine, new[] { InMemoryEventId.ChangesSaved })
        .UseInMemoryDatabase("ValidateRequiredProps");

    //  To disable the validation
    //  .UseInMemoryDatabase("ValidateRequiredProps", b => b.EnableNullChecks(false));
}

6 EF.Functions.Contains method

In EF Core 6.0, you can use the EF.Functions.Contains method to work with columns mapped using value converters (it can also work with binary columns).

using var context = new ExampleContext();

var query = context.People
    .Where(e => EF.Functions.Contains(e.FullName, "Oleg"))
    .ToQueryString();

Console.WriteLine(query);
// SELECT[p].[Id], [p].[FullName]
// FROM[People] AS[p]
// WHERE CONTAINS([p].[FullName], N'Oleg')

class Person
{
    public int Id { get; set; }
    public FullName FullName { get; set; }
}
public class FullName
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}
class ExampleContext : DbContext
{
    public DbSet<Person> People { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>()
            .Property(x => x.FullName)
            .HasConversion(
                v => JsonSerializer.Serialize(v, (JsonSerializerOptions)null),
                v => JsonSerializer.Deserialize<FullName>(v, (JsonSerializerOptions)null));
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFCore6Contains");
}

7 Conclusion

All code examples in this article can be found in my GitHub: https://github.com/okyrylchuk/dotnet6_features/tree/main/EF%20Core%206#miscellaneous-enhancements

Original: https://blog.okyrylchuk.dev/entity-framework-core-6-features-part-3

Author: Oleg Kyrylchuk

Translation: Refined Coder