NHibernate won't persist DateTime SqlDateTime overflow

Question

I am working on an ASP.NET MVC project with NHibernate as the backend and am having some trouble getting some dates to write back to my SQL Server database tables.

These date fields are NOT nullable, so the many answers here about how to setup nullable datetimes have not helped.

Basically when I try to save the entity which has a DateAdded and a LastUpdated fields, I am getting a SqlDateTime overflow exception. I have had a similar problem in the past where I was trying to write a datetime field into a smalldatetime column, updating the type on the column appeared to fix the problem. My gut feeling is that its going to be some problem with the table definition or some type of incompatible data types, and the overflow exception is a bit of a bum steer.

I have attached an example of the table definition and the query that NHibernate is trying to run, any help or suggestions would be greatly appreciated.

CREATE TABLE [dbo].[CustomPages](
    [ID] [uniqueidentifier] NOT NULL,
    [StoreID] [uniqueidentifier] NOT NULL,
    [DateAdded] [datetime] NOT NULL,
    [AddedByID] [uniqueidentifier] NOT NULL,
    [LastUpdated] [datetime] NOT NULL,
    [LastUpdatedByID] [uniqueidentifier] NOT NULL,
    [Title] [nvarchar](150) NOT NULL,
    [Term] [nvarchar](150) NOT NULL,
    [Content] [ntext] NULL
)

exec sp_executesql N'INSERT INTO CustomPages (Title, Term, Content, LastUpdated, DateAdded, StoreID, LastUpdatedById, AddedById, ID) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)',N'@p0 
nvarchar(21),@p1 nvarchar(21),@p2 nvarchar(33),@p3 datetime,@p4 datetime,@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier',@p0=N'Size and Colour 
Chart',@p1=N'size-and-colour-chart',@p2=N'This is the size and colour chart',@p3=''2009-03-14 14:29:37:000'',@p4=''2009-03-14 
14:29:37:000'',@p5='48315F9F-0E00-4654-A2C0-62FB466E529D',@p6='1480221A-605A-4D72-B0E5-E1FE72C5D43C',@p7='1480221A-605A-4D72-B0E5-E1FE72C5D43C',@p8='1E421F9E-9A00-49CF-9180-DCD22FCE7F55'

In response the the answers/comments, I am using Fluent NHibernate and the generated mapping is below

  public CustomPageMap() {

            WithTable("CustomPages");

            Id( x => x.ID, "ID" )
                .WithUnsavedValue(Guid.Empty)
            .   GeneratedBy.Guid();

            References(x => x.Store, "StoreID");

            Map(x => x.DateAdded, "DateAdded");
            References(x => x.AddedBy, "AddedById");
            Map(x => x.LastUpdated, "LastUpdated");
            References(x => x.LastUpdatedBy, "LastUpdatedById");


            Map(x => x.Title, "Title");
            Map(x => x.Term, "Term");
            Map(x => x.Content, "Content");

        }  

    <?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false" assembly="MyNamespace.Core" namespace="MyNamespace.Core">
<class name="CustomPage" table="CustomPages" xmlns="urn:nhibernate-mapping-2.2">
<id name="ID" column="ID" type="Guid" unsaved-value="00000000-0000-0000-0000-000000000000"><generator class="guid" /></id>
<property name="Title" column="Title" length="100" type="String"><column name="Title" /></property>
<property name="Term" column="Term" length="100" type="String"><column name="Term" /></property>
<property name="Content" column="Content" length="100" type="String"><column name="Content" /></property>
<property name="LastUpdated" column="LastUpdated" type="DateTime"><column name="LastUpdated" /></property>
<property name="DateAdded" column="DateAdded" type="DateTime"><column name="DateAdded" /></property>
<many-to-one name="Store" column="StoreID" /><many-to-one name="LastUpdatedBy" column="LastUpdatedById" />
<many-to-one name="AddedBy" column="AddedById" /></class></hibernate-mapping>

Your table and the SQL seem ok. Does that SQL query work if you try running it by hand? It might be worth posting the code and the hibernate mapping file here — Andy White, Mar 14 '09 at 07:27
Yes the query does run okay in SQL Query Analyzer, except I have to change the double single quotes around the datetimes (as utc style strings) to single quotes, this query is generated by nhibernate — chris raethke, Mar 14 '09 at 15:02

score 17 · Answer 1 · edited Feb 17 '17 at 14:50

Actually the reason behind the scene is:

When NHibernate reads the row from the db, the value is null, and that is what the session remembers. When the object is rehydrated by NHibernate the Date is set to the value DateTime.MinValue. When the Session is synchronized with the db, NHibernate assumes that something has changed, because the currentState and the previousState are different and tries to update the row. Which in turn fails, because DateTime.MinValue will not fit into a SqlServer datetime column.

The solution: make your datetime nullable either by putting ? at end of Datetime like DateTime? or Nullable

complet article can be found at: nhibernate-sqldatetime-overflow-issue

score 1 · Answer 2 · answered Sep 21 '11 at 13:28

I faced the same issue table with non nullable datetime columns and a SqlDateTime overflow on a save.

In my case the actual problem was that the save triggered a session.flush which causes an update to some object loaded earlier using the same session. This object did have the 'datetime fields where nullable datetime field should have been used' issue.

In other words: Try to do a session.flush prior to the save or update to check whether the problem is maybe caused by earlier usage of the same session object.

Hope that helps anyone.

score 0 · Answer 3 · answered Apr 23 '10 at 19:21

Just an FYI, I have been working on migrating an existing application to NHibernate from a variety of embedded Data Access approaches. This issue came up quickly, and I found an easy solution that minimized impact to the existing functional application code. Because we are migrating by layer in some cases, the data class interface needed to remain stable if at all possible. The solution here was to continue to use DateTime types for the public interface, add extension methods to the private field types to convert from MinValue to null and back on the private fields in the accessors, and map nHibernate to the private fields.

JF

gbn · Answer 4 · 2009-03-14T09:56:47.663

0

Why do @p3 and @p4 have 2x single quotes? Copy and paste error?

I can't check (don't have SQL installed here), but should the millisecond separator not be "dot", that is, "2009-03-14 14:29:37.000"

@p3 is "before", @p4 is "after" here:

exec sp_executesql
  N'INSERT INTO CustomPages (Title, Term, Content, LastUpdated, DateAdded, StoreID, LastUpdatedById, AddedById, ID)
  VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)',
  N'@p0 nvarchar(21),@p1 nvarchar(21),@p2 nvarchar(33),@p3 datetime,@p4 datetime,@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier',

    @p0=N'Size and Colour Chart',
    @p1=N'size-and-colour-chart',
    @p2=N'This is the size and colour chart',
    @p3=''2009-03-14 14:29:37:000'', --quotes + dot wrong?
    @p4='2009-03-14 14:29:37.000', --quotes + dot correct?
    @p5='48315F9F-0E00-4654-A2C0-62FB466E529D',
    @p6='1480221A-605A-4D72-B0E5-E1FE72C5D43C',
    @p7='1480221A-605A-4D72-B0E5-E1FE72C5D43C',
    @p8='1E421F9E-9A00-49CF-9180-DCD22FCE7F55'

edited Mar 14 '09 at 09:56

answered Mar 14 '09 at 09:43

gbn

422,506
82
585
676

Yes, I thought the way it rendered the dates was a bit weird also, however it does work in query analyzer except I have to swap the double quotes for single quotes. I looked into chapter 5 of the nhibernate doco thinking I could tell nhibernate how to correct the dialect, but it doest look possible – chris raethke Mar 14 '09 at 15:04
The 2009-03-14 14:29:37:000 format is wrong though for SQL Server – gbn Mar 15 '09 at 16:56
The dialect might be your problem. Why do you say it doesn't look possible to set that? How are you creating your SessionFactory? Are you reading it from a config file, or creating it programmatically? – Andy White Mar 15 '09 at 20:21

score 0 · Answer 5 · answered Mar 16 '09 at 21:18

0

The SQL script in your question executed just fine on my SQL 2005 installation (once I fixed the quote issues picked up by gbn). And your mapping also looks fine. Sorry to be of so little help.

answered Mar 16 '09 at 21:18

John Rayner

3,485
21
13

score 0 · Answer 6 · answered Apr 19 '09 at 12:01

0

I was using s#architecture/fluentnhibernate to create this mapping, I have upgraded to the latest version and it seems to be working fine.

answered Apr 19 '09 at 12:01

chris raethke

425
3
8

NHibernate won't persist DateTime SqlDateTime overflow

6 Answers6

Linked