dotnet / dotnet/efcore

Optimize date/time member comparison translation to use indexes

Open
#29,368 0 comments 1 reaction 0 assignees View on GitHub
area-perf area-query customer-reported
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.