In this lesson, you will learn how to write LINQ predicates that allow database providers to translate your code into high-performance SQL using indexes.
When you use IQueryable to fetch data from a database, your LINQ expressions are converted into SQL. A "SARGable" query (Search ARGumentable) is one that is written in a way that allows the database engine to use an index to find rows quickly. If a query is non-SARGable, the database is forced to perform a "table scan," meaning it must check every single row in the database, which is incredibly slow for large datasets.
The Golden Rule: Don't Transform the Column
The most common way to break SARGability is to wrap the database column inside a function or perform a calculation on it within your Where clause. When you do this, the database cannot use the pre-sorted index because it has to calculate the result for every row first.
Consider this example where we want to find orders placed in a specific year:
// BAD: Non-SARGable
// The database must calculate .Year for every single row in the table.
var orders = db.Orders.Where(o => o.OrderDate.Value.Year == 2023);
// GOOD: SARGable
// We perform the calculation on the constant, not the column.
var start = new DateTime(2023, 1, 1);
var end = new DateTime(2024, 1, 1);
var orders = db.Orders.Where(o => o.OrderDate >= start && o.OrderDate < end);
In the "Bad" version, the database cannot use an index on OrderDate because the index doesn't know the "Year" of every date until it calculates it. In the "Good" version, the database can simply jump to the range of dates between start and end using the index.
Another trap to avoid is using string functions like ToUpper() or Trim() on the database column inside your query. If you need to search for a name regardless of case, try to normalize your input before sending it to the query, or rely on the database's natural collation settings.
Key Takeaway: Always write your predicates so that database columns remain raw and unmodified on one side of the operator, while performing any necessary calculations or function calls on the constant value on the other side.