Create database from model using Entity Framework Core and ASP.NET Core

While working with Entity Framework Core and ASP.NET Core you typically create EF Core model consisting of a custom DbContext and entity classes. If your database already exists, it can be mapped with the EF Core model. However, if the database doesn't exist already, you would want it to be created. Of course, you can create the database manually by looking at the EF Core model and creating tables accordingly. Wouldn't it be nice if the database gets created automatically for you based on the EF Core model your created? To that end this article discusses just that.

To begin with, create a new ASP.NET Core MVC application (you can create Razor Pages app if you want).

Now add these two NuGet packages to the project:

• Microsoft.EntityFrameworkCore.SqlServer
• Microsoft.EntityFrameworkCore.Design

The first NuGet package is the EF Core provider for SQL Server whereas the second NuGet package is needed when you use EF Core migrations (discussed later). 

Then add a new folder to the project called DataAccess. The DataAccess folder stores the DbContext and entity classes required for the example discussed in this article.

Then add two classes - Customer and Order - in the DataAccess folder. The following code shows Customer class.

[Table("Customers")]
public class Customer
{
    [Column("CustomerID")]
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption
.Identity)]
    [Required]
    public int CustomerID { get; set; }

    [Column("CustomerName")]
    [Required]
    [StringLength(100)]
    public string FirstName { get; set; }

    [ForeignKey("CustomerID")]
    public virtual List<Order> Orders { get; set; }
}

As you can see the Customer class is a POCO that uses data annotations to indicate the primary key, identity, and foreign key. Especially notice the Orders property that is intended to store all the orders belonging to a customer. Although we won't use this class to perform CRUD operations in this example, we still added Orders property to illustrate how the database schema is created for us.

On the same lines add Order class as shown below:

public class Order
{
    [Column("OrderID")]
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption
.Identity)]
    [Required]
    public int OrderID { get; set; }

    [Column("OrderDate")]
    [Required]
    public DateTime OrderDate { get; set; }

    [Required]
    public int CustomerID { get; set; }
}

The CustomerID in the Order class indicates a CustomerID this order belongs to.

Next, add a custom DbContext class named AppDbContext and write the following code in it:

public class AppDbContext:DbContext
{
    public AppDbContext(DbContextOptions
<AppDbContext> options) : base(options)
    {
    }
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }
}

As you can see, AppDbContext contains two DbSet properties - Customers and Orders.

This completes a sample EF Core model required for our examples. The following figure shows the DataAccess folder after adding these classes.

Build the project just to ensure that compiler is happy with our code.

Now open the appsetttings.json file and store a database connection string as shown below:

"ConnectionStrings": {
  "MyNewDatabase": "data source=.;
initial catalog=MyNewDb;
integrated security=true"
}

Note that the database specified in the connection string (MyNewDatabase) doesn't exists at this point. It will be created for you in the following sections.

At this point, our EF Core model is ready and you can register the DbContext with the DI framework. To do that open the Startup class and add this code to the ConfigureServices() method.

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();
    services.AddDbContext<AppDbContext>
   (o=>o.UseSqlServer(Configuration.
    GetConnectionString("MyNewDatabase")));
}

The above code uses AddDbContext() method to register AppDbContext with the DI system of ASP.NET Core. Notice that the database connection string stored in the appsettings.json file is supplied to the UseSqlServer() method.

Creating database using EnsureCreated() method

Now that the EF Core model is ready, let's try to create the required database using EnsureCreated() method. This technique is a code based technique and works great for quick and simple database creation scenarios. This approach doesn't use EF Core Migrations. All it does is to create a database schema based on your EF Core model. If a database already exists then no action is taken, otherwise the database is created for you.

To use the EnsureCreated() method go to the Configure() method and add the following code:

public void Configure(IApplicationBuilder app, 
IWebHostEnvironment env, AppDbContext db)
{
    if (env.IsDevelopment())
    {
        app.UseDeveloperExceptionPage();
    }
    else
    {
        app.UseExceptionHandler("/Home/Error");
    }

    db.Database.EnsureCreated();

    app.UseStaticFiles();
    app.UseRouting();
    app.UseAuthorization();
    app.UseEndpoints(endpoints =>
    {
        endpoints.MapControllerRoute(
            name: "default",
            pattern: "{controller=Home}/
{action=Index}/{id?}");
    });
}

Notice the code marked in bold letters. It injects AppDbContext instance to the Configure() method so that the instance can be used inside the method. Inside, the code calls the EnsureCreated() method to ensure that the database for the DbContext is created if it doesn't exist already.

Now run the application and check whether the MyNewDb database gets created for you. The following figure shows this newly created database in SQL Server Object Explorer.

Notice how various tables, columns, column data types, primary keys, foreign keys are picked based on the EF Core model.

Creating database using EF Core Migrations and .NET Core CLI

The database creation approach discussed in the previous section is suitable for quick testing. A more realistic and recommended approach is to use EF Core Migrations to create and update database. This approach can not only create a database from ground-up, it can also update an existing database with changes (existing data is also preserved). Using EF Core migrations is a two step process - firstly you create a migration and secondly you apply that migration to a database.

Let's see how these steps can be performed in our example.

Open Visual Studio command prompt and navigate to the ASP.NET Core project's root folder.

Then install a .NET Core CLI tool called dotnet-ef. This is typically a one time operation. If you have already installed dotnet-ef tool then you don't need to install it again.

Issue the following command to install dotnet-ef tool on your machine.

> dotnet tool install dotnet-ef --global

The above command installs the latest stable version of dotnet-ef tool globally on your machine. You can also use --version option to specify a particular version of the tool.

You can check if the tool is installed or not by issuing the following command:

> dotnet tool list --global

Next, you need to create a migration issuing the following command:

> dotnet ef migrations add MyBaseMigration 
  --context AppDbContext

The above command creates a new migration named MyBaseMigration and uses a DbContext as specified by the --context option.

If all goes well, you will find Migrations folder getting created inside your ASP.NET Core project with certain class files.

You don't need to change anything from these files. They are used by the dotnet-ef tool in the next step.

Now, issue the following command to apply the migration you just created.

> dotnet ef database update

This command will create the database (if it doesn't exist already) and create the table as per the EF Core model definition. If the database already exists, it will be updated (if needed) to reflect the EF Core model definition.

After executing this command successfully you should see MyNewDb database getting created as before.

Notice that this time __EFMigrationsHistory table is also added that is used internally by EF Core migrations.

Applying EF Core migrations programmatically

In the preceding section you created and applied the migrations using .NET Core CLI. You can also apply the created migrations via code. The following code shows how:

public void Configure(IApplicationBuilder app, 
IWebHostEnvironment env, AppDbContext db)
{
    if (env.IsDevelopment())
    {
        app.UseDeveloperExceptionPage();
    }
    else
    {
        app.UseExceptionHandler("/Home/Error");
    }

    db.Database.Migrate();

    app.UseStaticFiles();
    app.UseRouting();
    app.UseAuthorization();
    app.UseEndpoints(endpoints =>
    {
        endpoints.MapControllerRoute(
            name: "default",
            pattern: "{controller=Home}/
{action=Index}/{id?}");
    });
}

Here, you called the Migrate() method to apply the migration. The final outcome remains identical to the previous example.

This article taught you how to create database from model. To learn how to create model from database go here.

That's it for now! Keep coding!!