Problems Fluent Nhibernate Mapping MySQL Time(6) to C# DateTime

Question

i have a MySql table with the following schema

Field   Type            Null    Key Default Extra
id          int(11)         NO  PRI NULL    auto_increment
Date    date            YES MUL NULL    
Time    time(6)         NO  MUL NULL    
Exch    varchar(45) YES MUL NULL    
ProdType    varchar(45) YES     NULL    
Product varchar(45) YES     NULL    
Contract    varchar(45) YES     NULL    
Direction   varchar(45) YES     NULL    
Price   decimal(10,4)   YES     NULL    
Quantity    int(11)         YES     NULL

Fluent Model:

public class Trade
{
    public virtual int Id { get; set; }
    public virtual DateTime Date { get; set; }
    public virtual DateTime Time { get; set; }
    public virtual string Contract { get; set; }
    public virtual string Direction { get; set; }
    public virtual double Price { get; set; }
    public virtual int Quantity { get; set; }
}

and mapping:

public TradeMap()
    {
        Id(x => x.Id).Column("id");
        Map(x => x.Date).Column("Date");
        Map(x => x.Time).Column("Time").CustomType("timestamp");;
        Map(x => x.Contract).Column("Contract");
        Map(x => x.Direction).Column("Direction");
        Map(x => x.Price).Column("Price");
        Map(x => x.Quantity).Column("Quantity");
        Table("ts");
    }

I'm testing the ORM with the following code

        DateTime dayStart = Convert.ToDateTime("11:31:00.000000");
        DateTime dayEnd = Convert.ToDateTime("11:32:00.000000");

        IQueryable<Trade> result = from ts in repo.GetList<Trade>()
                     where ts.Date == new DateTime(2013,7,1)
                        && ts.Time >= dayStart
                        && ts.Time <= dayEnd
                        && ts.Contract == "Sep13"
                    select ts;


        foreach (var l in result)
        {
            DateTime k = l.Time;
        }

and

Trade result = repo.GetList<Trade>().FirstOrDefault();

But i keep getting inner exception

{"Unable to cast object of type 'System.TimeSpan' to type 'System.IConvertible'."}

i tried resolving this by changing the time mapping to

Map(x => x.Time).Column("tsTime").CustomType("timestamp").CustomSqlType("TIME(6)").Nullable();

Map(x => x.Time).Column("tsTime").CustomSqlType("TIME(6)");

but nothing works

Ah for mysql aswell. We have the same kind of problem in oracle. NHibernate isnt working correctly with datetime etc. I hope they'll fix it in the future. — Alfons, Dec 16 '14 at 10:36
When we had time critical functions in nhibernate, we used to to ticks for the dates and and then time values as seconds in a day, with the timespan class it makes things a little easier http://stackoverflow.com/questions/463642/what-is-the-best-way-to-convert-seconds-into-hourminutessecondsmilliseconds — Dai Bok, Mar 11 '15 at 16:19

score 0 · Answer 1 · answered Apr 18 '15 at 18:17

0

Use a single field for DATE + TIME.

answered Apr 18 '15 at 18:17

Rick James

135,179
13
127
222

Problems Fluent Nhibernate Mapping MySQL Time(6) to C# DateTime

1 Answers1