Is there a connection string for InMemoryDatabase?

Question

So we are using the CQRS pattern in our applications using MediatR. I am working on a basic CRUD API, with the following stipulations:

• The GET call needs to use QueryHandlers, Dapper, SQL to retrieve data from the database.
• The PUT/POST/DELETE calls need to use CommandHandlers/Entity Framework to modify the database.

At this point I have it working as expected. However I am finding testing to be a nightmare because I can't figure out the connection string I need to be able to access the in-memory database.

I have tried connectionString = "Data Source=:memory:;Mode=Memory;Cache=Shared"; and various permutations of those values and none work.

I have looked at SQLite, but it doesn't like me trying to register two different DBs in the startup (SQLite for reads and Microsoft.EntityFrameworkCore.InMemory for writes), also the objects I am trying to get have primary keys of bigint/long and SQLite appears to only be able to handle integer/int type primary keys because it will throw the "Only primary keys of type integer can have autoincrement".

I am setting up the XUnit tests with code similar to the following:

public class CustomWebApplicationFactory<TStartup> : WebApplicationFactory<TStartup> where TStartup : class
{
   protected override void ConfigureWebHost(IWebHostBuilder builder)
   {
        builder.ConfigureServices(svcs => 
        {
           services.AddDbContextPool<MyDbContext>(opts =>
           {
              options.UseInMemoryDatabase("MyTestDB");
           });
         
           using var scope = svcs.BuildServiceProvider().CreateScope();
           var scopedSvcs = scope.ServiceProvider;
           var dbSvc = scopedSvcs.GetRequiredService<MyDbContext>();

           dbSvc.Database.EnsureCreated();


           // Do DB Seeding stuff
        });
    }
}

And in the query handlers I'm using SqlConnection as below:

public class GetModelHandler : IRequestHandler<GetModelRequest, MyModel>
{
   private readonly string _connString;


   public GetModelHandler(string connString) { _connString = connString; }


   public async Task<MyModel> Handle(GetModelRequest req, CancellationToken token)
   {
       using (var conn = new SqlConnection(_connectionString)) 
       {
          conn.Open();
          // set command info and make call
       }
   }
}

So, the TL;DR question is... "Is there a connection string that can be used to hit the Entity Framework Core In-Memory DB, and if so, how I can find it?"

Answer 1

When you run a database in memory the data within it will be lost once the connection is closed. If you want to keep the data you can create a DB file using this

in the startup.cs file

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{

  if (env.IsDevelopment())
  {
    app.UseDeveloperExceptionPage();
    app.UseMigrationsEndPoint(); //package Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore

...

    var connection = new SqliteConnectionStringBuilder(Configuration.GetConnectionString("yourConnection"));
    try
    {

      // Keep this line if you want to recreate the database every time you run your app
      if (File.Exists(connection.DataSource)) File.Delete(connection.DataSource);


      var connectionFolder = Directory.GetParent(connection.DataSource);
      if (!Directory.Exists(connectionFolder.FullName)) Directory.CreateDirectory(connectionFolder.FullName); // Create folder

      File.WriteAllBytes(connection.DataSource, new byte[0]); //Create database

    }
    catch
    {

    }
...
  }
  
var context = app.ApplicationServices.GetRequiredService<IdentityContext>();
context.Database.EnsureCreated();

...
}

For the code above work, you need to deactivate the validation of scope from your dependency injector container. You can do this in the program.cs

program.cs

public static IHostBuilder CreateHostBuilder(string[] args) =>
            Host.CreateDefaultBuilder(args)
                .ConfigureWebHostDefaults(webBuilder =>
                {
                    webBuilder.UseStartup<Startup>();
                })
            .UseDefaultServiceProvider(options => options.ValidateScopes = false); // Add this line.

appsettings.json

{
 ...

  "ConnectionStrings": {
    "yourConnection": "Data Source=./App_Data/appdb.db;Cache=Shared;Mode=ReadWrite;"
  }
}