How can I do an in-memory SQL Server for integration testing?

EF Core Migrations:

"The migrations feature in EF Core provides a way to incrementally update the database schema to keep it in sync with the application's data model while preserving existing data in the database."

Create and Drop APIs:

"The EnsureCreated and EnsureDeleted methods provide a lightweight alternative to Migrations for managing the database schema. These methods are useful in scenarios when the data is transient and can be dropped when the schema changes. For example during prototyping, in tests, or for local caches."

to create your tables at runtime.

And then use one of the Data Seeding techniques:

Data seeding is the process of populating a database with an initial set of data. There are several ways this can be accomplished in EF Core:

• Model seed data
• Manual migration customization
• Custom initialization logic

to populate them with known data.