0

I have an Oracle database column of type date, the date format is dd-MMM-yy, when I try and retrieve an entity from the database using the Entity Framework and a date comparison I get back no records.

I use this method for the query 

 var calendar = _repository.Single<Calendar>(c => DbFunctions.TruncateTime(c.CALNDR_DATE) == DbFunctions.TruncateTime(calendardate));

I've also tried it in various ways:

 var calendar = _repository.Single<Calendar>(c => c.CALNDR_DATE) == calendardate);

I thought this one would work for sure:

var calendar = _repository.Single<Calendar>(c => c.CALNDR_DATE.Year == calendardate.Year && c.CALNDR_DATE.Month == calendardate.Month && c.CALNDR_DATE.Day == calendardate.Day);

No matter what I've tried I cannot get the method to bring back a calendar record even though I have verified there is a record with the date I'm passing. The value in the calendardate variable is formatted as mm/dd/yyyy. If I query the database directly using SQLDeveloper I get back a result when I search with this query select * from table where CALNDR_DATE = '17-DEC-15'

But no result if I search for: select * from braidss.tmmis98 where CALNDR_DATE = '12/17/2015' - This is what I see if I check what is generated by the LINQ

My question is how can I handle this comparison so the dates match? I have tried several different ways but nothing has worked so far.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
pforsthoff
  • 474
  • 1
  • 3
  • 15
  • If you are using date variables throughout then format is a bit of a distraction. It might be easiest to observe the SQL that is being generated by the LINQ https://stackoverflow.com/questions/4899974/how-to-view-linq-generated-sql-statements. It's not clear what `calendardate` is in this example - is it a date data type? – Nick.Mc Nov 05 '17 at 04:01
  • Yes, its a .net DATETIME data type (The value in the calendardate variable is formatted as mm/dd/yyyy.) – pforsthoff Nov 05 '17 at 04:06
  • What does the generated SQL look like? Try not to get hung up on formatting. An identical value in a variable of type date will take on lots of different formats depending on what how the client decides to format it – Nick.Mc Nov 05 '17 at 04:11
  • The generated SQL looks like - select * from braidss.tmmis98 where CALNDR_DATE = '12/17/2015' which yields no results. The issue is I can't cast this oracle date to a .net DATETIME because of the date format. – pforsthoff Nov 05 '17 at 05:02
  • 1
    I've altered your question to include this info. I assume that this SQL is generated from your second LINQ example? Here's an identical bug - are you using this library (Mindscape Lightspeed ORM) by any chance? I agree that this is a bug with whatever LINQ provider you are using - it should be using a valid format when converting to SQL – Nick.Mc Nov 05 '17 at 05:20
  • I just found the issue and you're right, it was not the formatting. After further testing I discovered that it was finding more than 1 item when I was using the _repository.single repository method and this was throwing an error. I went down a rabbit hole thinking the dates werent matching but they actually were. The fix for me was to alter the linq statement and use: var calendar = _repository.Query(c => c.CALNDR_CODE == calendarcode && c.CALNDR_DATE == calendardate).FirstOrDefault(); – pforsthoff Nov 05 '17 at 05:28
  • 1
    Glad you found the issue. That's definitely not the default Oracle date literal string format so it confused me also. – Nick.Mc Nov 05 '17 at 05:31

0 Answers0