David Aleu David Aleu - 1 month ago 14
C# Question

Mapping entity in Dapper

I've just started working with Dapper and I don't seem to find something very simple like mapping an entity to a table in my database:

I have a stored procedure:

CREATE PROCEDURE [dbo].GetUserById (@UserId int)
AS
begin
SELECT UserId,LastName,FirstName,EmailAddress
FROM users
WHERE UserID = @UserId

end
go


Then an entity:

public class User
{
public int Id { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
public string Email { get; set; }
}


And a dapper query in my code:

int userid=1;
User User = connection.Query<User>("#GetUserById", new {userid=userid}, commandType: CommandType.StoredProcedure).FirstOrDefault();


My question is: How can I tell my entity User that Id is Userid on my database?

In EF I would do something like this:

MapSingleType(c => new
{
UserId = c.Id,
Firstname = c.Firstname,
Lastname = c.Lastname,
EmailAddress = c.Email
}).ToTable("users");


How can the above be achieved in dapper?

Answer

Dapper deliberately doesn't have a mapping layer; it is the absolute minimum that can work, and frankly covers the majority of real scenarios in the process. However, if I understand correctly that you don't want to alias in the TSQL, and don't want any pass-thru properties - then use the non-generic Query API:

User user = connection.Query("...", ...).Select(obj => new User {
           Id = (int) obj.UserId,
           FirstName = (string) obj.FirstName,
           LastName = (string) obj.LastName,
           Email = (string) obj.EmailAddress
        }).FirstOrDefault();

or perhaps more simply in the case of a single record:

var obj = connection.Query("...", ...).FirstOrDefault();
User user = new User {
      Id = (int) obj.UserId,
      FirstName = (string) obj.FirstName,
      LastName = (string) obj.LastName,
      Email = (string) obj.EmailAddress
};

The trick here is that the non-generic Query(...) API uses dynamic, offering up members per column name.