ObjectQuery中的DateTime比较 [英] DateTime comparison in ObjectQuery.Where

问题描述

我使用的是实体框架，我有一个COMMENT实体。 COMMENT有一个DATEMODIFIED属性，它是一个Nullable Date。我正在尝试构建一个将按日期过滤COMMENT的查询，因此我创建一个startDate对象，并执行以下操作：

I'm using Entity Framework, and I have a COMMENT entity. A COMMENT has a DATEMODIFIED property, which is a Nullable Date. I'm trying to build a query that will filter COMMENTs by date, so I create a startDate object, and do the following:

Dim q As ObjectQuery(Of COMMENT) = _ 
   (From c In model.COMMENT Select c)

If startDate.HasValue Then
   q = q.Where(Function(c) startDate.Value <= c.DATEMODIFIED)
End If

问题是q.toList（）没有返回任何评论，即使我认为它应该。数据库中的所有注释都具有DATEMODIFIED值，即使我将DateTime.MinValue作为startDate传递，查询仍然与任何实体不匹配。

The problem is that q.toList() is not returning any comments, even though I think it should. All comments in the database have DATEMODIFIED values, and even if I pass in DateTime.MinValue as the startDate, the query still doesn't match any entities.

我在If-Statement之前设置了一个断点，并使用Visual Studio Watch窗口来尝试看看发生了什么：

I set a breakpoint before the If-Statement and used the Visual Studio Watch Window to try and see what's going on:

q.ToList()(0).DATEMODIFIED    'Returns the expected date 
startDate.Value               'Returns the expected date 
startDate.Value <= q.ToList()(0).DATEMODIFIED    'Returns True...

q = q.Where（谓词）部分，q.ToList（）不再返回任何条目。

But once once it hits the q = q.Where(predicate) part, q.ToList() no longer returns any entries. I'm stumped.

推荐答案

更新：糟糕，我忘记了LINQ to Entities，所有WHERE表达式都被转换为SQL调用，而不是在代码中进行后处理，因此下面的调试建议不一定能起作用。

UPDATE: Oops, I forgot that, with LINQ to Entities, all WHERE expressions are translated into SQL calls instead of being post-processed in code-- so the debugging suggestions below won't necessarily work.

因此，我将首先对您的数据库运行相同的生成的SQL语句，并验证由您的实体框架提供者生成的SQL是否实际返回您期望的数据。 @Craig Stuntz上面的评论肯定是在正确的轨道上，以帮助您做到这一点。一旦你有参数化的SQL，我会尝试从您的代码（使用 System.Data.OracleClient ）直接执行该SQL，并验证您实际从该查询中获取结果。记住要从 ObjectQuery.Parameters 中注入相同的参数值。或者，您可以自行粘贴参数，并从您选择的Oracle客户端应用程序执行查询。

Therefore, I'd start by running the same generated SQL statement against your database, and validating whether the SQL generated by your Entity Framework provider is actually returning the data you expect. @Craig Stuntz's comment above is definitely on the right track here to help you do this. Once you have the parameterized SQL, I'd try executing that SQL directly from your code (using System.Data.OracleClient) and validating that you actually get results back from that query. Remember to inject the same parameter values that you get from ObjectQuery.Parameters. Alternatively, you could stick the parameters in yourself and execute the query from your Oracle client app of choice.

如果您没有从该SQL获取结果，那么这是可能的devArt的提供商正在构建查询错误。

If you don't get results from that SQL, then it's possible that devArt's provider is building the query incorrectly.

您可以忽略这里的内容，因为它适用于对LINQ对对象进行故障排除，但不适用于LINQ-实体

有些想法可以诊断：

首先，在您的观察窗口中尝试：

first, try this in your watch window:

q.Where(Function(c) startDate.Value <= c.DATEMODIFIED).Count()

我假设这将返回零，但是值得消除许多其他变量，以确保您真的不得到任何结果。

I'm assuming this will return zero, but it's worth eliminating as many other variables to make sure you're really not getting any results.

接下来，我会尝试一下定义你的LINQ查询，而不是分别附加Where（），尝试使用两个查询，如下所示： / p>

Next, I'd try is to define your LINQ query a bit differently-- instead of appending the Where() separately, try using two queries, like this:

Dim q As ObjectQuery(Of COMMENT)
If startDate.HasValue Then
    q = (From c In model.COMMENT Where startDate.Value <= c.DATEMODIFIED Select c)
Else
    q = (From c In model.COMMENT Select c)
End If

如果这样工作，那么Where子句如何附加到您现有的LINQ查询中，可能是DBMS实体中的错误 - 框架提供商？

If this works, then there's something wrong with how the Where clause is being attached to your existing LINQ query-- perhaps a bug in your DBMS's entity-framework provider?

如果仍然不起作用，我要诊断的下一步将是验证where子句中的代码被调用，并检查传递到该代码中的值。我无法弄清楚如何在VB中设置线内断点，就像在C＃中可以做到的一样，但是您可以轻松（临时）将lambda重构为单独的函数，并将断点设置在那里。像这样：

If that still doesn't work, the next step I'd take to diagnose would be to verify that the code inside the where clause is being called, and checking the values passed into that code. I couldn't figure out how to set intra-line breakpoints in VB like one can do in C#, but you can easily (temporarily) refactor your lambda into a separate function and set the breakpoint there. Like this:

Sub Main()
    Dim testDate As Date = New Date(2005, 1, 1)
    Dim x = New List(Of Date?)
    x.Add(New Date(2009, 1, 1))
    x.Add(New Date(2008, 1, 1))
    x.Add(New Date(2007, 1, 1))
    x.Add(New Date(2006, 1, 1))
    x.Add(New Date(2005, 1, 1))
    x.Add(New Date(2004, 1, 1))
    x.Add(New Date(2003, 1, 1))
    x.Add(New Date(2002, 1, 1))
    x.Add(New Date(2001, 1, 1))
    Dim y = From n In x Select n
    y = y.Where(Function(val) test(val, testDate))
    Dim z = y.ToArray()
End Sub

Function test(ByVal date1 As Date, ByVal date2 As Date) As Boolean
    test = date1 >= date2
End Function

检查正在发送的值进入你的比较功能 - 它们是否有效？比较是否返回您期望的内容？

Check the values being sent into your comparison function-- are they valid? Does the comparison return what you expect it to?

这篇关于ObjectQuery中的DateTime比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！