I have a simple query and Poco that I'm using with Dapper like so:
var jc = this.dbConnection.ExecuteScalar<JcUser>("SELECT loginid as Username,Password,coalesce(CustomerId,0) as CustomerId,TextProfileId,UxProfileId from \"user\" where id = @id", new {id = id});
Poco:
public class JcUser
{
public string UserName { get; set; }
public string Password { get; set; }
public int CustomerId{ get; set; }
public int TextProfileId { get; set; }
public int UxProfileId { get; set; }
}
When this executes it throws an exception with the message
Value is not a convertible object: System.String to JcUser
The stack trace ends up at: at System.Convert.ToType (System.Object value, System.Type conversionType, IFormatProvider provider, Boolean try_target_to_type)
Any ideas why its doing this?
Thanks
UPDATE: Using var jc = this.dbConnection.Query<JcUser>("SELECT loginid as Username,Password,coalesce(CustomerId,0) as CustomerId,TextProfileId,UxProfileId from \"user\" where id = @id", new {id = id}).First();
appears to work. I also realise I'm a moron and ExecuteScalar is only for one value. However, is my update the best way to retrieve only one row?
ExecuteScalar
maps to the ADO.NET method of the same name. It returns at most one cell: one grid, one row, one column. As such, it is not intended for use with complex objects, and cannot work correctly in your case as you have multiple columns.
Dapper assumes you would only use that with simple types like int
, string
etc.
In your case, use:
var jc = this.dbConnection.Query<JcUser>(
sql, args).SingleOrDefault();
If you want to avoid a hidden List<>
allocation you could also pass buffered: false
.