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:

Transact-SQL

1

2

3

4

5

6

CREATETABLE#Products

(

Idint,

NameNVarchar(128),

Pricedecimal

)

Then insert first-level entities into this table:

Transact-SQL

1

2

3

4

5

6

7

8

INSERTINTO#Products

SELECTP.Id,P.Name,P.Price

FROMdbo.ProductsASP

ORDERBYP.CreateDate

OFFSET@Take*(@Page-1)ROWS

FETCHNEXT@TakeROWSONLY;

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:

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

-- Query all found products

SELECT*FROM#Products

-- Query product photos

SELECT

PP.ProductId,PP.PhotoUrl

FROMProductPhotosPP

WHEREPP.ProductIdIN(SELECTIdFROM#Products)

-- Query latest reviews

SELECTTOP4

R.Rating,R.CreateDate,R.ProductId,

U.NameASUserName

FROMReviewsR

JOINUsersUONR.UserId=U.Id

WHERER.ProductIdIN(SELECTIdFROM#Products)

ORDERBYR.CreateDateDESC

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

C#

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

List<ProductVm>products;

using(varmulti=connection.QueryMultiple(sqlQuery,

new{Page=command.Page,Take=command.Take))

{

products=multi.Read<ProductVm>().ToList();

varphotos=multi.Read<PhotoVm>().ToList();

products.ForEach(p=>

{

p.Photos=photos.Where(r=>r.ProductId==p.Id).ToList();

});

varlatestReviews=multi.Read<ReviewVm>().ToList();

products.ForEach(p=>

{

p.LatestReviews=latestReviews.Where(r=>r.ProductId==p.Id).ToList();

});

}

returnproducts;

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.

Related Posts

About me

Welcome on my blog!

My name is Radek Maziarka and I solve problems ;)

On the daily basis, I work in Objectivity as Technology Development Manager. My duties, both in work and outside, are connected to broadly understood programming / architecture / leadership / management.