Tutorial How to use EntityFramework Core Database first approach

In a recent .NET 8 Core solution that had multiple projects I needed to generate the DbContext and models from an existing database. The solution has a "Site" Project for the application UI, I wanted to keep a clean seperation of concerns and use a seperate "Data" project for the DbContext. All other projects should be able to use the DbContext. Here is a summary of the steps required

1. Set Up Your Solution Structure

You'll need two primary projects in your solution:

Site (MVC Core Web App) — This project will serve as the UI and make use of the DbContext to interact with the database.
Data (Class Library) — This project will contain the generated models and DbContext.
In the Data project create a Models folder ( the generated DbContext and Models will get generated into this folder)

2. Install Required NuGet Packages

You need to install EF Core packages in both the Data and Site projects.

In the Data project: Install the following NuGet packages:

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools

These packages allow EF Core to generate the models and DbContext for your database.

In the Site project: Install:

Install-Package Microsoft.EntityFrameworkCore

This allows the web app to use EF Core to work with the context from the Data project.

3. Generate Models and DbContext in the "Data" Project

Now, you can use the Scaffold-DbContext command to generate the models and DbContext in the Data project.

Make sure your Data project has a connection string, even temporarily, to scaffold the database. In the Data project, open the appsettings.json file (or you can create a temporary one for scaffolding):

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=your_server;Database=your_database;Trusted_Connection=True;"
  }
}

Once you have the connection string ready, use the following command to generate the models and DbContext in the Data project:

In the Package Manager Console:

Scaffold-DbContext "Server=your_server;Database=your_database;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Context ApplicationDbContext -Project Data

For example:

Scaffold-DbContext "Server=.\sqlexpress;Database=winnersappdb;Trusted_Connection=True;TrustServerCertificate=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Context ApplicationDbContext -Project Data

This will:

Generate the DbContext (ApplicationDbContext.cs) and models in the Models folder inside your Data project.

4. Add the Connection String in the "Site" Project

Now, in the Site project (your MVC app), add the connection string to the appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=your_server;Database=your_database;Trusted_Connection=True;"
  }
}

5. Register the DbContext in the Site Project

Since the Data project doesn't have a Program.cs or Startup.cs, you need to register the DbContext in the Site project (MVC layer) and point to the ApplicationDbContext that was generated in the Data project.

In Program.cs (of the Site project), add the following:

using Data;  // Reference to the Data project
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllersWithViews();

// Configure Entity Framework and DbContext
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

var app = builder.Build();

This code does the following:

It configures the ApplicationDbContext (from the Data project) in the Site project using the connection string defined in appsettings.json.

6. Make Sure Your Projects Reference Each Other

The Site project should have a reference to the Data project.
- Right-click on the Site project | Add | Project Reference | Select the Data project.

This allows the Site project to use the ApplicationDbContext and models from the Data project.

7. Use DbContext in the Site Project

Now that the ApplicationDbContext is registered in the Site project, you can inject it into controllers or services in your MVC project. For example, in a controller:

using Data;  // Models and DbContext from Data project

public class HomeController : Controller
{
    private readonly ApplicationDbContext _context;

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

    public IActionResult Index()
    {
        var data = _context.SomeEntities.ToList(); // Example usage of DbSet
        return View(data);
    }
}

8: Use DTO's to seperate concerns.

It is common to separate concerns between the database entities and the data we expose or use in our application logic. DTOs are often used to do this and thus seperate the database schema from the business logic or presentation layer.

for example:

A: Database Entity (EF Model)
Assume that Product is the model class generated by EF Core database-first

public partial class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Description { get; set; }
}

B: Product DTO
Our DTO (Data Transfer Object) might not need all fields from the Product entity. For example, we might want to expose only Name, Price, and a custom property that formats the price.

public class ProductDto
{
    public string Name { get; set; }
    public decimal Price { get; set; }

    // Custom property that is not part of the database entity
    public string DisplayPrice => $"{Price:C}"; // Formats price as a currency
}

C: Custom service layer or Repository

The service layer would typically handle the transformation of the entity into the DTO.

public class ProductService
{
    private readonly ApplicationDbContext _context;

    public ProductService(ApplicationDbContext context)
    {
        _context = context;
    }

    // Method to get a list of DTOs
    public IEnumerable<ProductDto> GetProducts()
    {
        // Fetch data from the database
        var products = _context.Products.ToList();

        // Convert each Product entity into a ProductDto
        var productDtos = products.Select(p => new ProductDto
        {
            Name = p.Name,
            Price = p.Price
        }).ToList();

        return productDtos;
    }
}

D: Contoller Layer (Using the DTO in an API)

In the API or Controller layer, we would return the DTO instead of the entity directly to ensure the client only gets the necessary information.

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly ProductService _productService;

    public ProductsController(ProductService productService)
    {
        _productService = productService;
    }

    // GET: api/products
    [HttpGet]
    public IActionResult GetProducts()
    {
        var productDtos = _productService.GetProducts();
        return Ok(productDtos); // Returns a list of ProductDto
    }
}

9. Update and Maintain the Data Layer

When the database changes, you can update your models by re-running the Scaffold-DbContext command in the Data project. Ensure any manual changes made to models or DbContext are done cautiously to avoid being overwritten

If you've made manual changes to the generated models or DbContext in your Data project and you're using the Database-First approach, those changes could be overwritten when you re-run the Scaffold-DbContext command to update your models. To handle this scenario, there are a few strategies you can follow to avoid losing your changes:

Strategies to Preserve Manual Changes in Models and DbContext

1. Use Partial Classes

The cleanest way to extend and customize generated models and DbContext without worrying about overwrites is to use partial classes. Entity Framework Core models and DbContext are typically generated as partial classes, which means you can create another partial class with the same name in a separate file and extend it with your custom logic.

Steps:

Create a new file in the Data project for each model or DbContext you want to extend.
Define a partial class that has the same name as the generated model or DbContext.

For example, if you have a model called Product, and you want to add custom logic, you can create a new file ProductPartial.cs like this:

// File: ProductPartial.cs
namespace Data.Models
{
    public partial class Product
    {
        // Custom properties, methods, etc.
        public string CustomField { get; set; }
        
        public string GetDisplayName()
        {
            return $"{Name} - {Category}";
        }
    }
}

Similarly, if you need to extend the DbContext, you can use a partial class to add methods, configuration, or properties:

// File: ApplicationDbContextPartial.cs
namespace Data
{
    public partial class ApplicationDbContext
    {
        // Custom configurations or methods
        public void SeedDatabase()
        {
            // Custom seeding logic
        }
    }
}

Using partial classes means that when you re-run the Scaffold-DbContext command, your custom logic will not be overwritten because it's in separate files.

2. Custom Files for Non-Scaffolded Entities

If you need to create entirely new models that are not part of the scaffolding (for example, complex entities, or those used for specific business logic), create these models separately in their own files. These files will not be overwritten because they're not part of the EF scaffolding process.

For example:

// File: CustomEntity.cs
namespace Data.Models
{
    public class CustomEntity
    {
        public int Id { get; set; }
        public string CustomField { get; set; }
    }
}

3. How to Scaffold specific Tables or just the new db Tables

If you've made significant manual changes to certain models and don't want them to be overwritten, or you only want to scaffold the new database tables, you can scaffold specific tables by specifying which tables you want to scaffold.
WARNING: BE SURE TO USE A DIFFERENT -CONTEXT so as not to overwite your original. Then when done, merge any changes into your original dbcontext class.

Scaffold-DbContext "Your_Connection_String" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Context ApplicationDbContext -Tables NewTable1, NewTable2

4. Backup Modified Models Before Rescaffolding

If you're concerned about accidental overwrites but need to regenerate all models, a quick manual approach would be:

Copy the existing model files and the DbContext to a separate folder as a backup.
Re-run the scaffolding command to generate fresh models.
Compare the regenerated models with your backup using a comparison tool (like WinMerge or Beyond Compare) to reapply your custom changes.

EF Core Database first tutorial