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.

You can use the [DapperExtensions](https://github.com/tmsmith/Dapper-Extensions) package and create a custom mapper for your class by inheriting from ClassMapper or AutoMapper. See [this](http://stackoverflow.com/questions/12183310/can-i-specify-db-column-names-for-dapper-dot-net-mappings) for more info. Don't have time to give a full answer. I believe you will also need to change from using the Query method to using the Get method. — Shane, Oct 30 '14 at 09:17

Krishnraj Rana · Accepted Answer · 2014-10-30T09:35:38.943

8

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

edited Oct 30 '14 at 09:35

answered Oct 30 '14 at 09:28

Krishnraj Rana

6,516
2
29
36

This is a good answer because for one-off queries and mapping, this solution will suffice. However, for many queries that need to be mapping, DapperExtensions is the way to go. – Ivan-Mark Debono Oct 30 '14 at 10:17
Yes for many queries or some complex objects you need to use DapperExtensions. Anyways, Glad to help you and thanks if you can upvote my answer.. :) – Krishnraj Rana Oct 30 '14 at 11:52

Dapper mapping columns to entity properties?

1 Answers1