Optimize date/time member comparison translation to use indexes
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
```c#
_ = context.Posts.Where(p => p.PublishedOn.Year > 1990).ToList();
```
... generates the following SQL:
```sql
SELECT [p].[Id], [p].[BlogId], [p].[PublishedOn], [p].[Warning]
FROM [Posts] AS [p]
WHERE DATEPART(year, [p].[PublishedOn]) > 1990
```
... which cannot use an index on PublishedOn. It's possible to rewrite the LINQ query to use the index as follows:
```c#
_ = context.Posts.Where(p => p.PublishedOn > new DateTime(1990, 1, 1)).ToList();
```
However, the natural way to write .NET code is the former pattern, which creates a perf pit of failure (see conversation in https://twitter.com/SQL_Kiwi/status/1581190777992273920).
So far, we generally haven't done optimizations where an efficient LINQ rewrite is possible. However, this is a good example where we may want to consider this. We'd automatically detect only the constrained scenarios in which the rewrite is useful for indexes (equality/comparison operators, DateTime member on left side), and produce the better SQL.
Contributor guide
Assessment
This issue has not been assessed yet.