Querying Data with LINQ

What is LINQ?

LINQ (Language Integrated Query) allows you to query data using C# syntax instead of SQL. EF Core translates LINQ to SQL.

Basic Queries

Get All Records

// Synchronous
var products = _context.Products.ToList();

// Asynchronous (recommended)
var products = await _context.Products.ToListAsync();

Find by Primary Key

// Find by ID
var product = await _context.Products.FindAsync(1);

// Returns null if not found
if (product == null)
{
    return NotFound();
}

First, Single, and FirstOrDefault

// First (throws if empty)
var product = await _context.Products.FirstAsync();

// FirstOrDefault (returns null if empty)
var product = await _context.Products.FirstOrDefaultAsync();

// Single (throws if 0 or >1 results)
var product = await _context.Products.SingleAsync(p => p.Id == 1);

// SingleOrDefault (returns null if 0, throws if >1)
var product = await _context.Products.SingleOrDefaultAsync(p => p.Id == 1);

Filtering with Where

// Simple filter
var expensiveProducts = await _context.Products
    .Where(p => p.Price > 100)
    .ToListAsync();

// Multiple conditions (AND)
var products = await _context.Products
    .Where(p => p.Price > 100 && p.Quantity > 0)
    .ToListAsync();

// OR condition
var products = await _context.Products
    .Where(p => p.Price < 50 || p.Price > 500)
    .ToListAsync();

// String operations
var products = await _context.Products
    .Where(p => p.Name.Contains("Laptop"))
    .ToListAsync();

// StartsWith, EndsWith
var products = await _context.Products
    .Where(p => p.Name.StartsWith("Dell"))
    .ToListAsync();

Projecting with Select

// Select specific properties
var productNames = await _context.Products
    .Select(p => p.Name)
    .ToListAsync();

// Anonymous type
var products = await _context.Products
    .Select(p => new 
    {
        p.Id,
        p.Name,
        p.Price
    })
    .ToListAsync();

// DTO projection
var products = await _context.Products
    .Select(p => new ProductDto
    {
        Id = p.Id,
        Name = p.Name,
        Price = p.Price
    })
    .ToListAsync();

Ordering

// Ascending order
var products = await _context.Products
    .OrderBy(p => p.Price)
    .ToListAsync();

// Descending order
var products = await _context.Products
    .OrderByDescending(p => p.Price)
    .ToListAsync();

// Multiple ordering (ThenBy)
var products = await _context.Products
    .OrderBy(p => p.Category)
    .ThenByDescending(p => p.Price)
    .ToListAsync();

Paging

// Page 1: first 20 products
var products = await _context.Products
    .OrderBy(p => p.Id)
    .Take(20)
    .ToListAsync();

// Page 2: skip 20, take 20
var products = await _context.Products
    .OrderBy(p => p.Id)
    .Skip(20)
    .Take(20)
    .ToListAsync();

// Generic paging
int page = 2;
int pageSize = 20;

var products = await _context.Products
    .OrderBy(p => p.Id)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

Aggregation

// Count
var totalProducts = await _context.Products.CountAsync();

// Count with condition
var expensiveCount = await _context.Products
    .CountAsync(p => p.Price > 100);

// Sum
var totalValue = await _context.Products
    .SumAsync(p => p.Price * p.Quantity);

// Average
var avgPrice = await _context.Products
    .AverageAsync(p => p.Price);

// Min and Max
var minPrice = await _context.Products.MinAsync(p => p.Price);
var maxPrice = await _context.Products.MaxAsync(p => p.Price);

// Any (check if exists)
var hasExpensive = await _context.Products
    .AnyAsync(p => p.Price > 1000);

Grouping

// Group by category
var grouped = await _context.Products
    .GroupBy(p => p.CategoryId)
    .Select(g => new
    {
        CategoryId = g.Key,
        Count = g.Count(),
        TotalValue = g.Sum(p => p.Price * p.Quantity),
        AvgPrice = g.Average(p => p.Price)
    })
    .ToListAsync();

Complete InvenTrack Example

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly InvenTrackDbContext _context;

    public ProductsController(InvenTrackDbContext context)
    {
        _context = context;
    }

    // GET /api/products?page=1&pageSize=20&search=laptop&minPrice=100
    [HttpGet]
    public async Task<ActionResult> GetProducts(
        [FromQuery] int page = 1,
        [FromQuery] int pageSize = 20,
        [FromQuery] string? search = null,
        [FromQuery] decimal? minPrice = null,
        [FromQuery] decimal? maxPrice = null)
    {
        var query = _context.Products.AsQueryable();

        // Apply filters
        if (!string.IsNullOrEmpty(search))
        {
            query = query.Where(p => p.Name.Contains(search));
        }

        if (minPrice.HasValue)
        {
            query = query.Where(p => p.Price >= minPrice.Value);
        }

        if (maxPrice.HasValue)
        {
            query = query.Where(p => p.Price <= maxPrice.Value);
        }

        // Get total count
        var totalCount = await query.CountAsync();

        // Apply paging
        var products = await query
            .OrderBy(p => p.Name)
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .Select(p => new
            {
                p.Id,
                p.Name,
                p.Price,
                p.Quantity
            })
            .ToListAsync();

        return Ok(new
        {
            data = products,
            pagination = new
            {
                page,
                pageSize,
                totalCount,
                totalPages = (int)Math.Ceiling(totalCount / (double)pageSize)
            }
        });
    }

    // GET /api/products/statistics
    [HttpGet("statistics")]
    public async Task<ActionResult> GetStatistics()
    {
        var stats = new
        {
            totalProducts = await _context.Products.CountAsync(),
            totalValue = await _context.Products
                .SumAsync(p => p.Price * p.Quantity),
            averagePrice = await _context.Products.AverageAsync(p => p.Price),
            minPrice = await _context.Products.MinAsync(p => p.Price),
            maxPrice = await _context.Products.MaxAsync(p => p.Price)
        };

        return Ok(stats);
    }
}

Best Practices

• Always use async: Use ToListAsync(), FirstOrDefaultAsync(), etc.
• Filter early: Apply Where() before Select() for efficiency
• Project early: Use Select() to reduce data transfer
• AsQueryable(): Build queries dynamically
• Avoid N+1: Use Include() for related data (next section)
• Use Any(): Instead of Count() > 0
• Pagination: Always paginate large result sets

Key Takeaways

• LINQ: Query databases using C# syntax
• Where(): Filter records
• Select(): Project specific columns
• OrderBy(): Sort results
• Skip/Take: Implement paging
• Count/Sum/Average: Aggregation functions
• GroupBy(): Group and aggregate
• Async methods: Always use for I/O operations