Entity Framework (EF) is a popular Object-Relational Mapper (ORM) in C# that allows developers to interact with databases using LINQ (Language Integrated Query). LINQ enables querying the database in a clean, readable, and type-safe manner. However, writing LINQ queries efficiently is crucial to avoid performance bottlenecks.

In this article, we’ll explore how to use LINQ with Entity Framework to perform optimized queries while following best practices for performance.

---

Why Use LINQ with Entity Framework?

• Type Safety: Queries are strongly typed and checked at compile time.
• Readable Syntax: The query structure resembles SQL, improving readability.
• Database Abstraction: Works with various database providers, abstracting SQL details.

---

Setting Up Entity Framework

To get started, ensure you have Entity Framework installed:

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

Define a Sample Database Context and Models

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourConnectionStringHere");
    }
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Category { get; set; }
}

---

Optimized LINQ Queries in Entity Framework

---

1. Basic LINQ Query Example

Fetch all products from the database:

using (var context = new AppDbContext())
{
    var products = context.Products.ToList();

    foreach (var product in products)
    {
        Console.WriteLine($"Name: {product.Name}, Price: {product.Price}");
    }
}

---

2. Filtering Data with Where

Filter products by category:

using (var context = new AppDbContext())
{
    var filteredProducts = context.Products
        .Where(p => p.Category == "Electronics")
        .ToList();

    foreach (var product in filteredProducts)
    {
        Console.WriteLine($"Name: {product.Name}, Category: {product.Category}");
    }
}

Optimization Tip:

• Use Server-Side Filtering: Filter data before loading it into memory to reduce data transfer overhead.

---

3. Selecting Specific Columns with Select

Fetch only specific fields to reduce the amount of data retrieved:

using (var context = new AppDbContext())
{
    var productNames = context.Products
        .Where(p => p.Price > 500)
        .Select(p => new { p.Name, p.Price })
        .ToList();

    foreach (var product in productNames)
    {
        Console.WriteLine($"Name: {product.Name}, Price: {product.Price}");
    }
}

Optimization Tip:

• Avoid Loading Entire Entities: Use Select to retrieve only the fields you need.

---

4. Sorting with OrderBy and OrderByDescending

Sort products by price in descending order:

using (var context = new AppDbContext())
{
    var sortedProducts = context.Products
        .OrderByDescending(p => p.Price)
        .ToList();

    foreach (var product in sortedProducts)
    {
        Console.WriteLine($"Name: {product.Name}, Price: {product.Price}");
    }
}

---

5. Pagination with Skip and Take

Fetch data in pages to improve performance:

int pageNumber = 2;
int pageSize = 5;

using (var context = new AppDbContext())
{
    var paginatedProducts = context.Products
        .OrderBy(p => p.Name)
        .Skip((pageNumber - 1) * pageSize)
        .Take(pageSize)
        .ToList();

    foreach (var product in paginatedProducts)
    {
        Console.WriteLine($"Name: {product.Name}, Price: {product.Price}");
    }
}

Optimization Tip:

• Use Pagination for Large Datasets: Load only what the user needs to see.

---

6. Using Include for Eager Loading

Assume Product has a related Supplier entity:

public class Supplier
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Product> Products { get; set; }
}

Fetch products with suppliers using Include:

using Microsoft.EntityFrameworkCore;

using (var context = new AppDbContext())
{
    var productsWithSuppliers = context.Products
        .Include(p => p.Supplier)
        .ToList();

    foreach (var product in productsWithSuppliers)
    {
        Console.WriteLine($"Product: {product.Name}, Supplier: {product.Supplier?.Name}");
    }
}

Optimization Tip:

• Avoid N+1 Queries: Use Include to load related data in a single query.

---

7. Aggregations Using LINQ

Find the most expensive product:

using (var context = new AppDbContext())
{
    var maxPrice = context.Products.Max(p => p.Price);
    Console.WriteLine($"Most Expensive Product Price: {maxPrice}");
}

Find the total number of products:

int productCount = context.Products.Count();
Console.WriteLine($"Total Products: {productCount}");

---

8. Combining Queries Using LINQ Methods

Use multiple LINQ methods for complex queries:

using (var context = new AppDbContext())
{
    var result = context.Products
        .Where(p => p.Category == "Electronics" && p.Price > 500)
        .OrderBy(p => p.Name)
        .Select(p => new { p.Name, p.Price })
        .ToList();

    foreach (var product in result)
    {
        Console.WriteLine($"Name: {product.Name}, Price: {product.Price}");
    }
}

---

Performance Tips for LINQ with Entity Framework

1. Use AsNoTracking(): If you only need to read data without modifying it. var products = context.Products.AsNoTracking().ToList();
2. Avoid ToList() in the Middle of Queries: Apply ToList() only when the query is complete.
3. Optimize Queries:
   • Filter results before loading them into memory.
   • Use projections (Select) for lightweight queries.
4. Use Caching:
   • Implement caching for frequently accessed data.
5. Profile Queries:
   • Use database profiling tools like SQL Server Profiler or EF Core’s logging feature.

---

Conclusion

Using LINQ with Entity Framework allows you to create efficient, type-safe, and readable database queries. By following best practices such as filtering data server-side, selecting specific columns, and using eager loading, you can build high-performing C# applications. Apply these LINQ techniques to ensure your queries are optimized and scalable.