Version as timestamp in Fluent NHibernate / SQL Server

Question

Using FNH w/ SQL Server 2008, I'm trying to add a Version as a timestamp, but running into the SQLDateTime Overflow error because the value is passed as 1/1/0001 12:00:00 AM. I found this (also referenced here), but still experiencing the problem.

// entity base
public abstract class EntityBase
{
    public virtual Int64 Id { get; set; }
    public virtual DateTime Version { get; set; }
}

// entity base map
public abstract class EntityBaseMap<T> : ClassMap<T> where T : EntityBase
{
    public EntityBaseMap()
    {
        Id(x => x.Id).GeneratedBy.Identity();
        OptimisticLock.Version();
        Version(x => x.Version)
           .CustomType("Timestamp");

    }
}

The SQL Server data type is "datetime".

I'm guessing its something small and stupid, but haven't found the cause yet - what am I missing?

EDIT: Action method for the actual "save" code

    public ActionResult Create()
    {
        int currMaxSortOrder = session.CreateCriteria(typeof(Section))
                            .SetProjection(Projections.ProjectionList().Add(Projections.Max("Sortorder")))
                            .UniqueResult<int>();
        SectionViewModel sectionViewModel = new SectionViewModel();
        sectionViewModel.Sortorder = currMaxSortOrder + 1;
        return View("Create", "_AdminLayout", sectionViewModel);
    }

    [HttpPost]
    public ActionResult Create(SectionViewModel sectionInputModel)
    {
        if (ModelState.IsValid)
        {
            section = new Section();
            Mapper.Map(sectionInputModel, section);
            using (var tx = session.BeginTransaction())
            {
                session.SaveOrUpdate(section);
        tx.Commit();
            }
            return RedirectToAction("index", "pages").WithFlash(new { success = "Section '" + section.Name + "' was successfully added." });
        }
        return View("Create", "_AdminLayout", section);
    }

Edit 2: Added section entity & mapping

    public class Section : EntityBase
    {
        public virtual String Name { get; set; }
        public virtual int Sortorder { get; set; }
        public virtual String RedirectUrl { get; set; }
        public virtual IList<Page> Pages { get; set; }

        public Section()
        {
            Pages = new List<Page>();
        }

        public virtual void AddPage(Page page)
        {
            page.Section = this;
            this.Pages.Add(page);
        }
    }

    public class SectionMap : EntityBaseMap<Section>
    {
        public SectionMap()
        {
            Map(x => x.Name);
            Map(x => x.Sortorder);
            Map(x => x.RedirectUrl);
            // one to many relationship
            HasMany(x => x.Pages)
                .Inverse()
                .Cascade.All();
        }
    }
}

Please, could you add a code fragment that is causing the error? — Jakub Linhart, Apr 30 '11 at 07:53
Hmm, similar case works well for me. Do you use any listeners or interceptors? How looks Section entity and its mapping? Is the Timestamp db. column not-null? — Jakub Linhart, May 01 '11 at 19:18
@Jakob, no listeners or interceptors, but using Castle Windsor as shown [here](http://docs.castleproject.org/Windsor.Windsor-tutorial-ASP-NET-MVC-3-application-To-be-Seen.ashx). Section entity & mapping added above. Tried with DB data type datetime as both 'null' and 'not null'. — seekay, May 02 '11 at 15:52

score 2 · Accepted Answer · answered May 04 '11 at 18:04

sheepish Doh! moment

(Adding this in case any other n00bs like me run into the same problem)

I finally dug deeper and realized that I had configured it to use AutoMapping while I was creating maps that would only work with FluentMapping. Reverted to use FluentMapping and the Version started working perfectly!

I'm guessing I could possibly use AutoMapping and add a convention that will treat a column named "Version" with CustomType("Timestamp"), but for now am going to use FluentMapping until I get more up to speed.

score 0 · Answer 2 · answered Apr 30 '11 at 00:02

This might be the classic .NET min datetime != SQL Server min datetime.

The min datetime in .NET is in the year 0001, but in SQL server the min date can only go as low as the year 1753. You're getting an overflow in SQL Server because the SQL datetime type can't store the date you're trying to pass.

You might have better luck with the datetime2 type, but I'm not sure of the compatibility with Hibernate.

See this article for more info: http://blog.malevy.net/2010/01/datetimeminvalue-sql-server-minimum.html

Yep - tried that too i.e. changed SQL datatype to datetime2(7) and the map to use CustomType("datetime2") - no difference — seekay, Apr 30 '11 at 00:06

Version as timestamp in Fluent NHibernate / SQL Server

2 Answers2

Linked