Dapper returns SQL conversion error on ID column

Question

In my database I have an ID column that is a nvarchar type because it contains unique user names. In my .Net MVC application I am using Dapper to handle the database exchange. I then use the DapperExtensions.Get(dynamic id) on my models to grab a specific entry by the ID field. This works great in all cases except in users where I get the following error:

Conversion failed when converting the nvarchar value 'abcdefg' to data type int.'

I know that this is the ID column because that value exists in the ID property of my User model when it is used to extract the entity using Dapper. My best guess is that maybe Dapper expects that the dynamic id argument would be an int and tries to use it as such. I'm hoping to get some clarification on this by someone who understands it better than myself.

I have made sure that my database table uses nvarchar for this field and it is marked as the Primary Key.

Thanks

Answer 1

Make sure to use Dapper.Contrib.Extensions.Key attribute to flag the correct property as the Key property. Do not use System.ComponentModel.DataAnnotations.Key as it may act differently.

However, sometimes, this is known to not work. When no Key attribute is present, try putting the field to the top of your class and DapperExtensions may treat this as your Primary Key.

Or try using ExplicitKey?

Answer 2

I figured out the answer right after posting the question by running a sql profiler on the application. The solution is quite a stupid one.

Using the profiler, I could see that the sql command created by Dapper was attempting to match the nvarchar against a different column that was indeed an integer column. I then looked at my User model and noticed that this column shows up as the first property declared in this model.

The solution was to move the ID property to be the first declared property and then everything worked fine. Apparently Dapper just uses the first declared property within a model as the ID column despite which property might have the [Key] attribute tag on it.