Entity Framework and DateTime2

I’ve decided that anything that takes me more than a day to figure out is worth a blog post. It’s been awhile since I’ve posted anything development related, but I’ve just recently started kicking the tires on the Entity Framework. I started with a simple example, and started with a very simple relational database containing 2 tables.

I then moved over into Visual Studio, and used the Entity Framework toolset to reverse engineer the database. The generated model was extremely simple, so I was all set to create a simple form to add a record into my database. I started out with the form below:

The code behind contained the following in the page_load method on postback:


                        ScheduleRequest sr = new ScheduleRequest {Name = tbName.Text, Comments = tbComments.Text, ModifiedDate = DateTime.Now };
                    catch (Exception ex)
                        divMessage.Visible = true;
                        divMessage.Attributes.Add("class", "message-error");
                        divMessage.InnerText = "There was an error processing your request." + ex.ToString();

After submitting the form, I hurried into the database to see how much time the Entity Framework was going to save me. The record was there, however my code was failing. A quick breakpoint gave me access to the exception, and I was given the following error:

Store update, insert, or delete statement affected an unexpected number of rows (0).

I did my normal googling, and quickly found a resolution that suggested that someone else had modified the context, something about concurrency, and many other odds and ends. Since I was about 5 minutes into a very simple example, I assumed it was none of those, although I tried several different modifications. Again, I’m a little out of touch with development, so I assumed I was just rusty.

I finally decided to put it to rest for the evening, and picked it up again tonight. This time, I got a little smarter and ran a trace in SQL Profiler to see what was actually being sent to the database. At first glance, nothing seemed out of the ordinary

exec sp_executesql N'insert [dbo].[ScheduleRequest]([ModifiedDate], [Name], [Comments])
values (@0, @1, @2)
select [Id]
from [dbo].[ScheduleRequest]
where @@ROWCOUNT > 0 and [Id] = scope_identity() and [ModifiedDate] = @0',N'@0 datetime2(7),@1 nvarchar(50),@2 nvarchar(50)',@0='2013-05-23 20:42:00.8549512',@1=N'Testom',@2=N'g'

Running the query from management studio returned 0 results, which seemed consistent with the exception. I went through each parameter, tweaked a few things, and then finally narrowed it down to the ModifiedDate column that was causing the query to return 0 results, even though the record was actually being written to the database. A closer look revealed that the ModifiedDate that I was passing in was more precise than what was being stored in the database. Urgh

A little further research indicated that the Entity Framework handles all dates as datetime2 in SQL Server. A quick change of the datatype on the ModifiedDate column from datetime to datetime2, and I was all set!

Another Lyla



Lyla at the park

I took this picture of Lyla, and made a few adjustments to the color, and also softened the skin just a tiny bit.

Ready to run

This picture was captured by Bethany, and shows Lyla ready to take off

Lyla at the park