Data Access with ADO.NET and EF Core

Option Strict On

Imports Microsoft.Data.SqlClient

Module Offline
    Sub Main()
        Dim builder As New SqlConnectionStringBuilder()
        Console.WriteLine("Defaults of SqlConnectionStringBuilder:")
        Console.WriteLine("  Encrypt             = " & builder.Encrypt.ToString())
        Console.WriteLine("  TrustServerCertificate = " & builder.TrustServerCertificate)
        Console.WriteLine("  Pooling             = " & builder.Pooling)
        Console.WriteLine("  Min / Max Pool Size = " & builder.MinPoolSize & " / " & builder.MaxPoolSize)
        Console.WriteLine("  Connect Timeout     = " & builder.ConnectTimeout)
        Console.WriteLine("  MultipleActiveResultSets = " & builder.MultipleActiveResultSets)

        builder.DataSource = "db.example.test"
        builder.InitialCatalog = "Shelf"
        builder.IntegratedSecurity = True
        builder.ApplicationName = "Shelf.Cli"
        builder.ConnectTimeout = 1
        Console.WriteLine("Built: " & builder.ConnectionString)

        ' Nothing listens on this loopback port, so the failure is immediate and needs no network.
        builder.DataSource = "127.0.0.1,1"
        Using connection As New SqlConnection(builder.ConnectionString)
            Try
                connection.Open()
            Catch ex As SqlException
                Console.WriteLine("SqlException number " & ex.Number & ", class " & ex.Class)
                Console.WriteLine("  message starts: " & ex.Message.Substring(0, ex.Message.IndexOf(" The server")))
            End Try
        End Using
    End Sub
End Module

Defaults of SqlConnectionStringBuilder:
  Encrypt             = True
  TrustServerCertificate = False
  Pooling             = True
  Min / Max Pool Size = 0 / 100
  Connect Timeout     = 15
  MultipleActiveResultSets = False
Built: Data Source=db.example.test;Initial Catalog=Shelf;Integrated Security=True;Connect Timeout=1;Application Name=Shelf.Cli
SqlException number 0, class 20
  message starts: A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Option Strict On

Imports System.IO
Imports Microsoft.Data.Sqlite
Imports Microsoft.Extensions.Configuration

Module ConfigDemo
    Sub Main()
        Dim folder = Path.Combine(Path.GetTempPath(), "shelf-config-demo")
        Directory.CreateDirectory(folder)
        File.WriteAllText(Path.Combine(folder, "appsettings.json"),
            "{ ""ConnectionStrings"": { ""Shelf"": ""Data Source=shelf-dev.db;Cache=Shared"" } }")

        Dim configuration = New ConfigurationBuilder().
            SetBasePath(folder).
            AddJsonFile("appsettings.json", optional:=False).
            AddEnvironmentVariables(prefix:="SHELF_").
            Build()

        Dim fromFile = configuration.GetConnectionString("Shelf")
        Console.WriteLine("From appsettings.json: " & fromFile)

        Environment.SetEnvironmentVariable("SHELF_ConnectionStrings__Shelf", "Data Source=:memory:")
        Dim withEnvironment = New ConfigurationBuilder().
            SetBasePath(folder).
            AddJsonFile("appsettings.json", optional:=False).
            AddEnvironmentVariables(prefix:="SHELF_").
            Build()
        Dim effective = withEnvironment.GetConnectionString("Shelf")
        Console.WriteLine("With SHELF_ConnectionStrings__Shelf set: " & effective)

        Dim builder As New SqliteConnectionStringBuilder(effective)
        builder.Mode = SqliteOpenMode.ReadWriteCreate
        builder.Pooling = False
        Console.WriteLine("Normalised: " & builder.ConnectionString)

        Using connection As New SqliteConnection(builder.ConnectionString)
            connection.Open()
            Console.WriteLine("Opened, server version " & connection.ServerVersion.Split("."c)(0) & ".x")
        End Using
        File.Delete(Path.Combine(folder, "appsettings.json"))
    End Sub
End Module

From appsettings.json: Data Source=shelf-dev.db;Cache=Shared
With SHELF_ConnectionStrings__Shelf set: Data Source=:memory:
Normalised: Data Source=:memory:;Mode=ReadWriteCreate;Pooling=False
Opened, server version 3.x

Option Strict On

Imports Microsoft.Data.Sqlite

Module Pitfalls
    Sub Main()
        Using connection As New SqliteConnection("Data Source=:memory:")
            ' 1. A command on a closed connection
            Using command = connection.CreateCommand()
                command.CommandText = "SELECT 1"
                Try
                    command.ExecuteScalar()
                Catch ex As InvalidOperationException
                    Console.WriteLine("Closed connection: " & ex.Message)
                End Try
            End Using

            connection.Open()
            Using create = connection.CreateCommand()
                create.CommandText = "CREATE TABLE Books (Id INTEGER PRIMARY KEY, Title TEXT NOT NULL, Isbn TEXT NOT NULL UNIQUE, Subtitle TEXT NULL)"
                create.ExecuteNonQuery()
            End Using

            ' 2. Nothing is not NULL
            Using insert = connection.CreateCommand()
                insert.CommandText = "INSERT INTO Books (Title, Isbn, Subtitle) VALUES ($title, $isbn, $subtitle)"
                insert.Parameters.Add("$title", SqliteType.Text).Value = "Dune"
                insert.Parameters.Add("$isbn", SqliteType.Text).Value = "9780441172719"
                insert.Parameters.Add("$subtitle", SqliteType.Text).Value = Nothing
                Try
                    insert.ExecuteNonQuery()
                Catch ex As InvalidOperationException
                    Console.WriteLine("Value Nothing: " & ex.Message)
                End Try
                insert.Parameters("$subtitle").Value = DBNull.Value
                Console.WriteLine("Value DBNull.Value: inserted " & insert.ExecuteNonQuery())
            End Using

            ' 3. A constraint violation
            Using duplicate = connection.CreateCommand()
                duplicate.CommandText = "INSERT INTO Books (Title, Isbn) VALUES ('Dune again', '9780441172719')"
                Try
                    duplicate.ExecuteNonQuery()
                Catch ex As SqliteException
                    Console.WriteLine($"SqliteException {ex.SqliteErrorCode}/{ex.SqliteExtendedErrorCode}: {ex.Message}")
                End Try
            End Using

            ' 4. Concatenated text versus a parameter
            Dim hostile = "x' OR '1'='1"
            Using unsafeCommand = connection.CreateCommand()
                unsafeCommand.CommandText = "SELECT COUNT(*) FROM Books WHERE Title = '" & hostile & "'"
                Console.WriteLine("Concatenated: " & unsafeCommand.CommandText)
                Console.WriteLine("  rows matched: " & CLng(unsafeCommand.ExecuteScalar()))
            End Using
            Using safeCommand = connection.CreateCommand()
                safeCommand.CommandText = "SELECT COUNT(*) FROM Books WHERE Title = $title"
                safeCommand.Parameters.Add("$title", SqliteType.Text).Value = hostile
                Console.WriteLine("Parameterised: " & safeCommand.CommandText)
                Console.WriteLine("  rows matched: " & CLng(safeCommand.ExecuteScalar()))
            End Using

            ' 5. Reading a NULL column without checking
            Using query = connection.CreateCommand()
                query.CommandText = "SELECT Subtitle FROM Books"
                Using reader = query.ExecuteReader()
                    reader.Read()
                    Try
                        Console.WriteLine(reader.GetString(0))
                    Catch ex As InvalidOperationException
                        Console.WriteLine("GetString on NULL: " & ex.Message)
                    End Try
                    Console.WriteLine("GetValue on NULL is DBNull: " & (reader.GetValue(0) Is DBNull.Value))
                End Using
            End Using
        End Using
    End Sub
End Module

Closed connection: ExecuteScalar can only be called when the connection is open.
Value Nothing: Value must be set.
Value DBNull.Value: inserted 1
SqliteException 19/2067: SQLite Error 19: 'UNIQUE constraint failed: Books.Isbn'.
Concatenated: SELECT COUNT(*) FROM Books WHERE Title = 'x' OR '1'='1'
  rows matched: 1
Parameterised: SELECT COUNT(*) FROM Books WHERE Title = $title
  rows matched: 0
GetString on NULL: The data is NULL at ordinal 0. This method can't be called on NULL values. Check using IsDBNull before calling.
GetValue on NULL is DBNull: True

Option Strict On

Imports Microsoft.Data.Sqlite
Imports Microsoft.Extensions.Caching.Memory

Module CacheDemo
    Private _queries As Integer

    Sub Main()
        Using connection As New SqliteConnection("Data Source=:memory:")
            connection.Open()
            Using setup = connection.CreateCommand()
                setup.CommandText = "CREATE TABLE Books (Id INTEGER PRIMARY KEY, Title TEXT); INSERT INTO Books (Title) VALUES ('Dune')"
                setup.ExecuteNonQuery()
            End Using

            Using cache As New MemoryCache(New MemoryCacheOptions())
                For i = 1 To 3
                    Dim title = cache.GetOrCreate("book:1",
                        Function(entry)
                            entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(5)
                            Return LoadTitle(connection, 1)
                        End Function)
                    Console.WriteLine($"Request {i}: {title} (database queries so far: {_queries})")
                Next
                cache.Remove("book:1")
                Console.WriteLine("After Remove: " & cache.GetOrCreate("book:1", Function(entry) LoadTitle(connection, 1)) & " (queries: " & _queries & ")")
            End Using
        End Using
    End Sub

    Private Function LoadTitle(connection As SqliteConnection, id As Integer) As String
        _queries += 1
        Using command = connection.CreateCommand()
            command.CommandText = "SELECT Title FROM Books WHERE Id = $id"
            command.Parameters.Add("$id", SqliteType.Integer).Value = id
            Return CStr(command.ExecuteScalar())
        End Using
    End Function
End Module

Request 1: Dune (database queries so far: 1)
Request 2: Dune (database queries so far: 1)
Request 3: Dune (database queries so far: 1)
After Remove: Dune (queries: 2)

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <RootNamespace>ShelfApp</RootNamespace>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="10.0.13" />
    <ProjectReference Include="..\Shelf.Core\Shelf.Core.vbproj" />
  </ItemGroup>
</Project>

Option Strict On

Imports Microsoft.Data.Sqlite
Imports Microsoft.EntityFrameworkCore
Imports Microsoft.EntityFrameworkCore.Storage.ValueConversion
Imports Shelf.Core

Public Class AuthorEntity
    Public Property Id As Integer
    Public Property Name As String = ""
    Public Property Books As New List(Of BookEntity)
End Class

Public Class BookEntity
    Public Property Id As Integer
    Public Property Title As String = ""
    Public Property Year As Integer
    Public Property Isbn As Isbn
    Public Property Version As Integer
    Public Property AuthorId As Integer
    Public Property Author As AuthorEntity
End Class

Public Class ShelfContext
    Inherits DbContext

    Public Sub New(options As DbContextOptions(Of ShelfContext))
        MyBase.New(options)
    End Sub

    Public Property Authors As DbSet(Of AuthorEntity)
    Public Property Books As DbSet(Of BookEntity)

    Protected Overrides Sub OnModelCreating(modelBuilder As ModelBuilder)
        modelBuilder.Entity(Of AuthorEntity)(
            Sub(author)
                author.Property(Function(a) a.Name).IsRequired().HasMaxLength(100)
                author.HasMany(Function(a) a.Books).WithOne(Function(b) b.Author).HasForeignKey(Function(b) b.AuthorId)
            End Sub)

        modelBuilder.Entity(Of BookEntity)(
            Sub(book)
                book.Property(Function(b) b.Title).IsRequired().HasMaxLength(200)
                book.Property(Function(b) b.Version).IsConcurrencyToken()
                book.Property(Function(b) b.Isbn).HasConversion(New IsbnConverter()).HasMaxLength(13)
                book.HasIndex(Function(b) b.Isbn).IsUnique()
            End Sub)

        modelBuilder.Entity(Of AuthorEntity)().HasData(
            New AuthorEntity With {.Id = 1, .Name = "Frank Herbert"},
            New AuthorEntity With {.Id = 2, .Name = "William Gibson"})
    End Sub
End Class

Public Class IsbnConverter
    Inherits ValueConverter(Of Isbn, String)

    Public Sub New()
        MyBase.New(Function(i) i.Value, Function(s) Isbn.Parse(s))
    End Sub
End Class

Public Module Db
    Public Function CreateOptions(connection As SqliteConnection) As DbContextOptions(Of ShelfContext)
        Return New DbContextOptionsBuilder(Of ShelfContext)().UseSqlite(connection).Options
    End Function

    ' One open in-memory connection keeps the database alive for the whole run.
    Public Function OpenDatabase() As SqliteConnection
        Dim connection As New SqliteConnection("Data Source=:memory:")
        connection.Open()
        Using context As New ShelfContext(CreateOptions(connection))
            context.Database.EnsureCreated()
        End Using
        Return connection
    End Function
End Module

Option Strict On

Imports Microsoft.EntityFrameworkCore
Imports Shelf.Core

Module Crud
    Sub Main()
        Using connection = OpenDatabase()
            Using context As New ShelfContext(CreateOptions(connection))
                Dim herbert = context.Authors.Single(Function(a) a.Name = "Frank Herbert")
                Dim dune As New BookEntity With {.Title = "Dune", .Year = 1965, .Isbn = Isbn.Parse("9780441172719"), .Author = herbert}
                Console.WriteLine("Before Add: " & context.Entry(dune).State.ToString())
                context.Books.Add(dune)
                Console.WriteLine("After Add:  " & context.Entry(dune).State.ToString())
                context.SaveChanges()
                Console.WriteLine("After Save: " & context.Entry(dune).State.ToString() & ", Id=" & dune.Id)

                dune.Year = 1966
                Console.WriteLine("After edit: " & context.Entry(dune).State.ToString())
                context.ChangeTracker.DetectChanges()
                Console.WriteLine(context.ChangeTracker.DebugView.ShortView)
                context.SaveChanges()
            End Using

            Using context As New ShelfContext(CreateOptions(connection))
                Dim loaded = context.Books.AsNoTracking().Single()
                Console.WriteLine($"Reloaded: {loaded.Title} {loaded.Year} {loaded.Isbn}")

                Dim changed = context.Books.Where(Function(b) b.Year < 2000).
                    ExecuteUpdate(Function(setters) setters.SetProperty(Function(b) b.Year, Function(b) b.Year + 1))
                Console.WriteLine("ExecuteUpdate rows: " & changed)
                Console.WriteLine("Year now: " & context.Books.Select(Function(b) b.Year).Single())

                Dim deleted = context.Books.Where(Function(b) b.Title = "Dune").ExecuteDelete()
                Console.WriteLine("ExecuteDelete rows: " & deleted)
            End Using
        End Using
    End Sub
End Module

Before Add: Detached
After Add:  Added
After Save: Unchanged, Id=1
After edit: Modified
AuthorEntity {Id: 1} Unchanged
BookEntity {Id: 1} Modified FK {AuthorId: 1}

Reloaded: Dune 1966 9780441172719
ExecuteUpdate rows: 1
Year now: 1967
ExecuteDelete rows: 1

Option Strict On

Imports Microsoft.EntityFrameworkCore
Imports Shelf.Core

Module Queries
    Sub Main()
        Using connection = OpenDatabase()
            Using context As New ShelfContext(CreateOptions(connection))
                Dim herbert = context.Authors.Single(Function(a) a.Id = 1)
                Dim gibson = context.Authors.Single(Function(a) a.Id = 2)
                context.Books.AddRange(
                    New BookEntity With {.Title = "Dune", .Year = 1965, .Isbn = Isbn.Parse("9780441172719"), .Author = herbert},
                    New BookEntity With {.Title = "Dune Messiah", .Year = 1969, .Isbn = Isbn.Parse("9780593098233"), .Author = herbert},
                    New BookEntity With {.Title = "Neuromancer", .Year = 1984, .Isbn = Isbn.Parse("9780441569595"), .Author = gibson},
                    New BookEntity With {.Title = "Count Zero", .Year = 1986, .Isbn = Isbn.Parse("9780441117734"), .Author = gibson})
                context.SaveChanges()
            End Using

            Using context As New ShelfContext(CreateOptions(connection))
                Console.WriteLine("--- query syntax with Group By")
                Dim perAuthor = From b In context.Books
                                Group By Name = b.Author.Name Into Count(), Latest = Max(b.Year)
                                Order By Name
                Console.WriteLine(perAuthor.ToQueryString())
                For Each row In perAuthor
                    Console.WriteLine($"{row.Name}: {row.Count} book(s), latest {row.Latest}")
                Next

                Console.WriteLine("--- Aggregate")
                Dim total = Aggregate b In context.Books Into Sum(b.Year)
                Console.WriteLine("Sum of years: " & total)

                Console.WriteLine("--- method syntax with paging")
                Dim page = context.Books.OrderBy(Function(b) b.Title).Skip(1).Take(2).Select(Function(b) b.Title).ToList()
                Console.WriteLine(String.Join(", ", page))

                Console.WriteLine("--- Include")
                Dim authors = context.Authors.Include(Function(a) a.Books).OrderBy(Function(a) a.Name).ToList()
                For Each a In authors
                    Console.WriteLine(a.Name & ": " & String.Join(", ", a.Books.Select(Function(b) b.Title)))
                Next

                Console.WriteLine("--- EF.Functions.Like")
                Dim likeResult = context.Books.Where(Function(b) EF.Functions.Like(b.Title, "Dune%")).Count()
                Console.WriteLine("Titles like Dune%: " & likeResult)

                Console.WriteLine("--- the Visual Basic Like operator")
                Try
                    Dim vbLike = context.Books.Where(Function(b) b.Title Like "Dune*").Count()
                    Console.WriteLine(vbLike)
                Catch ex As InvalidOperationException
                    Dim start = ex.Message.IndexOf("Translation of")
                    Dim finish = ex.Message.IndexOf(" If this method", start)
                    Console.WriteLine(ex.GetType().Name & ": " & ex.Message.Substring(start, finish - start))
                End Try
            End Using
        End Using
    End Sub
End Module

--- query syntax with Group By
SELECT "a"."Name", COUNT(*) AS "Count", MAX("b"."Year") AS "Latest"
FROM "Books" AS "b"
INNER JOIN "Authors" AS "a" ON "b"."AuthorId" = "a"."Id"
GROUP BY "a"."Name"
ORDER BY "a"."Name"
Frank Herbert: 2 book(s), latest 1969
William Gibson: 2 book(s), latest 1986
--- Aggregate
Sum of years: 7904
--- method syntax with paging
Dune, Dune Messiah
--- Include
Frank Herbert: Dune, Dune Messiah
William Gibson: Neuromancer, Count Zero
--- EF.Functions.Like
Titles like Dune%: 2
--- the Visual Basic Like operator
InvalidOperationException: Translation of method 'Microsoft.VisualBasic.CompilerServices.LikeOperator.LikeString' failed.

Option Compare Text
Option Strict On

Imports Microsoft.EntityFrameworkCore
Imports Shelf.Core

Module CompareText
    Sub Main()
        Using connection = OpenDatabase()
            Using context As New ShelfContext(CreateOptions(connection))
                context.Books.Add(New BookEntity With {.Title = "Dune", .Year = 1965, .Isbn = Isbn.Parse("9780441172719"), .AuthorId = 1})
                context.SaveChanges()

                Console.WriteLine("In memory, Option Compare Text: " & ("dune" = "DUNE"))
                Try
                    Dim count = context.Books.Where(Function(b) b.Title = "dune").Count()
                    Console.WriteLine("Rows: " & count)
                Catch ex As InvalidOperationException
                    Dim start = ex.Message.IndexOf("Translation of")
                    Dim finish = ex.Message.IndexOf(" If this method", start)
                    Console.WriteLine(ex.Message.Substring(start, finish - start))
                End Try
                Console.WriteLine("Rows with EF.Functions.Like: " & context.Books.Where(Function(b) EF.Functions.Like(b.Title, "dune")).Count())
            End Using
        End Using
    End Sub
End Module

In memory, Option Compare Text: True
Translation of method 'string.Compare' failed.
Rows with EF.Functions.Like: 1

Option Strict On

Imports Microsoft.EntityFrameworkCore
Imports Microsoft.EntityFrameworkCore.ChangeTracking
Imports Shelf.Core

Module Advanced
    Sub Main()
        RunAsync().GetAwaiter().GetResult()
    End Sub

    Private Async Function RunAsync() As Task
        Using connection = OpenDatabase()
            Using context As New ShelfContext(CreateOptions(connection))
                Dim options = CreateOptions(connection)

                context.Books.Add(New BookEntity With {.Title = "Dune", .Year = 1965, .Isbn = Isbn.Parse("9780441172719"), .AuthorId = 1})
                Console.WriteLine("Saved: " & Await context.SaveChangesAsync())
                Dim titles = Await context.Books.Select(Function(b) b.Title).ToListAsync()
                Console.WriteLine("Titles: " & String.Join(", ", titles))

                Console.WriteLine("--- explicit transaction")
                Dim transaction = Await context.Database.BeginTransactionAsync()
                Try
                    context.Books.Add(New BookEntity With {.Title = "Temporary", .Year = 2000, .Isbn = Isbn.Parse("9780441569595"), .AuthorId = 2})
                    Await context.SaveChangesAsync()
                    Console.WriteLine("Inside transaction: " & Await context.Books.CountAsync())
                    Await transaction.RollbackAsync()
                Finally
                    transaction.Dispose()
                End Try
                context.ChangeTracker.Clear()
                Console.WriteLine("After rollback: " & Await context.Books.CountAsync())

                Console.WriteLine("--- optimistic concurrency")
                Using first As New ShelfContext(options), second As New ShelfContext(options)
                    Dim a = Await first.Books.FirstAsync()
                    Dim b = Await second.Books.FirstAsync()
                    a.Title = "Dune (edited by first)"
                    a.Version += 1
                    Await first.SaveChangesAsync()
                    b.Title = "Dune (edited by second)"
                    b.Version += 1
                    Dim conflict As EntityEntry = Nothing
                    Try
                        Await second.SaveChangesAsync()
                    Catch ex As DbUpdateConcurrencyException
                        Console.WriteLine("DbUpdateConcurrencyException, entries: " & ex.Entries.Count)
                        conflict = ex.Entries(0)
                    End Try
                    If conflict IsNot Nothing Then
                        Dim stored = Await conflict.GetDatabaseValuesAsync()
                        Console.WriteLine("Database has: " & CStr(stored(NameOf(BookEntity.Title))))
                    End If
                End Using

                Console.WriteLine("--- raw SQL")
                Dim year = 1960
                Dim raw = Await context.Books.FromSql($"SELECT * FROM Books WHERE Year > {year}").ToListAsync()
                Console.WriteLine("FromSql rows: " & raw.Count)
                Dim affected = Await context.Database.ExecuteSqlAsync($"UPDATE Books SET Year = Year + 1 WHERE Id = {raw(0).Id}")
                Console.WriteLine("ExecuteSql affected: " & affected)
            End Using
        End Using
    End Function
End Module

Saved: 1
Titles: Dune
--- explicit transaction
Inside transaction: 2
After rollback: 1
--- optimistic concurrency
DbUpdateConcurrencyException, entries: 1
Database has: Dune (edited by first)
--- raw SQL
FromSql rows: 1
ExecuteSql affected: 1

Option Strict On

Imports Microsoft.Data.Sqlite
Imports Microsoft.EntityFrameworkCore
Imports Shelf.Core

Module Seeding
    Sub Main()
        Using connection As New SqliteConnection("Data Source=:memory:")
            connection.Open()
            Dim builder As New DbContextOptionsBuilder(Of ShelfContext)()
            builder.UseSqlite(connection)
            builder.UseSeeding(
                Sub(context, storeCreated)
                    Dim set1 = context.Set(Of BookEntity)()
                    If Not set1.Any(Function(b) b.Title = "Dune") Then
                        set1.Add(New BookEntity With {.Title = "Dune", .Year = 1965, .Isbn = Isbn.Parse("9780441172719"), .AuthorId = 1})
                        context.SaveChanges()
                        Console.WriteLine("UseSeeding added Dune (store created by this call: " & storeCreated & ")")
                    End If
                End Sub)

            Using context As New ShelfContext(builder.Options)
                context.Database.EnsureCreated()
                Console.WriteLine("Books: " & context.Books.Count())
            End Using
        End Using
    End Sub
End Module

UseSeeding added Dune (store created by this call: True)
Books: 1

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net10.0</TargetFramework>
    <NuGetAudit>false</NuGetAudit>
  </PropertyGroup>
  <ItemGroup>
    <ProjectReference Include="..\MigData\MigData.vbproj" />
  </ItemGroup>
</Project>

Option Strict On

Imports Microsoft.Data.Sqlite
Imports Microsoft.EntityFrameworkCore
Imports Microsoft.EntityFrameworkCore.Design

Public Class ShelfContextFactory
    Implements IDesignTimeDbContextFactory(Of ShelfContext)

    Public Function CreateDbContext(args As String()) As ShelfContext Implements IDesignTimeDbContextFactory(Of ShelfContext).CreateDbContext
        Return New ShelfContext(CreateOptions("Data Source=shelf.db"))
    End Function

    Public Shared Function CreateOptions(connectionString As String) As DbContextOptions(Of ShelfContext)
        Return New DbContextOptionsBuilder(Of ShelfContext)().
            UseSqlite(connectionString, Sub(o) o.MigrationsAssembly("Shelf.Migrations")).Options
    End Function
End Class

Module Program
    Sub Main()
        Using connection As New SqliteConnection("Data Source=:memory:")
            connection.Open()
            Dim options = New DbContextOptionsBuilder(Of ShelfContext)().
                UseSqlite(connection, Sub(o) o.MigrationsAssembly("Shelf.Migrations")).Options
            Using context As New ShelfContext(options)
                Console.WriteLine("Pending before: " & String.Join(", ", context.Database.GetPendingMigrations().Select(Function(m) m.Substring(m.IndexOf("_"c) + 1))))
                context.Database.Migrate()
                Console.WriteLine("Applied after:  " & String.Join(", ", context.Database.GetAppliedMigrations().Select(Function(m) m.Substring(m.IndexOf("_"c) + 1))))
                Console.WriteLine("Authors seeded: " & context.Authors.Count())
            End Using
        End Using
    End Sub
End Module

$ dotnet ef migrations add Initial --project MigCs --startup-project MigApp
Build started...
Build succeeded.
Done. To undo this action, use 'ef migrations remove'

$ ls MigCs/Migrations
20261019215308_Initial.Designer.cs
20261019215308_Initial.cs
ShelfContextModelSnapshot.cs

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

#pragma warning disable CA1814 // Prefer jagged arrays over multidimensional

namespace Shelf.Migrations.Migrations
{
    /// <inheritdoc />
    public partial class Initial : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Authors",
                columns: table => new
                {
                    Id = table.Column<int>(type: "INTEGER", nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    Name = table.Column<string>(type: "TEXT", maxLength: 100, nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Authors", x => x.Id);
                });

            migrationBuilder.CreateTable(

Pending before: Initial
Applied after:  Initial
Authors seeded: 2

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" TEXT NOT NULL CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY,
    "ProductVersion" TEXT NOT NULL
);

BEGIN TRANSACTION;
CREATE TABLE "Authors" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Authors" PRIMARY KEY AUTOINCREMENT,
    "Name" TEXT NOT NULL
);

CREATE TABLE "Books" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Books" PRIMARY KEY AUTOINCREMENT,
    "Title" TEXT NOT NULL,
    "Year" INTEGER NOT NULL,
    "Isbn" TEXT NOT NULL,
    "Version" INTEGER NOT NULL,
    "AuthorId" INTEGER NOT NULL,
    CONSTRAINT "FK_Books_Authors_AuthorId" FOREIGN KEY ("AuthorId") REFERENCES "Authors" ("Id") ON DELETE CASCADE
);

INSERT INTO "Authors" ("Id", "Name")
VALUES (1, 'Frank Herbert');
SELECT changes();

INSERT INTO "Authors" ("Id", "Name")
VALUES (2, 'William Gibson');
SELECT changes();

warning NU1608: Detected package version outside of dependency constraint: EntityFrameworkCore.VisualBasic 8.0.0 requires Microsoft.EntityFrameworkCore.Design (>= 8.0.2 && < 9.0.0) but version Microsoft.EntityFrameworkCore.Design 10.0.13 was resolved.
warning NU1608: Detected package version outside of dependency constraint: Microsoft.CodeAnalysis.VisualBasic 4.5.0 requires Microsoft.CodeAnalysis.Common (= 4.5.0) but version Microsoft.CodeAnalysis.Common 5.0.0 was resolved.

$ dotnet ef migrations add Initial --no-build
Method 'GetForEachSymbols' in type 'Microsoft.CodeAnalysis.VisualBasic.Utilities.SpeculationAnalyzer' from assembly 'Microsoft.CodeAnalysis.VisualBasic.Workspaces, Version=4.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' does not have an implementation.
Could not load type 'Microsoft.CodeAnalysis.LanguageService.AbstractBlockFacts' from assembly 'Microsoft.CodeAnalysis.Workspaces, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.