Performing Queries with .NET

This document explains how to perform various types of queries against data sources using .NET technologies, focusing on LINQ (Language Integrated Query) and data access libraries like Entity Framework Core.

Introduction to Querying

Querying is the process of retrieving specific data from a collection of data. .NET provides powerful tools to make this process intuitive and efficient. LINQ allows you to write queries in a C# or Visual Basic syntax directly within your code, offering compile-time type checking and IntelliSense support.

LINQ Basics

LINQ provides a consistent model for querying various data sources, including in-memory collections, databases, XML documents, and more. The core concepts involve:

• Query Syntax: A declarative syntax that resembles SQL.
• Method Syntax: A fluent API using extension methods.
• Data Sources: Objects that implement the IEnumerable<T> or IQueryable<T> interfaces.
• Query Operators: Methods that perform operations like filtering (Where), projecting (Select), ordering (OrderBy), grouping (GroupBy), etc.

Example: Querying an In-Memory Collection

Let's consider a simple list of objects:

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

List<Product> products = new List<Product>
{
    new Product { Id = 1, Name = "Laptop", Price = 1200.00m, CategoryId = 1 },
    new Product { Id = 2, Name = "Mouse", Price = 25.00m, CategoryId = 1 },
    new Product { Id = 3, Name = "Keyboard", Price = 75.00m, CategoryId = 1 },
    new Product { Id = 4, Name = "Monitor", Price = 300.00m, CategoryId = 2 },
    new Product { Id = 5, Name = "Webcam", Price = 50.00m, CategoryId = 2 }
};
        

Using LINQ query syntax to find all products with a price greater than 100:

var expensiveProductsQuerySyntax = from p in products
                                   where p.Price > 100
                                   orderby p.Price descending
                                   select p;

foreach (var product in expensiveProductsQuerySyntax)
{
    Console.WriteLine($"{product.Name} - ${product.Price}");
}
        

Using LINQ method syntax for the same query:

var expensiveProductsMethodSyntax = products
                                    .Where(p => p.Price > 100)
                                    .OrderByDescending(p => p.Price)
                                    .ToList(); // Execute the query immediately

foreach (var product in expensiveProductsMethodSyntax)
{
    Console.WriteLine($"{product.Name} - ${product.Price}");
}
        

Querying Databases with Entity Framework Core

Entity Framework Core (EF Core) is an Object-Relational Mapper (ORM) that simplifies database access. It allows you to query your database using LINQ, translating your queries into SQL commands that are executed against the database.

DbContext and DbSet

Your interaction with the database typically starts with a DbContext. A DbSet<TEntity> property on your context represents a table in your database.

public class ApplicationDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }

    // Constructor and OnConfiguring methods for database connection
    // ...
}
        

Performing Queries with EF Core

You can use the same LINQ syntax against DbSet<TEntity> properties:

using (var context = new ApplicationDbContext())
{
    // Query for products in a specific category
    var electronicsProducts = context.Products
                                     .Where(p => p.CategoryId == 1)
                                     .ToList(); // Executes the query and brings data into memory

    // Querying and joining with another table (e.g., Categories)
    var productsWithCategoryNames = from p in context.Products
                                    join c in context.Categories on p.CategoryId equals c.Id
                                    where c.Name == "Electronics"
                                    select new { ProductName = p.Name, CategoryName = c.Name };

    foreach (var item in productsWithCategoryNames)
    {
        Console.WriteLine($"{item.ProductName} in {item.CategoryName}");
    }

    // Projection to a new anonymous type
    var productNamesAndPrices = context.Products
                                       .Select(p => new { Name = p.Name, Price = p.Price });

    foreach (var item in productNamesAndPrices)
    {
        Console.WriteLine($"{item.Name}: ${item.Price}");
    }
}
        

Advanced Querying Techniques

• Filtering: Use Where() to select data based on specified criteria.
• Sorting: Use OrderBy(), OrderByDescending(), ThenBy(), ThenByDescending() for sorting results.
• Grouping: Use GroupBy() to group elements based on a key.
• Joining: Use Join() to combine elements from two sequences.
• Aggregation: Use methods like Count(), Sum(), Average(), Min(), Max() to perform calculations on a set of values.
• Paging: Use Skip() and Take() to implement pagination.

Example: Paging

int pageNumber = 2;
int pageSize = 10;

var pagedProducts = products
                    .Skip((pageNumber - 1) * pageSize)
                    .Take(pageSize)
                    .ToList();

// For database queries with EF Core, these operations are translated to SQL's OFFSET/FETCH or similar.
        

Conclusion

Mastering LINQ and data access libraries like Entity Framework Core is essential for building efficient and data-driven .NET applications. By leveraging these powerful tools, you can write expressive and type-safe queries that interact seamlessly with various data sources.