Querying Data in EF Core

Entity Framework Core translates LINQ (Language Integrated Query) expressions into SQL queries that run against your database. Understanding how this translation works is essential for writing efficient data access code.

IQueryable vs IEnumerable

The distinction between IQueryable<T> and IEnumerable<T> is fundamental to how EF Core processes queries.

IQueryable represents a query that has not yet executed. It builds an expression tree that EF Core translates into SQL when you enumerate the results.

csharp
1// IQueryable - builds an expression tree, no SQL executed yet
2IQueryable<Product> query = context.Products
3    .Where(p => p.Price > 10)
4    .OrderBy(p => p.Name);
5
6// SQL executes HERE when results are materialized
7List<Product> results = await query.ToListAsync();

IEnumerable represents an in-memory collection. Once you convert to IEnumerable, any further operations happen in C# memory, not in SQL.

csharp
1// Dangerous: loads ALL products into memory, then filters in C#
2IEnumerable<Product> allProducts = await context.Products.ToListAsync();
3var expensive = allProducts.Where(p => p.Price > 100);
4
5// Better: filters in SQL, only matching rows are loaded
6var expensive = await context.Products
7    .Where(p => p.Price > 100)
8    .ToListAsync();

Deferred Execution

LINQ queries use deferred execution -- the query does not run until you request the results. This lets you compose queries step by step.

csharp
1var query = context.Products.AsQueryable();
2
3// No SQL yet - just building the expression tree
4if (minPrice.HasValue)
5    query = query.Where(p => p.Price >= minPrice.Value);
6
7if (!string.IsNullOrEmpty(category))
8    query = query.Where(p => p.Category == category);
9
10query = query.OrderBy(p => p.Name);
11
12// SQL executes now with all filters combined into one query
13var products = await query.ToListAsync();

The query is only sent to the database when you call a materializing operator:

Client vs Server Evaluation

EF Core attempts to translate your entire LINQ expression into SQL. When it encounters C# code that has no SQL equivalent, it may evaluate that part on the client side.

Server-evaluated (translated to SQL -- preferred):

csharp
1// String methods that EF Core can translate to SQL
2var results = await context.Products
3    .Where(p => p.Name.Contains("Widget"))     // LIKE '%Widget%'
4    .Where(p => p.Name.StartsWith("Pro"))       // LIKE 'Pro%'
5    .Where(p => p.Price > 10 && p.Stock > 0)    // AND in SQL
6    .ToListAsync();

Client-evaluated (runs in C# after loading data -- avoid if possible):

csharp
1// Custom C# method - EF Core cannot translate this to SQL
2var results = await context.Products
3    .AsEnumerable()  // Forces switch to client evaluation
4    .Where(p => CalculateDiscount(p.Price) > 5)
5    .ToList();

Starting with EF Core 3.0, client evaluation of the final Select expression is allowed, but client evaluation in Where or OrderBy throws an exception by default. This prevents accidentally loading entire tables into memory.

How LINQ Translates to SQL

Each LINQ method maps to a SQL clause:

Logging Generated SQL

You can inspect the SQL that EF Core generates by enabling logging:

csharp
1// In Program.cs or DbContext configuration
2options.UseSqlite("Data Source=app.db")
3       .LogTo(Console.WriteLine, LogLevel.Information)
4       .EnableSensitiveDataLogging(); // Shows parameter values (dev only)

Reviewing the generated SQL helps you catch performance issues early, such as missing filters that cause full table scans or N+1 query problems.

Key Takeaways

1. IQueryable builds expression trees that translate to SQL; IEnumerable operates in memory
2. Queries use deferred execution and only run when materialized (e.g., ToListAsync())
3. Keep filters on IQueryable so they execute as SQL, not in C# memory
4. EF Core throws exceptions for untranslatable expressions in Where and OrderBy
5. Always review the generated SQL during development to verify efficiency