Dapper Not Populating Fields and Throwing Exceptions

1
votes

I'm switching from ActiveRecord to Dapper. I have a simple Project class with a couple of fields:

  • Id (int)
  • Name (string)
  • Secret (guid)
  • OwnerId (guid)

My initial attempt to use Dapper resulted in this code: I'm using Dapper like so:

List<Project> projects = new List<Project>();
using (var conn = MvcApplication.GetSqlConnection()) // new connection
{
    projects = conn.Query<Project>("SELECT * FROM Projects p WHERE p.OwnerId = (SELECT UserId FROM aspnet_Users WHERE LoweredUserName=@userName)", new { userName = User.Identity.Name.ToLower() }).ToList();
}
return View(projects);

This query resulted in a DataException: Error parsing column 0 (Id=11 - Int16). The inner exception is an InvalidCastException: Specified cast is not valid.

Since none of the samples on the Dapper usage page mention SELECT *, I tried to reform my query like so:

    projects = conn.Query<Project>("SELECT OwnerId = (SELECT UserId FROM aspnet_Users WHERE LoweredUserName=@userName)", new { userName = User.Identity.Name.ToLower() }).ToList();

When I debug, although I have two projects in my DB, I only get one back; and that one has null for a name.

Strange. What am I doing wrong? I feel like Dapper is not mapping the fields (DB) to the fields (class) properly. I'm using the latest NuGet version.

2
asp.net-mvc-3dapper
For info, 1.12 has now been deployed to nuget - Marc Gravell
Thanks @MarcGravell. I'll stick to source for now, since I can more easily figure out what exactly the problem is when exceptions appear. - ashes999

2 Answers

1
votes

The difference you are seeing is the key feature in the next (1.12?) release. Historically, dapper has been very fussy that data must be a total match, and it would complain (throw) if, say, the data is double but the field/property is float, or the data is long and the field/property is int. In the pending version there is extra code to detect this and compensate as part of the generated IL (not via Convert.ChangeType - too slow).

One key driver for this change is that some databases return some fairly simple values in very different ways. It was often problematic for users of some databases to map their types.

Tl;dr: I'm glad the new feature works for you. It should be released soon.

0
votes

Dapper will only map up the result of the SQL, so you will need select from the table that you want it to map.

List<Project> projects = new List<Project>();
using (var conn = MvcApplication.GetSqlConnection()) // new connection
{
    projects = conn.Query<Project>("SELECT * FROM Projects Where OwnerId = (SELECT UserId FROM aspnet_Users WHERE LoweredUserName=@userName)", new { userName = User.Identity.Name.ToLower() }).ToList();
}
return View(projects);

Si