Dynamic LINQ with EF Core

Introduction

Dynamic LINQ with EF Core is essential for .NET developers building the data layer of ShopNest.Data — Toolliyo's 100-article EF Core path covering DbContext, Code First, migrations, relationships, LINQ, performance, transactions, and enterprise patterns for SQL Server and cloud deployments.

In Indian delivery projects, teams lose sprints when juniors skip dynamic linq with ef core fundamentals — N+1 queries, missing indexes, sync database calls, or untested migrations. This article prevents that on Dynamic Filters.

After this article you will

• Explain Dynamic LINQ with EF Core in plain English and in technical EF Core ORM terms
• Implement dynamic linq with ef core in ShopNest.Data (Dynamic Filters)
• Compare the wrong approach vs the production-ready enterprise approach
• Answer fresher and mid-level EF Core interview questions confidently
• Connect this lesson to Article 41 and the 100-article EF Core roadmap

Prerequisites

• Software: .NET 8 SDK, VS 2022 or VS Code, SQL Server Express / LocalDB
• Knowledge: C# basics
• Previous: Article 39 — Aggregation with LINQ in EF Core
• Time: 28 min reading + 30–45 min hands-on

Concept deep-dive

Level 1 — Analogy

LINQ over EF Core is asking questions in C# — EF translates your query to SQL instead of loading everything into memory first.

Level 2 — Technical

Dynamic LINQ with EF Core translates LINQ to SQL — use IQueryable until the last moment, project to DTOs, and log generated SQL in development.

Level 3 — EF Core data flow

[Application Service / API]
       ▼
[DbContext (Scoped)]
       ▼
[LINQ → Expression Tree → SQL Generator]
       ▼
[SQL Server / PostgreSQL]
       ▼
[Data Reader → Materialization → Change Tracker]
       ▼
[DTO Projection / SaveChangesAsync]

Project structure

ShopNest.Data/
├── ShopNest.Domain/           ← Entity classes
├── ShopNest.Infrastructure/   ← DbContext, configurations, migrations
├── ShopNest.Application/      ← Services, repository interfaces
├── ShopNest.Api/              ← ASP.NET Core host (optional)
└── ShopNest.Tests/            ← Integration tests (SQLite/InMemory)

Hands-on implementation — Dynamic Filters

Follow the steps below to practice Dynamic LINQ with EF Core in Dynamic Filters with a minimal working example.

1. Read the lesson objective and list success criteria.
2. Implement the smallest working version.
3. Test happy path and one failure case.
4. Compare your code to the good example below.
5. Note one interview talking point from what you built.

Anti-pattern (quick hack without tests or error handling)

// ❌ BAD — N+1 queries, sync IO, tracked entities returned to API
foreach (var orderId in orderIds)
{
    var order = _context.Orders.Find(orderId); // sync + N round-trips
    dto.Add(Map(order)); // exposes tracked entity graph
}

Production-style example

// ✅ CORRECT — Dynamic LINQ with EF Core on ShopNest (Dynamic Filters)
public async Task<ProductDto?> GetDtoAsync(int id, CancellationToken ct)
{
    return await _context.Products.AsNoTracking()
        .Where(p => p.Id == id)
        .Select(p => new ProductDto { Id = p.Id, Name = p.Name })
        .FirstOrDefaultAsync(ct);
}

Complete example

await _context.Products
    .Where(p => p.IsPublished)
    .OrderBy(p => p.Name)
    .ToListAsync();

Database design

Product (Id, Name, Price, CategoryId)
Category (Id, Name)
Order (Id, CustomerId, OrderDate, Total)
OrderItem (OrderId, ProductId, Quantity, UnitPrice)

Use FK constraints, indexes on CategoryId and CustomerId, and avoid SELECT * in production LINQ queries.

Common errors & fixes

• N+1 queries in loops — Use Include, projection, or explicit loading.
• Tracking large graphs — Use AsNoTracking for read-only queries.
• Ignoring migration reviews — Review generated SQL before applying to production.

Best practices

• 🟢 Register DbContext as Scoped; inject into services, not singletons
• 🟢 Use async LINQ (ToListAsync, SaveChangesAsync) on I/O paths
• 🟡 Use AsNoTracking() for read-only queries and API list endpoints
• 🟡 Review migration SQL before applying to production
• 🔴 Never use EnsureCreated() in shared or production databases
• 🔴 Log generated SQL in dev; monitor slow queries in production

Interview questions

Fresher level

Q1: Explain Dynamic LINQ with EF Core in an EF Core interview.
A: Define the concept, show a ShopNest entity/query example, mention tracking implications, and one production pitfall you avoided.

Q2: Code First vs Database First — when to use which?
A: Code First for greenfield; scaffold from existing DB for legacy; raw SQL/Dapper for hot reporting paths.

Q3: Explain the EF Core query pipeline.
A: LINQ → expression tree → SQL generator → database → data reader → materialization → optional change tracking.

Mid / senior level

Q4: How do you fix N+1 queries?
A: Use Include/projection, split queries, or explicit loading; verify with logged SQL and profiling.

Q5: DbContext lifetime in ASP.NET Core?
A: Register as Scoped — one context per request; never singleton with concurrent requests.

Q6: EF Core vs Dapper vs raw ADO.NET?
A: EF for productivity and change tracking; Dapper/ADO for hand-tuned reads and bulk operations.

Coding round

Write a LINQ query: top 3 customers by total order value on ShopNest orders.

var top = await _context.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new { CustomerId = g.Key, Total = g.Sum(o => o.GrandTotal) })
    .OrderByDescending(x => x.Total).Take(3).ToListAsync();

Summary & next steps

• Article 40: Dynamic LINQ with EF Core
• Module: Module 4: LINQ · Level: ADVANCED
• Applied to ShopNest.Data — Dynamic Filters

Previous: Aggregation with LINQ in EF Core
Next: One-to-One Relationships in EF Core

Practice: Add one small feature using today's pattern — commit with feat(efcore): article-40.

FAQ