英文:
How to Use Dates in Where Clause in EF Core?
问题
我需要按日期过滤我的查询，但在这种情况下，我不关心存储在SQL数据库中的时间部分。
我首先尝试了类似以下的方法：
var now = DateTime.Now.Date;
Where(x => x.CreatedDate.Date.Compare(now) == 0)
但似乎这会导致所有内容在本地进行检查，使查询变得很慢。如何在不进行本地检查的情况下实现这个目标？
我基本上只是想找到所有今天发生过的结果（2020-01-06）。
英文:
I need to filter my queries by dates but I don't care in this case about time portion of it that is stored in SQL Database.
I first tried to something like
var now = DateTime.Now.Date;
Where(x => x.CreatedDate.Date.Compare(now) == 0)
but this seems to all get locally checked making the query slow. How can I do this without making it do the check locally?
I am pretty much trying to just find all results that would say have happened today(2020-01-06).
答案1
得分: 3
有关可翻译类型的 Lambda / Linq 表达式构建，你可以使用的方法有限。这是因为每个方法都需要额外的代码，以便将其翻译成 SQL 存储表达式。这意味着你必须检查任何想要使用并期望翻译成 SQL 存储表达式的方法是否受支持。
在这种情况下，DateTime.Compare 不受支持。
在这里最简单的做法是进行简单的范围比较，因为时间已包含在你的持久化值中。
var start = DateTime.Now.Date;
var end = start.AddDays(1);
Where(x => x.CreatedDate >= start && x.CreatedDate < end)
这将产生一个sargable查询。
英文:
There are a limited number of methods you can use on translatable types when constructing your Lambda / Linq expressions. This is because each method would need additional code so that it could be translated into a sql store expression. It means that you must check that any methods you want to use and expect to be translated into a sql store expression are supported.
In this case the DateTime.Compare is not supported.
The easiest thing to do here is a simple range comparison because the time is included in your persisted value.
var start = DateTime.Now.Date;
var end = start.AddDays(1);
Where(x => x.CreatedDate >= start && x.CreatedDate < end)
This will result in a sargable query.
答案2
得分: 1
Sure, here's the translated part:
使用：
var now = DateTime.Now.Date
...WHERE(CreatedDate.Date == now)
我刚刚检查了上面的代码，它翻译成以下的SQL查询：
WHERE ((CONVERT(date, [x].[CreatedDate]) = '2019-01-07T00:00:00.000')
我使用了这个方法来查看LINQ翻译成SQL的方式：
英文:
Use
var now = DateTime.Now.Date
...WHERE(CreatedDate.Date == now)
I just checked that above translates to the following SQL query:
WHERE ((CONVERT(date, [x].[CreatedDate]) = '2019-01-07T00:00:00.000')
I used this (link) method to see what LINQ translates to
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论