Database First Approach with Entity Framework

The Database First approach allows you to generate an Entity Framework model from an existing database. This is particularly useful when you have a legacy database or when you want to ensure your model perfectly reflects the existing schema.

When to Use Database First

Steps to Implement Database First

1. Prerequisites

Ensure you have the following installed:

2. Creating the EF Model from the Database

You can use the Entity Data Model Wizard in Visual Studio:

  1. In Visual Studio, go to File > New > Project.
  2. Select a .NET project template (e.g., Console App, Web Application).
  3. Right-click on your project in Solution Explorer, select Add > New Item.
  4. In the "Add New Item" dialog, select Data > ADO.NET Entity Data Model.
  5. Give your model a name (e.g., MyDatabaseModel.edmx).
  6. Choose "Database from Code-First" or "Database from SQL Server" (depending on your EF version and project type). For Database First, select "Generate from Database".
  7. Click "Next".
  8. Click "New Connection" and provide your database connection details.
  9. Choose the tables and views you want to include in your model.
  10. Specify the EF version (e.g., EF 6.x, EF Core).
  11. Click "Finish".

Visual Studio will generate:

3. Using the Generated Model

Once the model is generated, you can start interacting with your database:

Example: Querying Data


using (var context = new YourDbContext()) // Replace YourDbContext with your context class name
{
    // Query for all customers
    var customers = context.Customers.ToList();

    // Query for a specific customer by ID
    var customer = context.Customers.Find(1);

    // Query with LINQ
    var ordersForCustomer = context.Orders
                                   .Where(o => o.CustomerId == customer.CustomerId)
                                   .ToList();

    foreach (var order in ordersForCustomer)
    {
        Console.WriteLine($"Order ID: {order.OrderId}, Date: {order.OrderDate}");
    }
}

Example: Adding Data


using (var context = new YourDbContext())
{
    var newCustomer = new Customer
    {
        FirstName = "Jane",
        LastName = "Doe",
        Email = "jane.doe@example.com"
    };

    context.Customers.Add(newCustomer);
    context.SaveChanges(); // Persist the changes to the database
}

Example: Updating Data


using (var context = new YourDbContext())
{
    var customerToUpdate = context.Customers.Find(1); // Assuming customer with ID 1 exists

    if (customerToUpdate != null)
    {
        customerToUpdate.Email = "updated.email@example.com";
        context.SaveChanges();
    }
}

Example: Deleting Data


using (var context = new YourDbContext())
{
    var customerToDelete = context.Customers.Find(2); // Assuming customer with ID 2 exists

    if (customerToDelete != null)
    {
        context.Customers.Remove(customerToDelete);
        context.SaveChanges();
    }
}

Considerations for Database First

Tip: Keep your .edmx file synchronized with your database. If the database schema changes, you'll need to update the model using the wizard.
Important: In modern .NET Core and .NET 5+ development, while Database First is still possible with EF Core, the workflow often involves scaffolding from an existing database using the EF Core CLI tools, which is a more command-line-driven approach than the Visual Studio wizard for .NET Framework. The principles remain the same: generate code from the database.

Updating the Model

To update your model after database changes:

  1. Right-click on the .edmx file in Solution Explorer.
  2. Select "Update Model from Database".
  3. Follow the wizard to refresh your model with the latest database schema.

Database First vs. Code First

Database First: Starts with an existing database and generates the model and code. Best for existing databases.

Code First: Starts with your C# classes and generates the database schema. Best for new projects where you want to define your domain model first.

Further Reading