Skip to main content

.NET Entity Framework

Introduction​

Entity Framework (EF) is Microsoft's object-relational mapping (ORM) framework for .NET applications. It serves as a bridge between your object-oriented .NET code and relational databases, eliminating the need to write repetitive data access code. For beginners, Entity Framework offers a way to work with databases using familiar C# objects and LINQ queries rather than SQL statements.

In this guide, we'll explore Entity Framework's core concepts, demonstrate how to set up and use it in your projects, and show practical examples to help you understand its real-world applications.

What is an ORM?​

Before diving deeper into Entity Framework, let's understand what an Object-Relational Mapper (ORM) is:

An ORM is a programming technique that converts data between incompatible type systems in object-oriented programming languages (like C#) and relational databases. It creates a "virtual object database" that can be used from within the programming language.

Benefits of using an ORM like Entity Framework include:

  • Writing less code for database operations
  • Working with database data as C# objects
  • Automatic handling of database connections
  • Built-in protection against SQL injection attacks
  • Database-provider independence (can switch from SQL Server to PostgreSQL with minimal code changes)

Entity Framework Approaches​

Entity Framework supports two main development approaches:

1. Database-First​

This approach is used when you already have a database and want to generate your models based on it.

2. Code-First​

This approach starts with C# classes (models) that are then used to generate a database schema. It's ideal for new projects or when your domain model drives development.

Getting Started with Entity Framework Core​

Entity Framework Core is the latest version of Entity Framework, redesigned to be cross-platform, lightweight, and extensible.

Setting Up Your Project​

First, install the required NuGet packages:

csharp
// Install via Package Manager Console
Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.SqlServer  // For SQL Server

Or add them to your project file:

xml
<ItemGroup>
  <PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.13" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.13" />
</ItemGroup>

Creating Your Model Classes​

Let's create a simple model for a bookstore application:

csharp
public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public string ISBN { get; set; }
    public decimal Price { get; set; }
    
    // Navigation property
    public Author Author { get; set; }
    public int AuthorId { get; set; }
}

public class Author
{
    public int AuthorId { get; set; }
    public string Name { get; set; }
    public DateTime DateOfBirth { get; set; }
    
    // Navigation property
    public List<Book> Books { get; set; }
}

Creating the Database Context​

The DbContext class is the foundation of Entity Framework that represents a session with the database:

csharp
public class BookstoreContext : DbContext
{
    public BookstoreContext(DbContextOptions<BookstoreContext> options)
        : base(options)
    {
    }
    
    public DbSet<Book> Books { get; set; }
    public DbSet<Author> Authors { get; set; }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure additional model mappings here
        modelBuilder.Entity<Book>()
            .Property(b => b.Title)
            .IsRequired()
            .HasMaxLength(200);
        
        modelBuilder.Entity<Book>()
            .Property(b => b.Price)
            .HasColumnType("decimal(18,2)");
    }
}

Configuring the Database Connection​

In your application's startup code or Program.cs:

csharp
// ASP.NET Core example
builder.Services.AddDbContext<BookstoreContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

Your connection string in appsettings.json:

json
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=Bookstore;Trusted_Connection=True;"
  }
}

Creating Migrations​

Migrations are a way to keep your database schema in sync with your model classes:

bash
# Command line migrations
dotnet ef migrations add InitialCreate
dotnet ef database update

Or through Package Manager Console:

Add-Migration InitialCreate
Update-Database

Basic Database Operations with Entity Framework​

Creating (Insert) Data​

csharp
using var context = new BookstoreContext(options);

// Create a new author
var author = new Author
{
    Name = "J.K. Rowling",
    DateOfBirth = new DateTime(1965, 7, 31)
};

// Add the author to the context
context.Authors.Add(author);

// Create a new book
var book = new Book
{
    Title = "Harry Potter and the Philosopher's Stone",
    ISBN = "978-0747532743",
    Price = 9.99m,
    Author = author  // EF will handle the relationship
};

// Add the book to the context
context.Books.Add(book);

// Save changes to the database
context.SaveChanges();

Console.WriteLine($"Added book with ID: {book.BookId}");

Output:

Added book with ID: 1

Reading (Query) Data​

Simple query:

csharp
// Get all books
var books = context.Books.ToList();
foreach (var b in books)
{
    Console.WriteLine($"Book: {b.Title}, Price: ${b.Price}");
}

Output:

Book: Harry Potter and the Philosopher's Stone, Price: $9.99

More complex query with filtering and including related data:

csharp
// Find books by a specific author including author information
var rowlingsBooks = context.Books
    .Include(b => b.Author)  // Include related Author data
    .Where(b => b.Author.Name.Contains("Rowling"))
    .OrderBy(b => b.Title)
    .ToList();

foreach (var book in rowlingsBooks)
{
    Console.WriteLine($"Title: {book.Title}, Author: {book.Author.Name}");
}

Output:

Title: Harry Potter and the Philosopher's Stone, Author: J.K. Rowling

Updating Data​

csharp
// Find the book to update
var bookToUpdate = context.Books.FirstOrDefault(b => b.Title.Contains("Harry Potter"));

if (bookToUpdate != null)
{
    // Update properties
    bookToUpdate.Price = 14.99m;
    
    // Save changes
    context.SaveChanges();
    Console.WriteLine($"Updated price for {bookToUpdate.Title} to ${bookToUpdate.Price}");
}

Output:

Updated price for Harry Potter and the Philosopher's Stone to $14.99

Deleting Data​

csharp
// Find the book to delete
var bookToDelete = context.Books.FirstOrDefault(b => b.BookId == 1);

if (bookToDelete != null)
{
    // Remove the book
    context.Books.Remove(bookToDelete);
    
    // Save changes
    context.SaveChanges();
    Console.WriteLine($"Deleted book: {bookToDelete.Title}");
}

Output:

Deleted book: Harry Potter and the Philosopher's Stone

Advanced Entity Framework Features​

Working with Relationships​

Entity Framework supports various types of relationships between entities:

  1. One-to-Many: The most common relationship (e.g., one Author has many Books)
  2. Many-to-Many: Requires a junction table (e.g., Books and Categories)
  3. One-to-One: Where an entity relates to exactly one instance of another entity

Example of configuring a Many-to-Many relationship:

csharp
public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    // Other properties...
    
    public List<Category> Categories { get; set; }
}

public class Category
{
    public int CategoryId { get; set; }
    public string Name { get; set; }
    
    public List<Book> Books { get; set; }
}

Configure in OnModelCreating:

csharp
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Configure many-to-many relationship
    modelBuilder.Entity<Book>()
        .HasMany(b => b.Categories)
        .WithMany(c => c.Books)
        .UsingEntity(j => j.ToTable("BookCategories"));
}

Using Lazy Loading​

Lazy loading is the process of automatically loading related entities when they're accessed. To enable it, install the following package:

Microsoft.EntityFrameworkCore.Proxies

And configure in your context:

csharp
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseLazyLoadingProxies()
        .UseSqlServer(connectionString);
}

Then modify your model classes:

csharp
public class Book
{
    public int BookId { get; set; }
    // Other properties...
    
    // Virtual enables lazy loading
    public virtual Author Author { get; set; }
}

Raw SQL Queries​

Sometimes you need to execute raw SQL queries:

csharp
// Execute raw SQL query
var books = context.Books
    .FromSqlRaw("SELECT * FROM Books WHERE Price > 10")
    .Include(b => b.Author)
    .ToList();

// Execute stored procedure
var newBooks = context.Books
    .FromSqlRaw("EXEC GetNewBooks @p0", DateTime.Now.AddMonths(-1))
    .ToList();

Real-World Application Example​

Let's build a simple book inventory system using Entity Framework:

csharp
// Program.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

class Program
{
    static async Task Main(string[] args)
    {
        // Build configuration
        var configuration = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json")
            .Build();

        // Set up DbContext
        var optionsBuilder = new DbContextOptionsBuilder<BookstoreContext>();
        optionsBuilder.UseSqlServer(configuration.GetConnectionString("DefaultConnection"));
        
        using var context = new BookstoreContext(optionsBuilder.Options);
        
        // Ensure database is created
        await context.Database.EnsureCreatedAsync();
        
        // UI Loop
        bool exit = false;
        while (!exit)
        {
            Console.Clear();
            Console.WriteLine("=== Book Inventory System ===");
            Console.WriteLine("1. Add New Book");
            Console.WriteLine("2. List All Books");
            Console.WriteLine("3. Update Book Price");
            Console.WriteLine("4. Delete Book");
            Console.WriteLine("5. Exit");
            Console.Write("Enter your choice: ");
            
            var choice = Console.ReadLine();
            
            switch (choice)
            {
                case "1":
                    await AddNewBook(context);
                    break;
                case "2":
                    await ListAllBooks(context);
                    break;
                case "3":
                    await UpdateBookPrice(context);
                    break;
                case "4":
                    await DeleteBook(context);
                    break;
                case "5":
                    exit = true;
                    break;
                default:
                    Console.WriteLine("Invalid option, please try again.");
                    break;
            }
            
            if (!exit)
            {
                Console.WriteLine("\nPress any key to continue...");
                Console.ReadKey();
            }
        }
    }
    
    private static async Task AddNewBook(BookstoreContext context)
    {
        Console.WriteLine("\n=== Add New Book ===");
        
        Console.Write("Enter book title: ");
        var title = Console.ReadLine();
        
        Console.Write("Enter ISBN: ");
        var isbn = Console.ReadLine();
        
        Console.Write("Enter price: ");
        if (!decimal.TryParse(Console.ReadLine(), out decimal price))
        {
            Console.WriteLine("Invalid price format.");
            return;
        }
        
        Console.Write("Enter author name: ");
        var authorName = Console.ReadLine();
        
        // Find or create author
        var author = await context.Authors.FirstOrDefaultAsync(a => a.Name == authorName);
        if (author == null)
        {
            author = new Author { Name = authorName };
            context.Authors.Add(author);
        }
        
        // Add new book
        var book = new Book
        {
            Title = title,
            ISBN = isbn,
            Price = price,
            Author = author
        };
        
        context.Books.Add(book);
        await context.SaveChangesAsync();
        
        Console.WriteLine($"Book '{title}' added successfully!");
    }
    
    private static async Task ListAllBooks(BookstoreContext context)
    {
        Console.WriteLine("\n=== All Books ===");
        
        var books = await context.Books.Include(b => b.Author).ToListAsync();
        
        if (books.Count == 0)
        {
            Console.WriteLine("No books in the inventory.");
            return;
        }
        
        Console.WriteLine($"{"ID",-5}{"Title",-40}{"Author",-25}{"Price",-10}{"ISBN",-15}");
        Console.WriteLine(new string('-', 95));
        
        foreach (var book in books)
        {
            Console.WriteLine($"{book.BookId,-5}{book.Title,-40}{book.Author.Name,-25}{book.Price:C,-10}{book.ISBN,-15}");
        }
    }
    
    private static async Task UpdateBookPrice(BookstoreContext context)
    {
        Console.WriteLine("\n=== Update Book Price ===");
        
        Console.Write("Enter book ID: ");
        if (!int.TryParse(Console.ReadLine(), out int id))
        {
            Console.WriteLine("Invalid ID format.");
            return;
        }
        
        var book = await context.Books.FindAsync(id);
        if (book == null)
        {
            Console.WriteLine("Book not found.");
            return;
        }
        
        Console.WriteLine($"Current price for '{book.Title}': {book.Price:C}");
        Console.Write("Enter new price: ");
        
        if (!decimal.TryParse(Console.ReadLine(), out decimal newPrice))
        {
            Console.WriteLine("Invalid price format.");
            return;
        }
        
        book.Price = newPrice;
        await context.SaveChangesAsync();
        
        Console.WriteLine($"Price updated successfully to {newPrice:C}!");
    }
    
    private static async Task DeleteBook(BookstoreContext context)
    {
        Console.WriteLine("\n=== Delete Book ===");
        
        Console.Write("Enter book ID: ");
        if (!int.TryParse(Console.ReadLine(), out int id))
        {
            Console.WriteLine("Invalid ID format.");
            return;
        }
        
        var book = await context.Books.FindAsync(id);
        if (book == null)
        {
            Console.WriteLine("Book not found.");
            return;
        }
        
        Console.WriteLine($"Are you sure you want to delete '{book.Title}'? (Y/N)");
        if (Console.ReadLine().Trim().ToUpper() != "Y")
        {
            Console.WriteLine("Operation cancelled.");
            return;
        }
        
        context.Books.Remove(book);
        await context.SaveChangesAsync();
        
        Console.WriteLine("Book deleted successfully!");
    }
}

This example demonstrates how to use Entity Framework in a real-world application with user input, error handling, and database operations.

Best Practices for Entity Framework​

  1. Use Async Methods: For better scalability, use async methods like SaveChangesAsync() and ToListAsync().

  2. Track Queries Appropriately: Use AsNoTracking() for read-only queries to improve performance.

    csharp
    var books = await context.Books
        .AsNoTracking()
        .ToListAsync();

  3. Use Transactions for Multiple Operations:

    csharp
    using var transaction = await context.Database.BeginTransactionAsync();
    try
    {
        // Perform multiple database operations
        context.Books.Add(book1);
        context.Books.Add(book2);
        await context.SaveChangesAsync();
        
        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }

  4. Consider Performance: Avoid N+1 query problems by using Include() to eager-load related data.

  5. Use Migrations for Schema Changes: Don't manually modify your database schema.

Common Pitfalls to Avoid​

  1. Loading Too Much Data: Always filter data at the database level with Where() before calling ToList().

  2. Not Disposing Context: Always dispose your context (use using statements).

  3. Long-Lived DbContext: The DbContext is designed to be short-lived. Create a new instance for each business operation.

  4. Not Handling Concurrency: Use concurrency tokens for handling concurrent updates.

Summary​

Entity Framework is a powerful ORM that simplifies database access in .NET applications. With Entity Framework, you can:

  • Work with databases using .NET objects
  • Perform CRUD operations with minimal code
  • Model complex relationships between tables
  • Generate database schemas from your code (Code-First)
  • Generate models from existing databases (Database-First)

We've covered the basics of setting up Entity Framework, defining models, creating a database context, and performing basic and advanced database operations. We've also looked at best practices and common pitfalls to avoid.

As you become more comfortable with Entity Framework, you'll find it significantly reduces the amount of data access code you need to write and maintain.

Additional Resources​

Practice Exercises​

  1. Basic Implementation: Create a small console application that uses Entity Framework to manage a collection of movies, with relationships to directors and actors.

  2. Migration Exercise: Add a new property to an existing model and create/apply a migration to update the database.

  3. Performance Challenge: Refactor an existing query to improve its performance, using techniques like AsNoTracking() and proper eager loading.

  4. Advanced Relationships: Implement a many-to-many relationship between two entities and demonstrate how to add/remove associations.

  5. Real-world Application: Extend the book inventory system we built to include categories, publishers, and search functionality.


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