Data Access: EF Core and Dapper

33.1 Entity Framework Core from VB

Tooling caveat: dotnet ef migrations add generates C# code. Options:

Keep the DbContext and migrations in a small C# project referenced by VB.

Use EF Core Power Tools (it supports VB reverse engineering).

Use SQL scripts / DbUp for schema changes.

Topics: change tracking, Include/ThenInclude, projections, AsNoTracking, raw SQL (FromSql), transactions, concurrency tokens (rowversion), compiled queries, global query filters, value converters, interceptors..NET Framework: EF6 (EDMX designer or Code First) works with VB.

Imports Microsoft.EntityFrameworkCore

Public Class Customer
    Public Property Id As Integer
    Public Property Name As String
    Public Property Orders As New List(Of Order)
End Class

Public Class Order
    Public Property Id As Integer
    Public Property CustomerId As Integer
    Public Property Customer As Customer
    Public Property Total As Decimal
    Public Property PlacedAt As Date
End Class

Public Class ShopContext
    Inherits DbContext
    Public Sub New(options As DbContextOptions(Of ShopContext))
        MyBase.New(options)
    End Sub
    Public Property Customers As DbSet(Of Customer)
    Public Property Orders As DbSet(Of Order)
    Protected Overrides Sub OnModelCreating(mb As ModelBuilder)
        mb.Entity(Of Customer)(Sub(e)
                                   e.Property(Function(c) c.Name).HasMaxLength(100).IsRequired()
                                   e.HasMany(Function(c) c.Orders).WithOne(Function(o) o.Customer)
                               End Sub)
        mb.Entity(Of Order)().Property(Function(o) o.Total).HasPrecision(18, 2)
    End Sub
End Class

' Registration: services.AddDbContext(Of ShopContext)(Sub(o) o.UseSqlServer(connStr))
Dim bigSpend
…

33.2 Dapper

Choose EF Core for rich domain models and productivity. Choose Dapper for performance-critical, SQL-centric code. You can mix both.

Imports Dapper
Using conn As New SqlConnection(connStr)
    Dim orders = Await conn.QueryAsync(Of Order)(
        "SELECT * FROM Orders WHERE CustomerId = @CustomerId AND PlacedAt >= @From",
        New With {.CustomerId = 5, .From = Date.Today.AddMonths(-1)})
    Dim newId = Await conn.ExecuteScalarAsync(Of Integer)(
        "INSERT INTO Customers(Name) OUTPUT INSERTED.Id VALUES (@Name)", New With {.Name = "Ana"})
    Using multi = Await conn.QueryMultipleAsync("SELECT * FROM Customers WHERE Id=@id; SELECT * FROM Orders WHERE CustomerId=@id", New With {.id = newId})
        Dim cust = Await multi.ReadSingleAsync(Of Customer)()
        Dim custOrders = (Await multi.ReadAsync(Of Order)()).ToList()
    End Using
End Using