Advanced Entity Framework: Mapping and Queries

Introduction
Imagine you are organizing a large closet. Each shelf needs to be well-categorized and each item in its proper place so you can find everything easily. Similarly, mapping in Entity Framework (EF) helps keep our data well-structured and accessible.

Today’s post will explore more advanced Entity Framework concepts, focusing on relationship mapping and creating complex queries.

Relationship Mapping
In Entity Framework, relationships between entities are mapped using navigation properties. Let’s see how to map different types of relationships:

One-to-Many Relationship

An author can have many books, but each book has only one author. Let’s map this relationship:

public class Author
{
    public int AuthorId { get; set; }
    public string Name { get; set; }
    public ICollection<Book> Books { get; set; }
}

public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public int AuthorId { get; set; }
    public Author Author { get; set; }
}

Many-to-Many Relationship

Imagine a book can have multiple authors and an author can have written multiple books. This is a many-to-many relationship:

public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public ICollection<BookAuthor> BookAuthors { get; set; }
}

public class Author
{
    public int AuthorId { get; set; }
    public string Name { get; set; }
    public ICollection<BookAuthor> BookAuthors { get; set; }
}

public class BookAuthor
{
    public int BookId { get; set; }
    public Book Book { get; set; }

    public int AuthorId { get; set; }
    public Author Author { get; set; }
}

One-to-One Relationship

A book can have one specific detail, and that detail belongs to only one book. Here’s how to map this relationship:

public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public BookDetail BookDetail { get; set; }
}

public class BookDetail
{
    public int BookDetailId { get; set; }
    public string Summary { get; set; }
    public int BookId { get; set; }
    public Book Book { get; set; }
}

Complex Queries

Querying data using LINQ is one of EF’s most powerful features. Let’s look at some examples of complex queries:

Query with Filters and Sorting

Let’s fetch books by a specific author, sorted by title:

using (var context = new LibraryContext())
{
    var authorBooks = context.Books
        .Include(b => b.Author)
        .Where(b => b.Author.Name == "Example Author")
        .OrderBy(b => b.Title)
        .ToList();
}

Query with Grouping

Let’s group books by author and count how many books each author has written:

using (var context = new LibraryContext())
{
    var authorGroups = context.Books
        .GroupBy(b => b.Author.Name)
        .Select(g => new 
        {
            Author = g.Key,
            BookCount = g.Count()
        })
        .ToList();
}

Query with Joins

Let’s fetch book details along with author information:

using (var context = new LibraryContext())
{
    var booksWithDetails = context.Books
        .Include(b => b.BookDetail)
        .Include(b => b.Author)
        .ToList();
}

Conclusion
Just like organizing a closet helps us find everything easily, mapping and queries in Entity Framework allow us to keep our data well-structured and accessible. Mastering these advanced concepts makes development with .NET more efficient and powerful.

Blog

Advanced Entity Framework: Mapping and Queries

Fabrício Marcondes Santos

Join Our Newsletter. No Spam, Only the good stuff.

Related