LINQ & Querying

Level: Beginner

ℹ️ Where This Fits

LINQ (Language Integrated Query) is how you retrieve data with EF Core. Mastering LINQ syntax and methods is essential for writing efficient queries.

ℹ️ What You'll Learn

• LINQ purpose: Query syntax similar to SQL but in C# (Type-safe, IntelliSense)
• Query syntax: from student in context.Students select student (SQL-like)
• Method syntax: context.Students.Where(s => s.Status == Active).ToList() (fluent)
• Where: Filter results (Where(s => s.ClassName == "10-A"))
• Select: Project results (Select(s => new { s.Id, s.Name }))
• OrderBy/ThenBy: Sort ascending (OrderBy(s => s.Name))
• OrderByDescending: Sort descending
• FirstOrDefault: Get first or null
• FirstAsync: Async version (preferred)
• Count/CountAsync: Count matching records
• Any: Check if any match condition
• Distinct: Remove duplicates
• Take/Skip: Pagination (Skip(10).Take(20) for page 2)
• School Management queries: Get students by class, get paid fees, get exam results
• ToList vs ToListAsync: ToListAsync executes asynchronously (better for APIs)
• Query translation: LINQ translated to SQL automatically
• Common mistakes: Not calling ToListAsync, filtering in memory vs database

Method Syntax Examples

// Get all active students
var students = await _context.Students
    .Where(s => s.Status == StudentStatus.Active)
    .ToListAsync();

// Get specific student
var student = await _context.Students
    .FirstOrDefaultAsync(s => s.Id == 101);

// Count students in class
var count = await _context.Students
    .Where(s => s.ClassName == "10-A")
    .CountAsync();

// Get 10 students, ordered by name
var topStudents = await _context.Students
    .OrderBy(s => s.Name)
    .Take(10)
    .ToListAsync();

// Pagination: Get page 2 (20 per page)
var page2 = await _context.Students
    .Skip(20)
    .Take(20)
    .ToListAsync();

Query Syntax Examples

// Get students by class with exams
var query = from student in _context.Students
            where student.ClassName == "10-A"
            orderby student.Name
            select student;

var students = await query.ToListAsync();

// More complex: Select only name and roll number
var query = from student in _context.Students
            where student.Status == StudentStatus.Active
            select new
            {
                student.Name,
                student.RollNumber
            };

var simpleStudents = await query.ToListAsync();

Common Patterns

// Check if record exists
bool exists = await _context.Students
    .AnyAsync(s => s.RollNumber == "SMS-2024-001");

// Get distinct classes
var classes = await _context.Students
    .Select(s => s.ClassName)
    .Distinct()
    .ToListAsync();  // ["10-A", "10-B", "11-A"]

// Group by (aggregation)
var studentsPerClass = await _context.Students
    .GroupBy(s => s.ClassName)
    .Select(g => new
    {
        ClassName = g.Key,
        StudentCount = g.Count()
    })
    .ToListAsync();

Advanced: Join Multiple Tables

var studentExams = await (from student in _context.Students
                          join exam in _context.Exams 
                              on student.Id equals exam.StudentId
                          where student.ClassName == "10-A"
                          select new
                          {
                              student.Name,
                              exam.ExamName,
                              exam.ExamDate
                          })
    .ToListAsync();

School Management Examples

Get fee report by class:

var feeReport = await _context.Students
    .Where(s => s.ClassName == "10-A")
    .Join(_context.FeeAccounts,
        student => student.Id,
        fee => fee.StudentId,
        (student, fee) => new
        {
            student.Name,
            fee.TotalFees,
            fee.PaidAmount,
            Pending = fee.TotalFees - fee.PaidAmount
        })
    .ToListAsync();

Async/Await Pattern (Required in APIs)

// Good - async all the way
public async Task<List<Student>> GetStudentsAsync(string className)
{
    return await _context.Students
        .Where(s => s.ClassName == className)
        .ToListAsync();  // Async execution
}

// Bad - blocks thread
public List<Student> GetStudents(string className)
{
    return _context.Students
        .Where(s => s.ClassName == className)
        .ToList();  // Blocks, not async
}

Best Practices

✓ Always use async methods (.ToListAsync, .FirstAsync) ✓ Filter in database, not in memory ✓ Use method syntax for complex queries ✓ Avoid Select N+1 queries (use Include)

💡 Performance Tip

// BAD - N+1 problem (1 query + N queries for exams)
var students = await _context.Students.ToListAsync();
foreach (var student in students)
{
    var exams = await _context.Exams
        .Where(e => e.StudentId == student.Id)
        .ToListAsync();  // Query N times!
}

// GOOD - Include exams in first query
var students = await _context.Students
    .Include(s => s.Exams)
    .ToListAsync();

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on LINQ & Querying. Try these prompts:

• "What's the difference between query syntax and method syntax?"
• "How do you prevent N+1 queries?"
• "Show me a pagination example with Skip and Take."
• "When should you use FirstOrDefault vs First?"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

-> 06. Relationships & Navigation