Database First in Entity Framework

Harnessing Existing Databases with C# and LINQ

In enterprise applications, you often work with pre-existing databases designed by DBAs or legacy systems.

Database First in Entity Framework (EF) lets you generate C# classes and a DbContext directly from an existing database, giving you a strongly-typed interface to query and manipulate data while keeping your code aligned with the database schema.

This approach is ideal for database-centric projects, reporting tools, or applications where the database is the source of truth.

🔹 What Is Database First?

Database First workflow:

• Starts with an existing database.
• Uses EF’s Entity Data Model (EDMX) or EF Core scaffolding to generate:
  • Entity classes (DbSet<T>)
  • DbContext class
• Allows you to query, insert, update, and delete data without writing SQL directly.

✅ Perfect for legacy systems or databases maintained separately from your code.

🔹 Setting Up Database First

1. Using EDMX Designer (EF 6)

1. Right-click your project → Add → New Item → ADO.NET Entity Data Model.
2. Select EF Designer from Database.
3. Connect to your database.
4. Choose tables, views, and stored procedures.
5. EF generates an EDMX file, entity classes, and DbContext.

2. Using EF Core Scaffolding

Scaffold-DbContext "Server=.;Database=CompanyDB;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Context CompanyContext

✅ Generates entity classes and a DbContext automatically.
✅ Supports tables, views, and relationships.

🔹 Generated C# Classes

Example entity:

public partial class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Salary { get; set; }
    public int DepartmentId { get; set; }

    public virtual Department Department { get; set; }
}

public partial class Department
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Employee> Employees { get; set; }
}

✅ Navigation properties reflect foreign keys and relationships.
✅ Classes are strongly-typed, ready for LINQ queries.

🔹 Querying Data

Database First works seamlessly with LINQ:

using (var context = new CompanyContext())
{
    var itEmployees = context.Employees
        .Where(e => e.Department.Name == "IT")
        .OrderByDescending(e => e.Salary)
        .ToList();

    foreach (var e in itEmployees)
        Console.WriteLine($"{e.Name} - £{e.Salary}");
}

Output:

Alice - £60000
Clara - £65000

✅ LINQ provides type-safe, readable queries without writing raw SQL.

🔹 Updating the Model

• If the database schema changes, regenerate the EDMX (EF 6) or re-scaffold (EF Core).
• Partial classes allow you to add business logic without overwriting generated code.
• Keep the DbContext and entity classes in sync with the database.

🔹 Advantages of Database First

🔹 Relationships

One-to-Many:

• Department has multiple Employees.
• EF generates Employees collection in Department and Department reference in Employee.

Many-to-Many:

• EF automatically manages join tables for relationships.

✅ No manual mapping needed; EF reflects the database structure directly.

🔹 Real-World Example – Department Salary Summary

using (var context = new CompanyContext())
{
    var summary = context.Employees
        .GroupBy(e => e.Department.Name)
        .Select(g => new
        {
            Department = g.Key,
            Count = g.Count(),
            MaxSalary = g.Max(e => e.Salary),
            MinSalary = g.Min(e => e.Salary),
            AverageSalary = g.Average(e => e.Salary)
        });

    foreach (var dept in summary)
        Console.WriteLine($"{dept.Department}: {dept.Count} staff, max £{dept.MaxSalary}, min £{dept.MinSalary}, avg £{dept.AverageSalary:F0}");
}

Output:

IT: 2 staff, max £65000, min £60000, avg £62500
HR: 2 staff, max £45000, min £42000, avg £43500
Finance: 1 staff, max £50000, min £50000, avg £50000

✅ Combine Database First with LINQ for clean reporting and analytics.

🔹 Database First vs Other Approaches

🔹 Best Practices

• Use partial classes for custom methods to avoid overwriting generated code.
• Re-scaffold only when necessary to avoid losing manual edits.
• Prefer views or stored procedures for complex database operations.
• Use LINQ for queries whenever possible for type safety and readability.
• Keep your database schema as the source of truth in Database First projects.

🧪 Challenge Task

1. Scaffold a Database First model from an existing database with Products and Categories.
2. Write a LINQ query that returns:
   • Category Name
   • Number of Products
   • Highest and Lowest Product Price
3. Display results like:
   "Electronics: 5 products, max £1200, min £25"

👨‍💻 Want More?

Our Advanced C# Course covers:

• Code First, Model First, and Database First approaches
• LINQ queries, joins, projections, and aggregates
• Partial classes, scaffolding, and maintaining models
• Migrations, seeding, and syncing with database changes

Master Database First to integrate with existing systems, generate robust C# classes, and query your database safely and efficiently with LINQ.