英文:
Wrong DateTime comparing in EF Core when column type date
问题
我有实体类：
```csharp
public class Test
{
public Guid Id { get; set; }
[Column(TypeName = "date")]
[DataType(DataType.Date)]
public DateTime Date {get; set;}
}
我使用的是 PostgreSQL。在数据库中，Date 列的类型是 date。如果没有这个属性（[Column(TypeName = "date")]），列的类型将是 timestamp。
问题出现在我尝试从 dbContext 获取实体时：
public List<Test> GetEntities([DataType(DataType.Date)] DateTime date)
{
var list = _context
.Where(x => x.Date == date)
.ToList();
return list;
}
这个方法总是返回空列表。但是如果在 linq 中写成：
_context.Where(x => x.Date.Year == date.Year
&& x.Date.Month == date.Month
&& x.Date.Day == date.Day)
它会正常工作并返回一些值。为什么在第一个版本中它总是返回空列表？
<details>
<summary>英文:</summary>
I have entity class:
public class Test
{
public Guid Id { get; set; }
[Column(TypeName = "date")]
[DataType(DataType.Date)]
public DateTime Date {get; set;}
}
I use PostgreSQL. In database ```Date``` column have type ```date```. And without this attribute (```[Column(TypeName = "date")]```) column have type ```timestamp```.
Problems starts when i try get entities from dbContext:
public List<Test> GetEntities([DataType(DataType.Date)] DateTime date)
{
var list = _context
.Where(x => x.Date == date)
.ToList()
return list;
}
This method always returns empty list. But if in linq write:
_context.Where(x => x.Date.Year == date.Year
&& x.Date.Month == date.Month
&& x.Date.Day == date.Day)
It`s works correct and return some value. Why in first version it's always returns empty list?
</details>
# 答案1
**得分**: 1
Dot net <strike>没有`Date`数据类型</strike>，所以`.Where(x => x.Date == date)`比较日期**和时间**的详细信息。
由于您只想比较`DateTime`的日期部分，应确保时间部分始终相等 - 您可以使用[`Date`][1]属性来实现这一点（它会给您一个具有00:00:00作为时间部分的`DateTime`值）：
```csharp
public List<Test> GetEntities([DataType(DataType.Date)] DateTime date)
=> _context
.Where(x => x.Date.Date == date.Date)
.ToList();
*显然，正如MindSwipe在他的评论中写道，在dot net 6中，Microsoft终于添加了DateOnly结构。
英文:
Dot net <strike>doesn't have a Date data type</strike>*, so .Where(x => x.Date == date) compares the date and time details.
Since you only want to compare the date portion of the DateTime, use should make sure the time portion is always equal - you can do that by using the Date property (which gives you a DateTime value with 00:00:00 as its time portion:
public List<Test> GetEntities([DataType(DataType.Date)] DateTime date)
=> _context
.Where(x => x.Date.Date == date.Date)
.ToList();
*Apperntly, as MindSwipe wrote in his comment, in dot net 6 Microsoft finally added the DateOnly struct.
答案2
得分: 0
尝试按日期范围比较：
public List<Test> GetEntities([DataType(DataType.Date)] DateTime date)
{
var startDate = date.Date;
var endDate = startDate.AddDays(1);
var list = _context
.Where(x => x.Date >= startDate && x.Date < endDate)
.ToList();
return list;
}
英文:
Try to compare by date range:
public List<Test> GetEntities([DataType(DataType.Date)] DateTime date)
{
var startDate = date.Date;
var endDate = startDate.AddDays(1);
var list = _context
.Where(x => x.Date >= startDate && x.Date < endDate)
.ToList();
return list;
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论