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.

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



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why