Can Dapper only return flat classes?

dapper entity-framework

Question

I am an Entity Framework user looking at Dapper as an alternative. In Entity Framework, I am able to do this and it will populate my Team objects and the player collection property in each one and the team property inside each player.

var players = context.Teams
.Include(x => x.Players)
.AsNoTracking()                                    
.Where(x => x.SportId == sportId).ToList();

I found this is Dapper but it just seems to return a flat player object with no team data at all. Is there a way to make Dapper do what EF does and if so, how?

var players = conn.Query<PlayerDTO, TeamDTO, PlayerDTO>("SELECT p.Id, 
p.FirstName, p.LastName, p.DateOfBirth, p.TeamId, t.Id as TeamId, t.Name, 
t.SportId FROM Team t "+ "INNER JOIN Player p ON t.Id = p.TeamId WHERE 
t.SportId = @ID", (player, team) => { return player; }, splitOn: "TeamId",
param: new { ID = sportId });

Accepted Answer

That isn't built in currently. But what is interesting to me is that there isn't actually a very good way to do this in SQL, either. Ultimately, SQL data is flat, so it shouldn't be a surprise that dapper (which sits pretty close to SQL) is also relatively flat.

It does have the multi-map stuff you've found. I would also be open to the possibility of a better multi-grid reader here that dealt with the required aggregation, but... it isn't a trivial API to implement or even to design, so it hasn't been done yet simply because it doesn't seem to be hugely demanded.

I suspect if you ask EF what SQL it is running for that, you'll find it is pretty inefficient. The choices are n+1 (x levels), or ultra-wide repetitive grids with masses of duplicated data.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why