EF Core中在日期列的类型不匹配时比较错误的日期时间。

huangapple go评论67阅读模式
英文:

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 = &quot;date&quot;)]```) 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&#39;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 =&gt; 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&lt;Test&gt; GetEntities([DataType(DataType.Date)] DateTime date)
  =&gt; _context
        .Where(x =&gt; 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&lt;Test&gt; GetEntities([DataType(DataType.Date)] DateTime date)
{
    var startDate = date.Date;
    var endDate = startDate.AddDays(1);

    var list = _context
       .Where(x =&gt; x.Date &gt;= startDate &amp;&amp; x.Date &lt; endDate)
       .ToList();

    return list;
}

huangapple
  • 本文由 发表于 2023年1月9日 14:58:55
  • 转载请务必保留本文链接:https://go.coder-hub.com/75054014.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定