Using Dapper-dot-net, how do I map SQL Time column to a .Net type?

.net dapper tsql

Question

I have an existing database that uses the SQL time(7) type, which does not map directly to a .Net type.

Question: Using Dapper, how do I map a SQL time column to a .Net type?

Issue: When I attempt to implicitly map a SQL time(7) column to DateTime using Dapper-dot-net, I get the following exception:

An exception of type 'System.Data.DataException' occurred in Dapper.dll but was not handled in user code

Additional information: Error parsing column 5 (CheckInTime=08:54:43.1470000 - Object)

I assumed it would map to DateTime automatically, but it does not appear to do so

Popular Answer

I've had some success mapping SQL time(7) columns to System.TimeSpan.

As Dan pointed out, a TimeSpan is "semantically" less appropriate if your intent is to represent a time of the day.

But I think that, technically, there is no loss of information going from time(7) to TimeSpan, so the data is there, and it's a matter of meaning.

One way I've been using to alleviate the ugliness is to use a private TimeSpan property just for the sake of querying and expose the data in a custom type public property.

I prefer that over using a DateTime in combination with the call to CONVERT() in the SQL, because even with the DateTime you still have a somehow "imperfect" representation of you data (the date part is bogus) and you introduce ugliness in the SQL as well.

But, at this point, I think it can be considered a matter of personal preference.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why