After execute a Dapper query, how to I map a master/detail entity?

dapper data-modeling

Question

At work, we're thinking aboutDapper . Our master/detail objects are loaded individually in the current code base. This often implies that one sql statement is run for each master record and one sql statement is run for each detail record.

I'd want to place two calls utilizing to enhance performance and facilitate the changeover.Dapper and then combine the outcomes:

public class Zoo
{
    Guid Id { get; set; }
    string Name { get; set;}
    IList<Monkey> Monkeys { get; set; }
}

public class Monkey
{
    Guid Id { get; set; }
    Guid ZooId { get; set; }
    string Name { get; set; }
    bool DeservesBanana { get; set; }
}

var sqlZoos = "SELECT * FROM Zoos"
var Zoos = connection.Query( sqlZoos )

var sqlMonkeys = "SELECT * FROM Monkeys"
var Zoos = connection.Query( sqlZoos )

foreach( var zoo in Zoos )
{
  zoo.Monkeys = Monkeys.Where( x => x.ZooId = zoo.Id ).ToList();
}

Exists a helper method or something similar inDapper something I may use to swap theforeach Should I employ anything similar?AutoMapper instead? Otherwise, is there a more effective method (than theforeach using the.NET library (loop)?

1
0
10/6/2017 12:31:58 AM

Popular Answer

Try something similar to this:

var zoos = new Dictionary<string, Zoo>();
var query = "SELECT * FROM Zoos LEFT OUTER JOIN Monkeys ON Monkeys.ZooId = Zoos.Id";

_connection.Query<Zoo, Monkey, Zoo>(query, (z, m) =>
{
    if (!zoos.TryGetValue(z.Id, out Zoo zoo))
        zoos.Add(z.Id, zoo = z);

    zoo.Monkeys.Add(m);
    return zoo;
});
0
10/6/2017 7:52:26 AM


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow