Data Access in ASP.NET Core using EF Core (Database First)

In my previous tutorial Data Access in ASP.NET Core 5 using EF Core 5 (Code First), I covered the basics of Entity Framework and DbContext and showed you how to use the Code First technique to generate a new database using the entity models. You can use Code First approach if you are starting a new project but it is not always the case in the real world. Sometimes you already have a database and you have to use Entity Framework with an existing database. Entity Framework Core support this approach as well and in this tutorial, I will demonstrate different techniques for generating DbContext and model classes from an existing database.

Download Source Code

Getting Started with EF Core using Existing Database

For this tutorial, I will be using the following database that has the Customers, Products, Orders, and ProductOrders table with a standard parent-child relationship.

Create a standard ASP.NET Core 5 MVC Web Application and install Entity Framework Core and SQL Server Data Provider by installing Microsoft.EntityFrameworkCore.SqlServer NuGet package. We also need to install Microsoft.EntityFrameworkCore.Design and Microsoft.EntityFrameworkCore.Tools to perform some Entity Framework Core design-time development tasks such as generating the DbContext and Entity Models from an existing database.

Generating Models from an Existing Database using EF Core

Entity Framework Core doesn’t support the visual designer (.edmx file) for creating models which we were using in the old version of Entity Framework (EF 6). In Entity Framework Core, we need to generate Model classes from an existing database using the following two options.

1. If you are using Visual Studio, you can use the Package Manager Console (PMC) tools for Entity Framework Core
2. If you are not using Visual Studio, you can use the cross-platform EF Core Command-line Tools from a command prompt

For this tutorial, I am using Package Manager Console Tools so the main command you need to reverse engineer the models from the existing database schema is Scaffold-DbContext. Please note that for Scaffold-DbContext to generate an entity type, the database table must have a primary key. This command has many options to customize the generated code. The following table shows all available parameters for Scaffold-DbContextcommand.

Let’s try to use some of the parameters from the above table. Open the Package Manager Console of the project and copy/paste the following command and press Enter.

Scaffold-DbContext -Connection "Server=DESKTOP-6SDKTRC; Database=OnlineShopDb; Trusted_Connection=True; MultipleActiveResultSets=true;" -Provider Microsoft.EntityFrameworkCore.SqlServer -OutputDir "Models" -ContextDir "Data" -Context "OnlineShopDbContext"

I am providing connection string as per my database so you need to change the server and database name in the connection string as per your machine. I also specified SQL Server provider because I am using SQL Server database. I want to generate all my model classes in the Models folder and I want my DbContext to be generated in the Data folder with the name OnlineShopDbContext. Once the command will execute you will see the generated classes in Solution Explorer.

The generated model classes such as Product, Customer, etc. are simple C# classes with the properties mapped to database table columns. Here is the example of a Customer class generated from the Customer table in the database.

public partial class Customer
{
    public Customer()
    {
        Orders = new HashSet<Order>();
    }

    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
    public string Mobile { get; set; }

    public virtual ICollection<Order> Orders { get; set; }
}

The code generated in the OnlineShopDbContext class is more interesting. The class is inheriting from DbContext class as expected and it has DbSet properties for each table in the database.

public partial class OnlineShopDbContext : DbContext
{
    public OnlineShopDbContext()
    {
    }

    public OnlineShopDbContext(DbContextOptions<OnlineShopDbContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Customer> Customers { get; set; }
    public virtual DbSet<Order> Orders { get; set; }
    public virtual DbSet<Product> Products { get; set; }
    public virtual DbSet<ProductOrder> ProductOrders { get; set; }

    ...
}

There is also an overridden method OnConfiguring which is configuring the Entity Framework using the UseSqlServer method. You can see that a warning is also generated telling you that you should not save sensitive information such as connection string in the code.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    if (!optionsBuilder.IsConfigured)
    {
        #warning To protect potentially sensitive information in your connection string, 
        you should move it out of source code. You can avoid scaffolding the connection 
        string by using the Name= syntax to read it from configuration - see 
        https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on 
        storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.

        optionsBuilder.UseSqlServer("Server=DESKTOP-6SDKTRC; Database=OnlineShopDb; Trusted_Connection=True; MultipleActiveResultSets=true;");
    }
}

Let’s first fix this issue as we want to follow the best practices. Run the following command with two additional parameters –NoOnConfiguring and –Force and you will see that this time OnConfiguring method will not be generated.

Scaffold-DbContext -Connection "Server=DESKTOP-6SDKTRC; Database=OnlineShopDb; Trusted_Connection=True; MultipleActiveResultSets=true;" -Provider Microsoft.EntityFrameworkCore.SqlServer -OutputDir "Models" -ContextDir "Data" -Context "OnlineShopDbContext" -NoOnConfiguring –Force

You can specify the connection string in appsettings.json file as follows

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=DESKTOP-6SDKTRC; Database=OnlineShopDb; Trusted_Connection=True; MultipleActiveResultSets=true"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "\*"
}

Entity Framework provider can be configured in ConfigureServices method of Startup.cs file as shown below:

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();

    services.AddDbContext<OnlineShopDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
}

If you want to learn more about ASP.NET Core Configuration then read my post A Step by Step Guide for ASP.NET Core Configuration.

The next overridden method in the OnlineShopDbContext class is OnModelCreating which is using the Fluent API to configure and map database tables and columns with classes and properties. If you want Entity Framework to configure mappings using DataAnnotations then you can use the –DataAnnotations parameter of Scaffold-DbContext command.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasAnnotation("Relational:Collation", "SQL_Latin1_General_CP1_CI_AS");

    modelBuilder.Entity<Customer>(entity =>
    {
        entity.Property(e => e.FirstName).IsRequired();

        entity.Property(e => e.LastName).IsRequired();
    });

    ...

    OnModelCreatingPartial(modelBuilder);
}

Extending EF Core Generated DbContext Class

In a real project, you would probably add more database tables or columns during development and every time you will generate model classes and DbContext class using Scaffold-DbContext, you will lose all your custom changes and configurations you have done in the OnModelCreating method. It is not recommended to add custom code in overridden method OnModelCreating and that’s why the Scaffold-DbContext command generates a partial method for us.

partial void OnModelCreatingPartial(ModelBuilder modelBuilder);

You can also see that this method is called from inside OnModelCreating method as follows:

OnModelCreatingPartial(modelBuilder);

To extend generated DbContext class, you can create a partial class in your project with the same name and define your custom configurations inside the overridden OnModelCreatingPartial method. Add a file named _OnlineShopDbContext.cs in the Data folder and inside the file define the partial OnlineShopDbContext class by inheriting the same DbContext class. Inside OnModelCreatingPartial you can specify the custom configurations as shown in the code snippet below:

public partial class OnlineShopDbContext : DbContext
{
    partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>(entity =>
        {
            entity.Property(e => e.Phone).IsRequired();
        });
    }
}

Displaying Data in ASP.NET Core using EF Core

In this final section of the tutorial, I will show you how you can use the generated OnlineShopDbContext in ASP.NET Core MVC to display data from the existing database. This is the same technique I have demonstrated in my previous tutorial Data Access in ASP.NET Core using EF Core (Code First)

First of all, we need to inject OnlineShopDbContext class in our HomeController constructor and then we can call the ToListAsync method on Products DbSet. This will automatically fetch data from the existing database. Once the data is available, we will pass the data to Razor View.

  

public class HomeController : Controller
{
    private readonly OnlineShopDbContext _context;

    public HomeController(OnlineShopDbContext context)
    {
        _context = context;
    }

    public async Task<IActionResult> Index()
    {
        return View(await _context.Products.ToListAsync());
    }
}

The Index Razor View declares the model as IEnumerable and then it has a standard foreach loop that will iterate over the list of Products and will generate an HTML table on the page.

@model IEnumerable<Product>

@{
    ViewData["Title"] = "Home Page";
}
<h2>Products</h2>
<table class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Id)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Name)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Price)
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.Id)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Name)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Price)
                </td>
            </tr>
        }
    </tbody>
</table>

If you will run the project now, you will see the following page showing all the products from the database.

Summary

In this tutorial, I have shown you how to generate model classes and DbContext from an existing database using Entity Framework commands and tools. I have also covered how to generate code with Fluent API or Data Annotations and finally, I gave you an example of extending the generated DbContext. If you like this post, please share it with your friends or colleagues to spread the knowledge further.