DateTime comparison in ObjectQuery.Where

Question

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

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.

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...

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

Answer 1

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.

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.

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

you can ignore what's below here, since it applies to troubleshooting LINQ-to-Objects but not LINQ-to-Entities

Some ideas to diagnose this:

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.

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

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?

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?