Creating/Updating database schema from a console application

Question

I have a console application that interacts with a backend database using EF. Core. I have the DBContext that looks as follows.

class HRDepartmentContext : DbContext
{
    public DbSet<Employee> Employee { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=somepath\\employees.db");
}

It's consumed in a class that looks as follows.

class EmployeeManager
{
    public  CreateEmployee(string firstname, string lastname)
    {
        using (var db = new EmployeeContext())
        {
            db.Database.EnsureCreated();

            // logic for employee creation
        }
    }

    // other methods for delete/update the employees
    public UpdateEmployee(...) {...}
    public DeleteEmployee(...) {...}
}

My questions.

a) What is the best way of calling EnsureCreated method as I don't want to call it in the individual Create/Update/Delete Employee methods.

b) What is the best way of handling schema changes scenarios in future versions of the application? Reading the documentation, it does not look like EsureCreated will be able to handle the scenario. Obviously upgrade schema need to be performed without any existing data loss.

UPDATE: I want to avoid using any EF command lines for migration in case of schema changes. I prefer the code to handle it.

I think this will solve your problem, [https://stackoverflow.com/questions/13089448/how-to-check-if-database-schema-matches-entity-framework-schema](https://stackoverflow.com/questions/13089448/how-to-check-if-database-schema-matches-entity-framework-schema) — Ahmed Salim, Feb 03 '20 at 02:18

score 2 · Accepted Answer · answered Feb 03 '20 at 02:20

Personally in netcore 3 I would use the generic IHost in your console application. Then configure the database services using dependency injection. And an IHostedService to create or migrate the database during startup.

    public class Program {
        public static void ConfigureServices(HostBuilderContext context, IServiceCollection serviceCollection)
        {
            serviceCollection.AddEntityFrameworkSqlite();
            serviceCollection.AddHostedService<DatabaseStartup>();

            serviceCollection.AddDbContextPool<HRDepartmentContext>(o =>
            {
                o.UseSqlite("Data Source=somepath\\employees.db");
            });
            // configure other services here
        }

        public static async Task<int> Main(string[] args)
        {
            using (var host = CreateHostBuilder(args).Build())
            {
                await host.StartAsync();
                var lifetime = host.Services.GetRequiredService<IHostApplicationLifetime>();

                // insert other console app code here

                lifetime.StopApplication();
                await host.WaitForShutdownAsync();
            }
            return 0;
        }

        public static IHostBuilder CreateHostBuilder(string[] args) =>
            Host
                .CreateDefaultBuilder(args)
                .UseConsoleLifetime()
                .ConfigureServices(ConfigureServices);
    }

    public class DatabaseStartup : IHostedService {
        private readonly IServiceProvider serviceProvider;
        public DatabaseStartup(IServiceProvider serviceProvider){
            this.serviceProvider = serviceProvider;
        }

        public async Task StartAsync(CancellationToken cancellationToken)
        {
            using (var scope = serviceProvider.CreateScope())
            {
                var db = scope.ServiceProvider.GetRequiredService<HRDepartmentContext>();
                await db.Database.EnsureCreated();
                // or 
                await db.Database.MigrateAsync();
            }
        }

        public Task StopAsync(CancellationToken cancellationToken) => Task.CompletedTask;
    }

Since your console application then has a CreateHostBuilder, the entity framework command line tools can discover any custom configuration when creating migrations.

Thanks for the answer. One thing I forgot to mention in my question is that I want to avoid using any EF command line for migration. I prefer the code to handle it. Does that impact the solution you proposed here? — whoami, Feb 03 '20 at 02:26
Automatic migration isn't supported by EF Core, and there is no plan to (see https://github.com/dotnet/efcore/issues/6214). However, it is possible to build an un-supported process, which I just happen to have already done, https://gist.github.com/lakeman/1509f790ead00a884961865b5c79b630. Use at your own risk. — Jeremy Lakeman, Feb 03 '20 at 02:29
I cannot resolve the Host from the CreateHostBuilder from the code you provided. This caused a compilation error The name 'Host' does not exist in the current context. Any idea how to resolve it? I am using .NET Core 3.1. — whoami, Feb 03 '20 at 03:58

score 0 · Answer 2 · answered Feb 03 '20 at 02:20

0

Also to create and employee you can use the class constructor, or the object initializer

answered Feb 03 '20 at 02:20

Ahmed Salim

71
6

Sorry but I don't understand how this helps to answer the question I asked. Do you mind elaborating a little? – whoami Feb 03 '20 at 02:35

Creating/Updating database schema from a console application

2 Answers2