C#•9mo ago

.NET EF - How to generate a new database in runtime (dynamically)?

Hello, I have the following logic, a user can create multiple projects via an API call. For each project I would need to create a new database from a migration, so each project would have different database. How is this achievable?

13 Replies

ben SKVU4-9YBC•9mo ago

In EF, databases are by default created at runtime anyway, when you start up the application, if they don't already exist. You can just go about it in the same manner, passing your dynamic database name into DbContextOptions and ensure it's being created with ctx.Database.EnsureCreatedAsync()

CoreOP•9mo ago

Thank you! I will look into this

ben SKVU4-9YBC•9mo ago

No problem!

ben SKVU4-9YBC•9mo ago

Jimmacle•9mo ago

EnsureCreated shouldn't be used with migrations, and EF doesn't do any kind of migration or schema modification by default at runtime you have to pick one or the other specifically, it ignores migrations and creates tables based on the current model so trying to run migrations after that will almost definitely fail context.Database.Migrate()/MigrateAsync(); is the closest migration-compatible way to do this i'd just use a little raw ADO.NET to create the database before running the migrations

ben SKVU4-9YBC•9mo ago

You're assuming this is code first migrations?

Jimmacle•9mo ago

not assuming anything, he said he wants to create a brand new DB from migrations at runtime which would look roughly like

    private static void SetupNewDb(string dbName)
    {
        using var connection = new SqlConnection(/* connection string to master database */);
        using var command = new SqlCommand($"CREATE DATABASE {dbName};", connection);
        command.ExecuteNonQuery();
        using var context = new DbContext(/* options with connection string to new database*/);
        context.Database.Migrate();
    }

    private static void SetupNewDb(string dbName)
    {
        using var connection = new SqlConnection(/* connection string to master database */);
        using var command = new SqlCommand($"CREATE DATABASE {dbName};", connection);
        command.ExecuteNonQuery();
        using var context = new DbContext(/* options with connection string to new database*/);
        context.Database.Migrate();
    }

definitely want to sanitize that database name if it's generated from user input

ben SKVU4-9YBC•9mo ago

That would run all migrations, including the initial one, database would already exist?

Jimmacle•9mo ago

the initial migration doesn't create the database and you can't write a connection string with an initial database that doesn't already exist anyway

ben SKVU4-9YBC•9mo ago

Huh? I think you can?

Jimmacle•9mo ago

i stand corrected, looks like EF core at least will try to create the database if you try to run migrations on one that doesn't exist so my code is useless, just context.Database.Migrate() is all you need

ben SKVU4-9YBC•9mo ago

Yep, I didn't really extend my answer to migrations, so thanks!

Angius•9mo ago

Why exactly do you want to use a separate database per project? Instead of, you know, using the relational part of relational database and simply having a projects table related to the user?

Gaming

Programming

.NET EF - How to generate a new database in runtime (dynamically)?