How to map objects when using Dapper SplitOn?

Question

I have a stored proc that returns multiple rows which the following columns:

Guid,
Guid,
Name,
Guid,
Description

The first Guid column is always the same for all the rows. So I created the following classes:

public class Header
{
    public Guid Guid { get; set; }
    public string Name { get; set; }
}

public class Content
{
    public Guid Guid { get; set; }
    public string Description { get; set; }
}

public class Result 
{
    public Guid Guid { get; set; }
    public IEnumerable<Header> Headers { get; set; }
    public IEnumerable<Content> Content { get; set; }
}

And to get the reults I'm trying to do:

var result connection.Query<Guid, Header, Content>("myproc", 
    new { criteria }, 
    commandType: CommandType.StoredProcedure,
    map: ,
    splitOn: "Guid").AsList();

But what should I do in the map parameter to split the results into the objects?

StuartLC · Answer 1 · 2019-04-07T14:46:35.583

I don't think you can call 3 of the 5 columns returned with the same name (Guid) and expect Dapper to figure out how to split correctly.

Assuming that the column names returned from the SPROC are unique (I've named Guid1, Guid2 and Guid3), we split on Guid2 (header) and Guid2 (content).

Since the Query will return one row per row returned from the Proc, we need to consolidate and group by the Parent Guid.

I've used the Dictionary pattern here to do this rollup:

var sql = "EXEC p_myProc";
var resultDictionary = new Dictionary<Guid, Result>();
var results = connection.Query<Result, Header, Content, Result>(
        sql,
        (result, header, content) =>
        {
            if (!resultDictionary.TryGetValue(result.Guid1, out var existingResult))
            {
                result.Headers = new List<Header>();
                result.Content = new List<Content>();
                resultDictionary.Add(result.Guid1, result);
                existingResult = result;
            }
            // Noting OP has defined the Child tables as immutable IEnumerable<>
            (existingResult.Headers as List<Header>).Add(header);
            (existingResult.Content as List<Content>).Add(content);
            return existingResult;
        },
        splitOn: "Guid2,Guid3")
    .Distinct() // Strip duplicates by reference equality
    .ToList();

Note that the results returned by Query will have as many rows as the proc returns, but because we'll return the same reference Result for each Guid1 key, Distinct() will strip out the duplicates.

An alternative to this approach would be to map the flattened result of the Stored Proc into a 1:1 temporary DTO POCO with all 5 columns, and then use LINQ in memory to GroupBy the Guid1 in order to project out the Header and Content children.

How to map objects when using Dapper SplitOn?

1 Answers1