Dapper – many to many relation in a single request

During my CQRS journey, I implemented many-to-many data querying, in the single database request. I achieved getting all products with associated entities at the same time.

To achieve the same, we need to define a temporary table to store all first-level entities:

Then insert first-level entities into this table:

With such temporary table, we are able to query it multiple times – to return values and to use it in additional subqueries to gather related entities:

At the end, we map all the data to C# objects:

Dapper has an extension QueryMultiple which allows reading multiple times from the query. Every Read usages gather data from the next SELECT statement in SQL query. With above solution, we don’t need to make roundtrips to the database to gather the data.