GustavoAndrade GustavoAndrade - 3 months ago 57
C# Question

How to create a multi-mapping query in Dapper with three levels deep mapping?

I'm try to do a query that correctly maps the following:

public class A
{
public int Id { get; set; }
public string Name { get; set; }
public int BId {get; set; }
public List<B> { get; set; }
}

public class B
{
public int Id { get; set; }
public string Name { get; set; }
public int CId { get; set; }
public int DId { get; set; }
public C C { get; set; }
public D D { get; set; }
}

public class C
{
public int Id { get; set; }
public string Name { get; set; }
}

public class D
{
public int Id { get; set; }
public string Name { get; set; }
}


How do I write a query that maps correctly entity A with a list of entity B with C and D filled?

Answer

You want to do two queries here, to capture the one-to-many nature of B on A. Also, your B's are probably going to need a reference back to A in the query.

var query1 = conn.Query<A>(select * from dbo.A)

var query2 = conn.Query<B,C,D,B>("select * from dbo.B...join C...join 

D",(b,c,d)=>{



     b.C = c;
        b.D = d;

return b;

        }

Now you'll have to wire them together. I use linq joins and extensions to automate a bunch of it, but the gist is, loop over each 'A' and find the matching 'B's from query 2. Dictionaries and lists might be faster than 'Where' clauses as well, so you can write an extension that optimizes the loop below.

foreach(var a in query1){
a.Bs = query2.Where(w=>w.AId.Equals(a.Id));
}

Note that you can reduce trips to the db by using QueryMultiple to return multiple recordsets (should your db support that).