Dapper mapping columns to entity properties?

Question

I'm using Dapper's Query<> to search for a number of records:

public class Product
{
    public int Id {get; set}
    public string Name {get; set}
    public int CategoryId {get; set}
]

public IEnumerable<Product> GetProducts(int categoryId)
{
    var connection = DataContext.Database.Connection;

    var sql = "SELECT * FROM products WHERE category_id = @categoryId";

    var result = connection.Query<Product>(sql, new { categoryId });

    return result;
}

The query itself returns the requested records but each object in the list has empty fields.

So how can I map the columns to the entity's properties?

I would not like to add column aliases in the sql statement. Decorating the entity's properties is also not an option because the entities are generated by the EF designer.

Answer 1

Well your Product class must be defined such as to match the result coming back from the query. So what you can do is -

public IEnumerable<Product> GetProducts(int categoryId)
{
    var connection = DataContext.Database.Connection;

    var sql = "SELECT * FROM products WHERE category_id = @categoryId";

    var result = connection.Query<Product>(sql, new { categoryId }).Select(p => new Product {
           Id = (int)p.ProductId,
           Name = (string)p.ProductName,
           CategoryId  = (int)p.ProductCategoryId
        });

    return result;
}