3

I am trying to do an equality comparison between a C# datetime and a SQL datetime, which appears to fail due to the precision in which those values are stored.

So say I have a really simple SQL table, with these fields:

ID (PK, int, not null) - identity
Title (text, null)
UpdateTime (datetime, not null)

I create a class object via Entity framework, setting its UpdateTime to DateTime.Now like so:

DateTime now = DateTime.Now;
Title.UpdateTime = now;

When I insert my new object into the table, I see that all the values appear to be stored correctly. Now I want the ID that was created for my object.

This is where things get borked. I try pulling the ID back via LINQ:

Title.ID = context.DBTitles.Where(x=>x.UpdateTime == now).FirstOrDefault().ID;

This throws an exception because the query returns null, despite the fact that the 'now' I've inserted is supposedly the same as the 'now' that was inserted into my table.

So my question : How do I ensure that my C# datetime is equivalent to the datetime stored in SQL? What is the best way to get my ID back?

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
MadHenchbot
  • 1,306
  • 2
  • 13
  • 27

2 Answers2

6

If you are using SQL server, use DateTime2 instead. It has the same precision as the .NET type.

Magnus
  • 45,362
  • 8
  • 80
  • 118
  • Thank you for this! It turns out I didn't need to query the table again to retrieve my ID, but this information was very helpful. I'd mark them both as answers if I could. – MadHenchbot Oct 24 '12 at 21:46
  • Thanks, Magnus. This did the trick for me. In my case, I was filling a traditional DateTime field called LastUpdated with a batch date, and then after the batch, was querying for all records that did not match the batch date so I could fill in a separate DeletedOn column. The equals (or not equals) comparison was always falling. Changed the column type as you suggested and now I'm in like Flynn. =) – Frog Pr1nce Jun 08 '16 at 16:47
3

If you're using Linq, won't the object you're inserting get the ID assigned to it upon commit? You won't need to 'recall' it.

Haedrian
  • 4,240
  • 2
  • 32
  • 53
  • It didn't appear to be coming back automatically. (Maybe I'm missing a step?) The ID is 0 when it goes in, and 0 after my context.SaveChanges() call. – MadHenchbot Oct 24 '12 at 21:23
  • What happens after context.SubmitChanges(); ? – Haedrian Oct 24 '12 at 21:24
  • Egads, you were right. I was letting the DB object go out of scope before checking the ID... Thank you and +1 for getting to the real root of my problem. – MadHenchbot Oct 24 '12 at 21:33