Filtering with Where
The Where() operator translates C# boolean expressions into a SQL WHERE clause, and EF Core automatically parameterizes any variable referenced inside the lambda, so context.Products.Where(p => p.Price > minPrice) generates a query with @minPrice as a SQL parameter rather than inlining the literal value. This is important for both performance, since the database can reuse cached query plans across calls with different parameter values, and for security, since parameterization inherently prevents SQL injection through the filtered value.
Cricket analogy: It is like a stadium's ticket-scanning system checking each barcode against a live database rule ('valid for today's match') rather than hardcoding one specific ticket number, so the same check works for every match without rewriting the rule.
Pattern Matching and Contains
String filtering with .Contains(), .StartsWith(), or .EndsWith() translates to SQL LIKE expressions with wildcards, for example p.Name.Contains("widget") becomes WHERE p.Name LIKE '%widget%'. On SQL Server, a leading-wildcard LIKE pattern (Contains and EndsWith) cannot use a standard b-tree index efficiently and typically forces a scan, whereas StartsWith() translates to a trailing-wildcard pattern that can still use an index, so it's worth knowing which of these you actually need for large tables.
Cricket analogy: It is like searching a scorecard archive for any player whose name contains 'kumar' anywhere versus one whose name starts with 'Kumar'; the first forces you to scan every entry, the second lets you jump straight to the K section.
// Filtering, sorting, and paging composed together
var page = await context.Products
.Where(p => p.IsActive && p.Price >= minPrice)
.OrderBy(p => p.Name)
.ThenByDescending(p => p.CreatedAt)
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.Select(p => new { p.Id, p.Name, p.Price })
.ToListAsync();Sorting and Dynamic OrderBy
OrderBy()/OrderByDescending() establish the primary sort, and ThenBy()/ThenByDescending() add secondary tie-breaking sorts, all translated into a SQL ORDER BY clause with matching column order and direction. When the sort column needs to be chosen dynamically at runtime, such as from a query-string parameter on an API endpoint, you build the expression conditionally in C# (a switch statement selecting the property, or a dynamic LINQ library) rather than string-concatenating column names, which keeps the query safely parameterized and strongly typed.
Cricket analogy: It is like sorting a batting lineup by strike rate as the default, with average as the tiebreaker, but letting a broadcaster switch the primary sort to economy rate for bowlers on demand without rebuilding the whole scoreboard system.
Offset Paging vs Keyset Paging
Skip() and Take() implement offset paging, translated to SQL OFFSET/FETCH NEXT (or LIMIT/OFFSET on PostgreSQL and MySQL), which is simple to implement but gets progressively slower on large offsets because the database must still count through and discard all skipped rows. Keyset pagination (also called cursor-based or seek pagination) avoids this by filtering on the last seen key instead: Where(p => p.Id > lastSeenId).OrderBy(p => p.Id).Take(pageSize), which lets the database use an index seek regardless of how deep into the result set you are.
Cricket analogy: It is like fast-forwarding a full match replay to see over 40 versus jumping straight to a bookmarked timestamp for over 40; the first gets slower the deeper you scrub, the second is instant regardless of depth.
Skip()/Take() offset paging becomes noticeably slower for high page numbers on large tables because the database engine must still traverse and discard every skipped row internally, even though it never returns them. For infinite-scroll or deep-paging UIs over large datasets, prefer keyset pagination.
Always pair Skip()/Take() with a deterministic OrderBy(); without a defined sort order, SQL databases do not guarantee stable row ordering across pages, which can cause duplicate or skipped rows between requests.
- Where() translates to a parameterized SQL WHERE clause, which both improves plan caching and prevents SQL injection.
- Contains()/EndsWith() translate to leading-wildcard LIKE patterns that typically cannot use a standard index.
- StartsWith() translates to a trailing-wildcard LIKE pattern that can still use an index.
- OrderBy/ThenBy compose into a multi-column ORDER BY; dynamic sort columns should be chosen via C# logic, not string concatenation.
- Skip()/Take() implement offset paging, which slows down on large offsets.
- Keyset pagination filters on the last seen key for consistent performance at any depth.
- Always pair Skip/Take with a deterministic OrderBy to guarantee stable page boundaries.
Practice what you learned
1. Why does EF Core parameterize values referenced in a Where() lambda instead of inlining them as literals?
2. Which string method translates to a SQL LIKE pattern that can still use a standard index?
3. What is the main downside of offset paging using Skip() and Take() on large tables?
4. What must always accompany Skip()/Take() to guarantee stable, non-duplicating pages?
Was this page helpful?
You May Also Like
LINQ to Entities
How EF Core translates LINQ query syntax into SQL, and the rules that govern what can and cannot be pushed down to the database.
Projections with Select
Using Select to shape query results into DTOs, reducing data transfer and avoiding unnecessary change tracking overhead.
Raw SQL Queries with FromSqlRaw
When and how to drop down to raw SQL in EF Core with FromSqlRaw and FromSqlInterpolated, and how to do it without opening a SQL injection hole.
Related Reading
Related Study Notes in Microsoft Technologies
Browse all study notesWindows 10 / UWP Development Study Notes
.NET · 30 topics
Microsoft TechnologiesWindows Batch Scripting Study Notes
Batch · 30 topics
Microsoft TechnologiesMFC (Microsoft Foundation Classes) Study Notes
C++ · 30 topics
Microsoft TechnologiesSilverlight Study Notes
.NET · 30 topics
Microsoft TechnologiesXAML Study Notes
.NET · 30 topics
Microsoft TechnologiesWPF (Windows Presentation Foundation) Study Notes
.NET · 30 topics