How to use Dapper to automatically map remaining columns to dictionary?

Question

I am attempting to use Dapper to map to my DTO:

public class MyDto
{
    public int Id { get; set; }
    public string Code { get; set; }
    public IDictionary<string, object> AdditionalColumns { get; set; }
}

I can have an instance of MyDto populate the first two properties based on convention, but I'm not sure how to tell Dapper to map all remaining columns into the dictionary.

connection.Query<MyDto>("Get_Something", commandType: CommandType.StoredProcedure, param: parameters);

The stored procedure returns the following:

Id: INT
Code: VARCHAR(32)
Foo: VARCHAR(16)
Bar: DATETIME

Multimap with `splitOn: "Foo"` then map it yourself in the lambda? *EDIT* not trying to say you should have known this — Charlieface, Jan 23 '21 at 19:19
@Charlieface: That's a big vague, even for me, and I'm experienced with Dapper in both it's dynamic and static modes. — Robert Harvey, Jan 23 '21 at 19:19
@RobertHarvey I know, it was just a comment for someone with the time to write a full answer. See https://stackoverflow.com/questions/7472088/correct-use-of-multimapping-in-dapper for further info — Charlieface, Jan 23 '21 at 19:20
The problem is that any solution is going to have to take into account both the static and dynamic aspects. Id and string will probably have to be mapped manually, instead of relying on Dapper's reflection. — Robert Harvey, Jan 23 '21 at 19:23
Is [your query of 10 months ago](https://stackoverflow.com/questions/60839565/map-leftover-column-names-to-populate-a-dictionary-in-dapper-net) a duplicate? — Caius Jard, Jan 23 '21 at 19:38
@CaiusJard: Ah, I forgot that I asked this before. Maybe I should just bounty that and delete this question. — michael, Jan 23 '21 at 20:29

Robert Harvey · Answer 1 · 2021-01-23T21:18:38.990

0

Return your query as dynamic objects.

var query = connection.Query<dynamic>(
    "Get_Something", commandType: CommandType.StoredProcedure, param: parameters);

Then, map your DTO's like this:

var results = new List<MyDto>();

foreach(item in query)
{
    var dictionary = new RouteValueDictionary(item).ToDictionary(x => x.Key, y => y.Value);
    var dto = new MyDto
    {
        Id = dictionary["Id"];
        Code = dictionary["Code"];
    }
    dictionary.Remove("Id");
    dictionary.Remove("Code");
    dto.AdditionalColumns = dictionary;
    results.Add(dto)
}

Reference
RouteValueDictionary Class

edited Jan 23 '21 at 21:18

answered Jan 23 '21 at 21:06

Robert Harvey

178,213
47
333
501

2

I wonder, would it make anything simpler to just leave the ID and Code in the dictionary and have the props of the class set/get them? – Caius Jard Jan 24 '21 at 07:48

How to use Dapper to automatically map remaining columns to dictionary?

1 Answers1