Dapper: Struggling to understand how to combine Multi Mapping with QueryMultiple

[dumbledad]

I am struggling with gluing together the results of a QueryMultiple call where several of the queries contain a multi mapping. I cannot find much on this in the documentation nor test suite. My Query looks like this:

    var sql = @"SELECT * FROM Users WHERE id = @id
        SELECT * FROM Items LEFT OUTER JOIN ItemLayouts ON Items.id = ItemLayouts.itemId WHERE userId = @id
        SELECT * FROM Collections LEFT OUTER JOIN CollectedItems ON Collections.id = CollectedItems.collectionId  WHERE userId = @id
        SELECT * FROM Tags LEFT OUTER JOIN TaggedItems ON Tags.id = TaggedItems.tagId  WHERE userId = @id";

The example I have found is an answer on StackOverflow (http://stackoverflow.com/a/12978912/575530) where the answerer adds a property to the data class to temporarily store the individual items that he'll build an array from. Is this the right way to combine multiple hits from a LEFT OUTER JOIN into an array on the object?

[mgravell]

Very hard to comment without knowledge of what the object model looks like.
What model are you trying to populate?

On 5 February 2016 at 12:23, Tim Regan [email protected] wrote:

I am struggling with gluing together the results of a QueryMultiple call
where several of the queries contain a multi mapping. I cannot find much on
this in the documentation nor test suite. My Query looks like this:

var sql = @"SELECT * FROM Users WHERE id = @id
    SELECT * FROM Items LEFT OUTER JOIN ItemLayouts ON Items.id = ItemLayouts.itemId WHERE userId = @id
    SELECT * FROM Collections LEFT OUTER JOIN CollectedItems ON Collections.id = CollectedItems.collectionId  WHERE userId = @id
    SELECT * FROM Tags LEFT OUTER JOIN TaggedItems ON Tags.id = TaggedItems.tagId  WHERE userId = @id";

The example I have found is an answer on StackOverflow (
http://stackoverflow.com/a/12978912/575530) where the answerer adds a
property to the data class to temporarily store the individual items that
he'll build an array from. Is this the right way to combine multiple hits
from a LEFT OUTER JOIN into an array on the object?

—
Reply to this email directly or view it on GitHub
https://github.com/StackExchange/dapper-dot-net/issues/452.

Regards,

Marc

[akaSybe]

@dumbledad if I understood you correctly:

C# using (IDbConnection connection = DbConnFactory()) { using (SqlMapper.GridReader reader = await connection.QueryMultipleAsync(<your_sql>, <your_params>, commandType: <your_command_type>)) { // first result set var users = reader.Read<User>(); // second result set IEnumerable<SomeOutputClass> itemsWithLayouts = reader.Read<Item, ItemLayout, SomeOutputClass>((item, layout) => { // construct instance of SomeOutputClass return new SomeOutputClass(); }, splitOn: <name_of_first_column_in_ItemLayouts_table>); // if you have another result sets do something else } }

[dumbledad]

Thanks all. I've pared-down my solution and posted it as an answer on Stack Overflow; that should provide the additional information required. I've included SQL tables, C# models, and the C# method doing the Dapper call. It still feels too complicated: especially the intermediary super-class for Collection, PartialDataCollection. Is that enough detail to see what I'm doing wrong?

[jawn]

Any new feedback on this?

[danvln]

@akaSybe - thanks for the example. It works great.

I do have the question, if this works with any async - since otherwise could be a deal perf breaker.
I did not see the async functions to support this functionality - does it? I am new to Dapper & might miss things.

[akaSybe]

Hey @danvln!

Database call is asynchronous and reading from memory is sync, I guess, there is no need to do this async