3

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

Joe Higley
  • 1,762
  • 3
  • 20
  • 33
  • A varchar field cannot be set with the Identity property in SQL Server (I assume you're using SQL Server). Only integer/decimal types. So it appears your assumption is correct. I'm not too familiar with dapper, but can you pass a string instead? You seem to be confusing `PRIMARY KEY` with `IDENTITY` also. – Jacob H Jan 24 '18 at 16:40
  • Can you provide the code for your User model in C# and schema for the User table in SQL Server? – ColdSolstice Jan 24 '18 at 16:43

2 Answers2

3

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?

ahelenius
  • 70
  • 8
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.

Joe Higley
  • 1,762
  • 3
  • 20
  • 33
  • FYI you are referencing Dapper and DapperExtensions as if they are one and the same; they are not. DapperExtensions does utilize Dapper but it is not part of the Dapper project and it has its own logic and configuration options. Make sure you understand where the scope of Dapper begins and ends, and where DapperExtensions takes over – ColdSolstice Jan 24 '18 at 17:21