Dapper mapping columns to entity properties?

Question

Dapper mapping columns to entity properties?

c# dapper sql-server

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.

Ivan-Mark Debono

View more on Stack Overflow

Accepted Answer

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;
}

Dapper mapping columns to entity properties?

Question

Accepted Answer

Performance

Expression Evaluator