Querying databases with LINQ

This tutorial explores how to query databases using Language Integrated Query (LINQ) with Entity Framework Core (EF Core) in C#. LINQ provides a powerful and expressive way to interact with data, allowing you to write queries directly in your C# code instead of using raw SQL.

We'll cover basic and advanced querying techniques, including filtering, sorting, projection, and aggregation.

Setting up Entity Framework Core

Before querying, you need to set up EF Core. This involves installing the necessary NuGet packages, defining your entity classes to mirror your database tables, and creating a DbContext class to manage the connection. The DbContext class includes DbSet properties for each entity you want to query. The OnModelCreating method is used to configure database relationships and other options.

// 1. Install necessary NuGet packages:
//   - Microsoft.EntityFrameworkCore
//   - Microsoft.EntityFrameworkCore.SqlServer (or your database provider)
//   - Microsoft.EntityFrameworkCore.Tools (for migrations)

// 2. Define your entity classes (e.g., 'Product', 'Category').  These represent tables in your database.

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

public class Category
{
    public int CategoryId { get; set; }
    public string Name { get; set; }
    public ICollection<Product> Products { get; set; }
}

// 3. Create your DbContext class. This class represents the connection to your database.
using Microsoft.EntityFrameworkCore;

public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
    {
    }

    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure relationships, keys, etc.
        modelBuilder.Entity<Product>()
            .HasOne(p => p.Category)
            .WithMany(c => c.Products)
            .HasForeignKey(p => p.CategoryId);
    }
}

Basic LINQ Query

This code demonstrates basic LINQ queries using EF Core. The using statement ensures that the DbContext is properly disposed of after use. The context.Products property accesses the Products DbSet. The Where() method filters the results based on a condition. The ToList() method executes the query against the database and returns the results as a list of objects.

using (var context = new MyDbContext(options))
{
    // Retrieve all products
    var allProducts = context.Products.ToList();

    // Retrieve products with a price greater than $50
    var expensiveProducts = context.Products
                                     .Where(p => p.Price > 50)
                                     .ToList();

    // Retrieve products whose names contain 'keyboard'
    var keyboardProducts = context.Products
                                    .Where(p => p.Name.Contains("keyboard"))
                                    .ToList();

    // Print the results
    foreach (var product in expensiveProducts)
    {
        Console.WriteLine($"Product: {product.Name}, Price: {product.Price}");
    }
}

Ordering and Sorting

The OrderBy() and OrderByDescending() methods allow you to sort the results of your query. You can chain multiple ThenBy() methods to specify secondary sorting criteria. Remember that ordering is done in memory after the data is retrieved, so it's best to filter the data before ordering to improve performance.

using (var context = new MyDbContext(options))
{
    // Retrieve products ordered by price in ascending order
    var productsByPriceAscending = context.Products
                                         .OrderBy(p => p.Price)
                                         .ToList();

    // Retrieve products ordered by name in descending order
    var productsByNameDescending = context.Products
                                        .OrderByDescending(p => p.Name)
                                        .ToList();

    // You can chain ordering methods for more complex sorting
    var productsByPriceThenName = context.Products
                                        .OrderBy(p => p.Price)
                                        .ThenBy(p => p.Name)
                                        .ToList();

}

Projection and Anonymous Types

Projection allows you to select only the specific properties you need from your entities. You can project into anonymous types or DTOs. Anonymous types are useful for simple projections when you don't need to reuse the result type. DTOs are useful for more complex projections and when you need to pass the data to other parts of your application. Projecting only necessary columns improves performance by reducing the amount of data transferred from the database.

using (var context = new MyDbContext(options))
{
    // Project into an anonymous type with only the product name and price
    var productInfo = context.Products
                            .Select(p => new { p.Name, p.Price })
                            .ToList();

    // Project into a DTO (Data Transfer Object)
    var productDtos = context.Products
                            .Select(p => new ProductDto
                            {
                                Name = p.Name,
                                Price = p.Price
                            })
                            .ToList();
}

//Example DTO
public class ProductDto
{
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Joining Tables

Joining tables allows you to combine data from multiple related entities. EF Core supports implicit joins using navigation properties (e.g., p.Category) and explicit joins using the Join() method. Implicit joins are generally easier to read and write, while explicit joins provide more control over the join conditions and result projection.

using (var context = new MyDbContext(options))
{
    // Implicit join using navigation properties
    var productsWithCategory = context.Products
                                     .Where(p => p.Category.Name == "Electronics")
                                     .ToList();

    // Explicit join using LINQ's Join method
    var productsWithCategoryExplicit = context.Products
        .Join(
            context.Categories,
            product => product.CategoryId,
            category => category.CategoryId,
            (product, category) => new
            {
                ProductName = product.Name,
                CategoryName = category.Name
            })
        .ToList();
}

Aggregation

Aggregation functions allow you to perform calculations on a set of values. LINQ provides methods like Average(), Count(), Max(), Min(), and Sum() for performing common aggregations. These methods execute directly on the database, improving performance compared to retrieving all data and calculating the aggregation in memory.

using (var context = new MyDbContext(options))
{
    // Calculate the average price of all products
    var averagePrice = context.Products.Average(p => p.Price);

    // Calculate the total number of products
    var productCount = context.Products.Count();

    // Find the maximum price
    var maxPrice = context.Products.Max(p => p.Price);

    // Find the minimum price
    var minPrice = context.Products.Min(p => p.Price);

    // Calculate the sum of all prices
    var totalPrice = context.Products.Sum(p => p.Price);
}

Real-Life Use Case

Imagine you're building an e-commerce website. You need to display a list of products, filtered by category, and sorted by price. This code demonstrates how to achieve this using LINQ and EF Core. The Include() method is used to eager load the Category entity, which avoids the N+1 query problem. The function then dynamically builds the query based on the input parameters.

// Scenario:  Displaying a list of products on an e-commerce website, filtered by category and sorted by price.

public List<Product> GetProductsByCategoryAndPrice(string categoryName, bool ascendingOrder)
{
    using (var context = new MyDbContext(options))
    {
        IQueryable<Product> query = context.Products.Include(p => p.Category); // Include Category to avoid N+1 queries.

        if (!string.IsNullOrEmpty(categoryName))
        {
            query = query.Where(p => p.Category.Name == categoryName);
        }

        if (ascendingOrder)
        {
            query = query.OrderBy(p => p.Price);
        }
        else
        {
            query = query.OrderByDescending(p => p.Price);
        }

        return query.ToList();
    }
}

Best Practices

• Use AsNoTracking() for read-only queries: When you're only reading data and don't need to track changes, use AsNoTracking() to improve performance. This tells EF Core not to track the entities, reducing memory usage and overhead.
• Use Include() to avoid N+1 queries: When querying related entities, use Include() to eager load the related data in a single query. This avoids the N+1 query problem, where you fetch the parent entity in one query and then make separate queries for each related entity.
• Filter and project early: Apply filters and projections as early as possible in your query. This reduces the amount of data that needs to be transferred from the database and processed in memory.
• Use compiled queries for frequently executed queries: Compiled queries can improve performance by caching the query execution plan.
• Consider using stored procedures for complex queries: For complex queries that are difficult to express in LINQ, consider using stored procedures. Stored procedures can provide better performance and security.

Interview Tip

Be prepared to explain the differences between IQueryable and IEnumerable. IQueryable represents a query that can be executed against a data source, such as a database. IEnumerable represents a collection of objects in memory. When using LINQ with EF Core, you typically start with an IQueryable and then convert it to an IEnumerable when you need to execute the query and retrieve the results.

Also, understanding the difference between eager loading (Include()), lazy loading, and explicit loading is essential. Explain the trade-offs between these approaches.

When to Use LINQ to Entities

Suitable Situations:

• Simplified Database Interaction: Ideal when you want to interact with relational databases using a more natural, object-oriented approach within C#.
• Reduced Boilerplate Code: It's beneficial when you aim to minimize the amount of raw SQL you need to write and maintain, leading to cleaner and more readable code.
• Type Safety and Compile-Time Checking: LINQ to Entities is helpful when you want type safety and compile-time error checking for your database queries, reducing runtime errors.
• Complex Queries: Use when you need to perform complex queries, filtering, sorting, or grouping operations on data stored in relational databases.

Situations to Avoid:

• Extremely Performance-Critical Scenarios: In cases where every millisecond counts, hand-optimized SQL queries might offer better performance than LINQ to Entities.
• Legacy Databases with No Entity Framework Support: If you are working with very old or unusual database systems, there might not be adequate Entity Framework providers, making LINQ to Entities impractical.
• Queries Involving Stored Procedures or Database-Specific Features: If you need to leverage database-specific features or stored procedures extensively, it might be more straightforward to use raw SQL or ADO.NET.

Memory Footprint Considerations

Factors Affecting Memory Footprint:

• Amount of Data Retrieved: The more data your LINQ query retrieves from the database, the larger the memory footprint.
• Entity Complexity: More complex entity models with numerous properties and relationships will consume more memory.
• Eager Loading: Eager loading related entities using Include() can increase memory usage, especially for large and complex object graphs.
• Data Projection: Projecting only the necessary columns from the database can help reduce the memory footprint by retrieving less data.
• AsNoTracking(): Using AsNoTracking() can reduce memory usage by disabling change tracking for read-only queries.

Alternatives to LINQ to Entities

Raw SQL with ADO.NET:

• Description: Write SQL queries directly and use ADO.NET classes (e.g., SqlConnection, SqlCommand, SqlDataReader) to execute them.
• Use Cases: High-performance scenarios, complex database-specific features, or when working with very old databases without EF support.
• Advantages: Full control over SQL, potential for optimization.
• Disadvantages: More boilerplate code, manual mapping of results to objects, no compile-time type checking.

Dapper:

• Description: A lightweight ORM that extends IDbConnection with extension methods for executing queries and mapping results to objects.
• Use Cases: When you need a simple and fast way to execute SQL queries and map results to objects without the overhead of a full ORM.
• Advantages: Excellent performance, easy to use, minimal overhead.
• Disadvantages: Requires writing SQL, limited features compared to EF.

Stored Procedures:

• Description: Precompiled SQL code stored in the database and executed by name.
• Use Cases: Complex business logic, data validation, performance optimization, security.
• Advantages: Better performance, code reusability, security benefits.
• Disadvantages: Can be harder to maintain, require DBA involvement.

Pros and Cons of LINQ to Entities

Pros:

• Simplified Database Interaction: LINQ to Entities provides a more object-oriented way to query databases, making it easier to write and understand database queries in C#.
• Reduced Boilerplate Code: It reduces the amount of raw SQL code, leading to cleaner and more maintainable code.
• Type Safety: LINQ to Entities offers type safety and compile-time checking, reducing runtime errors.
• Abstraction: It abstracts away the underlying database schema, allowing you to focus on the business logic rather than the database details.

Cons:

• Performance Overhead: LINQ to Entities can sometimes introduce performance overhead due to the abstraction layer and the translation of LINQ queries to SQL.
• Complexity: Complex LINQ queries can become difficult to understand and debug.
• Limited Control: You have less control over the generated SQL compared to writing raw SQL queries.
• Debugging Challenges: Debugging complex LINQ queries can be challenging, especially when performance issues arise.