Dapper: is it possible to customize the type mapping of a specific field of a specific type?

Question

Let's say I have this User class:

public class User
{
    public int      ID          { get; set; }
    public string   FirstName   { get; set; }
    public string   LastName    { get; set; }
    public string   Email       { get; set; }
    public DateTime DateCreated { get; set; }
    public DateTime LastLogin   { get; set; }
}

Which I want to map to the following table:

CREATE TABLE "user" (
  "ID"          int(11) NOT NULL AUTO_INCREMENT,
  "FirstName"   varchar(45) DEFAULT NULL,
  "LastName"    varchar(45) DEFAULT NULL,
  "Email"       varchar(255) NOT NULL,
  "DateCreated" int(11) NOT NULL,
  "LastLogin"   int(11) NOT NULL,
  PRIMARY KEY ("ID")
)

Or put in simpler words: I want to store the DateTime properties as int fields in the database. However, this is the case for this class/table. Other class/tables might be mapped differently. I was thinking of something along the lines of a custom conversion function in combination with type or member map.

Is it possible to achieve this with Dapper, and if so how?

Zhao

Answer 1

The bottom line is that Dapper does not support this by design. One of its core design principles is a 1:1 mapping between the table and the object, with the exception of column names to property names mapping.

The solution I went with in the end was to combine Dapper with AutoMapper which we are already making heavy use of anyway. In our Dapper DAOs, in complex cases we use an entity object separate to the domain object and map between them. So essentially the non-trivial mapping between domain and table becomes a simple question of object-to-object mapping.

Zhao

Answer 2

This should now be possible with custom SqlMapper.TypeHandler. See this example in unit tests. The downside is that it applies custom type handling to all fields of the same type. In your case all DateTime properties will be evaluated through type handler.

Bloemfontein

This should be a new accepted answer, thanks :) – Regional

Answer 3

Probably you can use the new SqlMapper.SetTypeMap method to achieve this. See https://mcmap.net/q/117210/-manually-map-column-names-with-class-properties for more information.

Kwabena

Unless I'm mistaken, SetTypeMap can be used to adjust how the columns map to the properties but not how the type conversion is performed. – Dessert

Answer 4

Dapper does not, to the best of my knowledge, currently support such conversions. You could use stored procs in Dapper, and have the proc do the conversion for you. You could also ensure that the data/conversion is done before passing the object to Dapper.

I heavily question why you'd opt for int dates vs DateTime however in your data source... Seems unnecessary and overly complex.

"Always write your code as if the next person to maintain it is an axe-wielding psychopath who knows your home address."

Brodie

That beats the point - I want this to be the "normal" mapping of this type in which ever query it is used in. I want to use Dapper because I want to rely on native SQL in Data Access Objects controlled at the application level, not stored procedures. I always map datetime to ints unless I have a good reason to do otherwise, because mapping datetime objects to sql datetime types in MySQL in notoriously prone to timezone mismatch errors. The person maintaining my code is a psycopath who knows where I live. He does not own an Axe though. – Zhao

@AmirAbiri no offense intended, but TimeZone mismatch errors are the programmer's fault, not the ORMs. Store everything as UTC and calculate local time for displays, and you can avoid the issue in any data source. Store your DateTimes as DateTimes if your only reason NOT to is concerns over TimeZones. – Brodie

None taken - programmer errors are what I'm trying to avoid. As an architect one of the considerations of any design is whether it is more or less error prone by the developer. I'm not blaming the ORM, I'm just trying to use it a certain way. Storing everything in UTC and having precise control over that is exactly the reason I want a custom conversion function. Anyway, the point wasn't to have a philosophical debate - we can agree to disagree. The question is if this is possible with Dapper? – Zhao

Well, perhaps compromise on your objects... Change your DateTime autoprops to manual properties... When setting the DateTime properties, convert the value to UTC. This guarantees a save as UTC and read as UTC, no matter how the programmer interacts. Then you can avoid the int conversion AND gain uniform data representation. – Brodie

So I take it Dapper doesn't support a custom conversion function or something similar? – Zhao

Not that I'm aware of - for sprocs and parameterized queries it just maps the object to a class called DynamicParameters under the hood... Deriving those values is up to you. :) – Brodie

let us continue this discussion in chat – Zhao

In this case, I'm the axe-wielding psychopath, and, while I've already cleaned up the remains of the programmer himself, I still have to deal with the remains of a shoddy database that used ints, strings, and other abominations to store dates. Sometimes you just have to deal with garbage. – Nita