LINQ to Entities
Introduction
LINQ to Entities is a powerful implementation of LINQ (Language Integrated Query) specifically designed to work with Entity Framework, Microsoft's object-relational mapping (ORM) framework for .NET. It allows developers to write strongly-typed queries against an Entity Framework data model using familiar C# syntax, which then get translated into SQL commands that execute against the database.
Unlike direct SQL queries, LINQ to Entities offers several advantages:
- Type safety: Compile-time error checking for your database queries
- IntelliSense support: Code completion and suggestions as you write queries
- Language integration: Write database queries using familiar C# syntax
- Abstraction: Work with database objects as .NET objects
- Testing: Easier to mock and test than raw SQL
In this tutorial, you'll learn how LINQ to Entities works, how to write basic and advanced queries, and how to use it in real-world scenarios.
Prerequisites
To follow along with this tutorial, you should have:
- Basic knowledge of C# and .NET
- Familiarity with basic LINQ concepts
- Entity Framework installed in your project (
Microsoft.EntityFrameworkCorefor EF Core) - A database context set up in your application
Basic LINQ to Entities Queries
Let's start by looking at some basic queries. We'll use a simple bookstore database model with Book, Author, and Category entities.
Setting Up Your Context
First, you need an Entity Framework context class:
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
public class BookstoreContext : DbContext
{
public BookstoreContext(DbContextOptions<BookstoreContext> options)
: base(options)
{ }
public DbSet<Book> Books { get; set; }
public DbSet<Author> Authors { get; set; }
public DbSet<Category> Categories { get; set; }
}
public class Book
{
public int Id { get; set; }
public string Title { get; set; }
public decimal Price { get; set; }
public int AuthorId { get; set; }
public int CategoryId { get; set; }
public int PageCount { get; set; }
public bool IsAvailable { get; set; }
public Author Author { get; set; }
public Category Category { get; set; }
}
public class Author
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Book> Books { get; set; }
}
public class Category
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Book> Books { get; set; }
}
Querying Data
The most basic LINQ to Entities query retrieves all records from a table:
// Get all books
public List<Book> GetAllBooks(BookstoreContext context)
{
var books = context.Books.ToList();
return books;
}
When this code executes, Entity Framework generates a SQL query like:
SELECT [b].[Id], [b].[Title], [b].[Price], [b].[AuthorId], [b].[CategoryId],
[b].[PageCount], [b].[IsAvailable]
FROM [Books] AS [b]
Filtering with Where
One of the most common operations is filtering data:
// Get all books with price less than $20
public List<Book> GetAffordableBooks(BookstoreContext context)
{
var affordableBooks = context.Books
.Where(book => book.Price < 20.0m)
.ToList();
return affordableBooks;
}
This generates SQL similar to:
SELECT [b].[Id], [b].[Title], [b].[Price], [b].[AuthorId], [b].[CategoryId],
[b].[PageCount], [b].[IsAvailable]
FROM [Books] AS [b]
WHERE [b].[Price] < 20.0
Ordering Results
You can order your query results:
// Get books ordered by price (lowest to highest)
public List<Book> GetBooksOrderedByPrice(BookstoreContext context)
{
var orderedBooks = context.Books
.OrderBy(book => book.Price)
.ToList();
return orderedBooks;
}
For descending order:
// Get books ordered by price (highest to lowest)
public List<Book> GetBooksOrderedByPriceDescending(BookstoreContext context)
{
var orderedBooks = context.Books
.OrderByDescending(book => book.Price)
.ToList();
return orderedBooks;
}
Selecting Specific Columns
You can select only the columns you need:
// Get only book titles and prices
public List<BookSummary> GetBookSummaries(BookstoreContext context)
{
var bookSummaries = context.Books
.Select(book => new BookSummary
{
Title = book.Title,
Price = book.Price
})
.ToList();
return bookSummaries;
}
public class BookSummary
{
public string Title { get; set; }
public decimal Price { get; set; }
}
Working with Related Data
LINQ to Entities makes it easy to work with related data through navigation properties.
Eager Loading with Include
To load related data in the same query:
// Get all books with their authors
public List<Book> GetBooksWithAuthors(BookstoreContext context)
{
var booksWithAuthors = context.Books
.Include(book => book.Author)
.ToList();
return booksWithAuthors;
}
This generates a SQL JOIN:
SELECT [b].[Id], [b].[Title], [b].[Price], /* other book columns */,
[a].[Id], [a].[Name], /* other author columns */
FROM [Books] AS [b]
INNER JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]
Multiple Levels of Include
You can include multiple related entities:
// Get books with both authors and categories
public List<Book> GetBooksWithAuthorsAndCategories(BookstoreContext context)
{
var books = context.Books
.Include(book => book.Author)
.Include(book => book.Category)
.ToList();
return books;
}
Filtering on Related Data
You can filter records based on related data:
// Get all books by author named "J.K. Rowling"
public List<Book> GetBooksByAuthor(BookstoreContext context, string authorName)
{
var books = context.Books
.Where(book => book.Author.Name == authorName)
.ToList();
return books;
}
Advanced LINQ to Entities Features
Aggregation Functions
LINQ to Entities provides several aggregation functions:
// Get the average book price
public decimal GetAverageBookPrice(BookstoreContext context)
{
return context.Books.Average(book => book.Price);
}
// Count books in a specific category
public int CountBooksInCategory(BookstoreContext context, int categoryId)
{
return context.Books.Count(book => book.CategoryId == categoryId);
}
// Get the most expensive book price
public decimal GetMaxBookPrice(BookstoreContext context)
{
return context.Books.Max(book => book.Price);
}
Grouping Results
You can group query results:
// Get count of books by category
public List<CategoryBookCount> GetBookCountByCategory(BookstoreContext context)
{
var bookCountByCategory = context.Books
.GroupBy(book => book.Category.Name)
.Select(group => new CategoryBookCount
{
CategoryName = group.Key,
BookCount = group.Count()
})
.ToList();
return bookCountByCategory;
}
public class CategoryBookCount
{
public string CategoryName { get; set; }
public int BookCount { get; set; }
}
Paging Results
For large datasets, you often want to retrieve data in pages:
// Get books with paging (page size = 10)
public List<Book> GetBooksPaged(BookstoreContext context, int pageNumber, int pageSize = 10)
{
return context.Books
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToList();
}
This generates SQL with OFFSET and FETCH:
SELECT [b].[Id], [b].[Title], /* other columns */
FROM [Books] AS [b]
ORDER BY [b].[Id]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
Real-World Examples
Let's explore some practical examples you might use in a real application.
Example 1: Building a Book Search Function
public List<Book> SearchBooks(BookstoreContext context, string searchTerm = null,
decimal? maxPrice = null, int? categoryId = null)
{
var query = context.Books.AsQueryable();
// Apply filters conditionally
if (!string.IsNullOrEmpty(searchTerm))
{
query = query.Where(b => b.Title.Contains(searchTerm));
}
if (maxPrice.HasValue)
{
query = query.Where(b => b.Price <= maxPrice.Value);
}
if (categoryId.HasValue)
{
query = query.Where(b => b.CategoryId == categoryId.Value);
}
// Include related data
query = query.Include(b => b.Author).Include(b => b.Category);
// Order by relevance (if searching) or by title
if (!string.IsNullOrEmpty(searchTerm))
{
// Books with the term at the start of the title come first
query = query.OrderByDescending(b => b.Title.StartsWith(searchTerm))
.ThenBy(b => b.Title);
}
else
{
query = query.OrderBy(b => b.Title);
}
return query.ToList();
}
Example 2: Dashboard Statistics
public class BookstoreDashboard
{
public int TotalBooks { get; set; }
public int TotalAuthors { get; set; }
public decimal AverageBookPrice { get; set; }
public int BooksAddedThisMonth { get; set; }
public List<CategorySummary> TopCategories { get; set; }
}
public class CategorySummary
{
public string CategoryName { get; set; }
public int BookCount { get; set; }
}
public BookstoreDashboard GetDashboardStats(BookstoreContext context)
{
var now = DateTime.UtcNow;
var startOfMonth = new DateTime(now.Year, now.Month, 1);
return new BookstoreDashboard
{
TotalBooks = context.Books.Count(),
TotalAuthors = context.Authors.Count(),
AverageBookPrice = context.Books.Average(b => b.Price),
BooksAddedThisMonth = context.Books
.Count(b => b.CreatedAt >= startOfMonth),
TopCategories = context.Categories
.OrderByDescending(c => c.Books.Count)
.Take(5)
.Select(c => new CategorySummary
{
CategoryName = c.Name,
BookCount = c.Books.Count
})
.ToList()
};
}
Best Practices and Optimization
1. Defer Execution Until Needed
LINQ to Entities queries use deferred execution, meaning they don't run until you iterate over the results or call methods like ToList(), First(), etc. Use this to build complex queries:
// The query isn't executed yet
var query = context.Books.Where(b => b.Price < 20);
// Apply more conditions if needed
if (onlyAvailable)
{
query = query.Where(b => b.IsAvailable);
}
// Query executes here when ToList() is called
var results = query.ToList();
2. Use IQueryable for Building Complex Queries
Keep your query as IQueryable<T> until you're ready to execute it:
// Good: Builds query before execution
public IQueryable<Book> GetBookQuery(BookstoreContext context)
{
return context.Books.Where(b => b.IsAvailable);
}
// Usage:
var affordableBooks = GetBookQuery(context)
.Where(b => b.Price < 20)
.ToList();
3. Avoid Loading Unnecessary Data
Only select the columns you need:
// Bad: Loads full objects
var titles = context.Books.ToList().Select(b => b.Title);
// Good: Only retrieves titles from database
var titles = context.Books.Select(b => b.Title).ToList();
4. Use Async Methods for Better Performance
For web applications, use async versions of LINQ methods:
public async Task<List<Book>> GetBooksAsync(BookstoreContext context)
{
return await context.Books
.Where(b => b.IsAvailable)
.ToListAsync();
}
Common Pitfalls
1. N+1 Query Problem
The N+1 query problem occurs when you load a collection and then individually access related entities for each item:
// Bad: Causes N+1 queries
var authors = context.Authors.ToList();
foreach (var author in authors)
{
// This causes a separate query for each author!
Console.WriteLine($"{author.Name} has {author.Books.Count()} books");
}
// Good: Use Include to load related data in one query
var authors = context.Authors.Include(a => a.Books).ToList();
foreach (var author in authors)
{
// No additional queries
Console.WriteLine($"{author.Name} has {author.Books.Count()} books");
}
2. Client vs Server Evaluation
Not all C# operations can be translated to SQL. When EF Core can't translate an operation, it retrieves data from the database and performs the operation in memory:
// Won't work well - custom method can't be translated to SQL
public bool IsPopularBook(Book book) => book.PageCount > 100 && book.Price < 15;
// This causes entire table to be loaded before filtering
var popularBooks = context.Books.Where(b => IsPopularBook(b)).ToList();
// Better - use expressions that EF can translate
var popularBooks = context.Books
.Where(b => b.PageCount > 100 && b.Price < 15)
.ToList();
Summary
LINQ to Entities through Entity Framework provides a powerful and type-safe way to query your databases using C# syntax. In this tutorial, we've covered:
- Basic queries for retrieving, filtering, and ordering data
- Working with related data using Include
- Advanced features like aggregation and grouping
- Building real-world examples
- Best practices and optimization techniques
By mastering LINQ to Entities, you can write clean, maintainable data access code that bridges the gap between your database and your C# application logic.
Additional Resources
Practice Exercises
- Create a LINQ to Entities query that finds all books published in the last year, ordered by most recent first.
- Write a query to find authors who have written more than 3 books.
- Create a book recommendation system that finds similar books based on category and price range.
- Build a query that calculates the average price of books by category and shows which categories have an average price above the overall average.
- Create a paged list of books that includes author information, ordered by author name and then by book title.
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)