.NET Database First Approach

Introduction

The Database First approach is a development workflow in Entity Framework (EF) where you start with an existing database and generate your entity models and context classes based on that database schema. This approach is particularly useful when:

You're working with an existing database that cannot be modified
Database design is handled by a separate team
You prefer designing your database schema using tools like SQL Server Management Studio
You're integrating a legacy database into your new .NET application

In this tutorial, we'll explore how to implement the Database First approach using Entity Framework Core, Microsoft's modern data access technology for .NET applications.

Prerequisites

Before getting started, make sure you have:

Visual Studio 2019 or higher
.NET 6.0 or later installed
Access to an existing SQL Server database (or another supported database)
Basic understanding of C# and .NET

Setting Up Your Project

Let's start by creating a new .NET project and adding the necessary packages.

Create a new Console Application project in Visual Studio
Install the required NuGet packages by running these commands in the Package Manager Console:

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

If you're using a different database provider (like MySQL or PostgreSQL), install the appropriate package instead of SqlServer.

Scaffolding Models from the Database

The Database First approach uses a process called "scaffolding" to generate C# classes from database tables. We'll use the Scaffold-DbContext command to reverse engineer our database.

Step 1: Create the Database Connection String

First, you need to create a connection string to your database:

csharp
// Example connection string for SQL Server
string connectionString = "Server=YourServerName;Database=YourDatabaseName;Trusted_Connection=True;";

For production applications, you would typically store this in your appsettings.json file:

json
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=YourServerName;Database=YourDatabaseName;Trusted_Connection=True;"
  }
}

Step 2: Generate Models from the Database

Open the Package Manager Console in Visual Studio and run the following command:

bash
Scaffold-DbContext "Server=YourServerName;Database=YourDatabaseName;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

This command does the following:

Connects to your database using the provided connection string
Uses the SQL Server provider
Creates model classes in a directory called "Models"

Step 3: Examine the Generated Code

After running the command, Entity Framework will create:

A class for each table in your database
A DbContext class that serves as your main entry point to the database

For example, if your database has Customers and Orders tables, you'll get:

csharp
// Models/Customer.cs
public partial class Customer
{
    public Customer()
    {
        Orders = new HashSet<Order>();
    }

    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    
    public virtual ICollection<Order> Orders { get; set; }
}

// Models/Order.cs
public partial class Order
{
    public int OrderId { get; set; }
    public int CustomerId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal TotalAmount { get; set; }
    
    public virtual Customer Customer { get; set; }
}

// Models/YourDatabaseNameContext.cs
public partial class YourDatabaseNameContext : DbContext
{
    public YourDatabaseNameContext()
    {
    }

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

    public virtual DbSet<Customer> Customers { get; set; }
    public virtual DbSet<Order> Orders { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer("Server=YourServerName;Database=YourDatabaseName;Trusted_Connection=True;");
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configuration code for your entities
    }
}

Using the Generated Models

Now that you have your models and context, let's see how to use them in your application.

Querying Data

Here's a simple example of querying data from your database:

csharp
using System;
using System.Linq;
using YourNamespace.Models;

class Program
{
    static void Main(string[] args)
    {
        using (var context = new YourDatabaseNameContext())
        {
            // Get all customers
            var customers = context.Customers.ToList();
            
            Console.WriteLine("All customers:");
            foreach (var customer in customers)
            {
                Console.WriteLine($"ID: {customer.CustomerId}, Name: {customer.Name}, Email: {customer.Email}");
            }
            
            // Get orders for a specific customer
            var customerOrders = context.Orders
                .Where(o => o.CustomerId == 1)
                .ToList();
                
            Console.WriteLine("\nOrders for customer ID 1:");
            foreach (var order in customerOrders)
            {
                Console.WriteLine($"Order ID: {order.OrderId}, Date: {order.OrderDate}, Amount: ${order.TotalAmount}");
            }
        }
    }
}

Creating New Data

You can add new records to your database like this:

csharp
using (var context = new YourDatabaseNameContext())
{
    // Create a new customer
    var newCustomer = new Customer
    {
        Name = "John Doe",
        Email = "[email protected]"
    };
    
    // Add to context
    context.Customers.Add(newCustomer);
    
    // Save changes to the database
    context.SaveChanges();
    
    Console.WriteLine($"Created new customer with ID: {newCustomer.CustomerId}");
}

Updating Existing Data

To update existing records:

csharp
using (var context = new YourDatabaseNameContext())
{
    // Find customer by ID
    var customer = context.Customers.Find(1);
    
    if (customer != null)
    {
        // Update properties
        customer.Email = "[email protected]";
        
        // Save changes
        context.SaveChanges();
        
        Console.WriteLine("Customer updated successfully");
    }
}

Deleting Data

To delete records:

csharp
using (var context = new YourDatabaseNameContext())
{
    // Find customer by ID
    var customer = context.Customers.Find(1);
    
    if (customer != null)
    {
        // Remove from context
        context.Customers.Remove(customer);
        
        // Save changes
        context.SaveChanges();
        
        Console.WriteLine("Customer deleted successfully");
    }
}

Using Dependency Injection (ASP.NET Core)

In ASP.NET Core applications, you'll typically use dependency injection to configure your DbContext. Here's how to set it up in the Startup.cs or Program.cs file:

csharp
// For .NET 6+ in Program.cs
builder.Services.AddDbContext<YourDatabaseNameContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

Then in your controllers or services, you can inject the context:

csharp
public class CustomerController : Controller
{
    private readonly YourDatabaseNameContext _context;
    
    public CustomerController(YourDatabaseNameContext context)
    {
        _context = context;
    }
    
    public async Task<IActionResult> Index()
    {
        var customers = await _context.Customers.ToListAsync();
        return View(customers);
    }
}

Real-world Example: Customer Management System

Let's create a more comprehensive example of a customer management system using the Database First approach.

First, imagine we have this database schema:

sql
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY IDENTITY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) UNIQUE,
    CreatedDate DATETIME DEFAULT GETDATE()
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY IDENTITY,
    CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID),
    OrderDate DATETIME DEFAULT GETDATE(),
    Status NVARCHAR(20),
    TotalAmount DECIMAL(18, 2)
);

CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY IDENTITY,
    OrderID INT FOREIGN KEY REFERENCES Orders(OrderID),
    ProductName NVARCHAR(100),
    Quantity INT,
    UnitPrice DECIMAL(18, 2)
);

After running the scaffold command, we would get our model classes. Now, let's create a customer service class to handle business logic:

csharp
public class CustomerService
{
    private readonly YourDatabaseNameContext _context;
    
    public CustomerService(YourDatabaseNameContext context)
    {
        _context = context;
    }
    
    public async Task<List<Customer>> GetAllCustomersAsync()
    {
        return await _context.Customers.ToListAsync();
    }
    
    public async Task<Customer> GetCustomerAsync(int id)
    {
        return await _context.Customers.FindAsync(id);
    }
    
    public async Task<List<Order>> GetCustomerOrdersAsync(int customerId)
    {
        return await _context.Orders
            .Where(o => o.CustomerId == customerId)
            .Include(o => o.OrderItems)
            .ToListAsync();
    }
    
    public async Task<Customer> CreateCustomerAsync(string firstName, string lastName, string email)
    {
        var customer = new Customer
        {
            FirstName = firstName,
            LastName = lastName,
            Email = email,
            CreatedDate = DateTime.Now
        };
        
        _context.Customers.Add(customer);
        await _context.SaveChangesAsync();
        
        return customer;
    }
    
    public async Task<Order> CreateOrderAsync(int customerId, List<OrderItem> items)
    {
        // Validate customer exists
        var customer = await _context.Customers.FindAsync(customerId);
        if (customer == null)
            throw new ArgumentException("Customer not found");
            
        // Calculate total amount
        decimal total = items.Sum(i => i.Quantity * i.UnitPrice);
        
        // Create order
        var order = new Order
        {
            CustomerId = customerId,
            OrderDate = DateTime.Now,
            Status = "Pending",
            TotalAmount = total,
            OrderItems = items
        };
        
        _context.Orders.Add(order);
        await _context.SaveChangesAsync();
        
        return order;
    }
}

Then in your application:

csharp
// Example usage in a console app
static async Task Main(string[] args)
{
    // Setup dependency injection
    var services = new ServiceCollection();
    services.AddDbContext<YourDatabaseNameContext>(options =>
        options.UseSqlServer("Your connection string"));
    services.AddTransient<CustomerService>();
    
    var serviceProvider = services.BuildServiceProvider();
    var customerService = serviceProvider.GetService<CustomerService>();
    
    // Get all customers
    var customers = await customerService.GetAllCustomersAsync();
    
    foreach (var customer in customers)
    {
        Console.WriteLine($"Customer: {customer.FirstName} {customer.LastName}");
        
        // Get orders for this customer
        var orders = await customerService.GetCustomerOrdersAsync(customer.CustomerId);
        
        foreach (var order in orders)
        {
            Console.WriteLine($"  Order: {order.OrderId} - ${order.TotalAmount} - Status: {order.Status}");
            
            foreach (var item in order.OrderItems)
            {
                Console.WriteLine($"    - {item.Quantity}x {item.ProductName} @ ${item.UnitPrice}");
            }
        }
    }
}

Updating the Database Schema

One challenge with Database First is keeping your model in sync with database changes. When your database schema changes, you need to regenerate your models:

bash
# Regenerate all models
Scaffold-DbContext "Your Connection String" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Force

# Or regenerate specific tables
Scaffold-DbContext "Your Connection String" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables "Customers,Orders" -Force

The -Force flag overwrites existing files.

Best Practices for Database First Development

Partial Classes: The generated model classes are created as partial classes, allowing you to extend them in separate files without modifying the generated code.

csharp
// Models/Customer.cs (generated)
public partial class Customer
{
    // Generated properties
}

// Models/Customer.Custom.cs (your custom code)
public partial class Customer
{
    // Calculate full name from first and last name
    public string FullName => $"{FirstName} {LastName}";
    
    // Custom validation
    public bool IsValidCustomer()
    {
        return !string.IsNullOrEmpty(Email) && Email.Contains("@");
    }
}

Repository Pattern: Consider using the repository pattern to abstract database access:

csharp
public interface ICustomerRepository
{
    Task<IEnumerable<Customer>> GetAllAsync();
    Task<Customer> GetByIdAsync(int id);
    Task AddAsync(Customer customer);
    Task UpdateAsync(Customer customer);
    Task DeleteAsync(int id);
}

public class CustomerRepository : ICustomerRepository
{
    private readonly YourDatabaseNameContext _context;
    
    public CustomerRepository(YourDatabaseNameContext context)
    {
        _context = context;
    }
    
    public async Task<IEnumerable<Customer>> GetAllAsync()
    {
        return await _context.Customers.ToListAsync();
    }
    
    // Implement other methods...
}

Script Generation: Save your scaffold command in a script file for easy regeneration.

Common Issues and Troubleshooting

Relationship Configuration: Sometimes, EF Core might not detect relationships correctly. You can manually configure them in the OnModelCreating method.

csharp
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    
    // Configure one-to-many relationship
    modelBuilder.Entity<Customer>()
        .HasMany(c => c.Orders)
        .WithOne(o => o.Customer)
        .HasForeignKey(o => o.CustomerId);
}

Pluralization Issues: Entity Framework might not always pluralize or singularize names correctly. You can use data annotations or the Fluent API to fix this:

csharp
modelBuilder.Entity<Person>().ToTable("People");

Missing Navigation Properties: If relationships aren't generated, you may need to add navigation properties manually.

Summary

The Database First approach in Entity Framework Core provides a convenient way to work with existing databases in .NET applications. It automatically generates entity classes and a DbContext based on your database schema, saving you time and ensuring your model correctly reflects the database structure.

Key benefits include:

Rapid development with existing databases
Accurate representation of complex database schemas
Support for legacy systems
No need to manually create entity classes

However, it does have some limitations:

Less control over entity design
Need to regenerate models when the database changes
Potential for overwrites of custom code (mitigated by using partial classes)

For many projects, especially those integrating with existing databases, Database First is an excellent approach that can significantly speed up development.

Additional Resources

Exercises

Create a new database with at least three related tables (e.g., Products, Categories, and Suppliers)
Use the Database First approach to scaffold models for your database
Create a simple console application that displays data from all three tables
Implement CRUD (Create, Read, Update, Delete) operations for one of the tables
Extend one of the generated model classes with additional functionality using partial classes
Create a repository class to abstract data access for one of your entities
Use eager loading to retrieve related data in a single query

By completing these exercises, you'll gain practical experience with the Database First approach in Entity Framework Core and be ready to apply it in your own projects.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Prerequisites​

Setting Up Your Project​

Scaffolding Models from the Database​

Step 1: Create the Database Connection String​

Step 2: Generate Models from the Database​

Step 3: Examine the Generated Code​

Using the Generated Models​

Querying Data​

Creating New Data​

Updating Existing Data​

Deleting Data​

Using Dependency Injection (ASP.NET Core)​

Real-world Example: Customer Management System​

Updating the Database Schema​

Best Practices for Database First Development​

Common Issues and Troubleshooting​

Summary​

Additional Resources​

Exercises​