Create a multi-mapping query in Dapper with three levels deep mapping?

Question

I'm trying to write 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?

BlackjacketMack · Accepted Answer · 2016-08-11 14:37:04Z

1

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).

edited Aug 11, 2016 at 14:37

answered Aug 10, 2016 at 0:55

BlackjacketMack

5,7321 gold badge33 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GustavoAndrade Over a year ago

Hum, I'll try that tomorrow. Not possible to do it in a single query?

BlackjacketMack Over a year ago

You could do it in one query and then groupby a's. I like it more with two and not doing a full map to A in query 2. You could also have a parameter called WithDetails that optionally returns query2. So you have a super fast one table query (e.g.. List page) and a details query.

BlackjacketMack Over a year ago

One last comment...you probably don't need to map the full query2.A...as long as query.B has the Aid on it you can match it up to the query1.A.AId. A minor point, but it would save you from having to make that join in query2 and would reduce the number of columns returned...both good things. I'm going to update my response to clarify that.

David Sexton · Accepted Answer · 2016-08-29 09:42:14Z

You could probably accomplish in one round trip to the db using Drapper (built on top of Dapper).

Assuming you have a set of SQL queries that return multiple results:

select * from [TableA]; 
select * from [TableB]; 
select * from [TableC]; 
select * from [TableD]

...with each result holding some form of identifier/foreign key to the others, you could probably build up a repository like the one below:

public class Repository : IRepository
{
    // IDbCommander is a Drapper construct.
    private readonly IDbCommander _commander;

    public Repository(IDbCommander commander)
    {
        _commander = commander;
    }

    public IEnumerable<A> RetrieveAll()
    {
        // execute the multiple queries and 
        // pass control to a mapping function.
        return _commander.Query(Map.Results);
    }

    private static class Map
    {
        internal static Func<IEnumerable<A>,
                            IEnumerable<B>,
                            IEnumerable<C>,
                            IEnumerable<C>,
                            IEnumerable<A>> Results = (collectionA, collectionB, collectionC, collectionD) => 
        {
            // map C and D to B based on their Id's
            collectionB.C = collectionC.SingleOrDefault(c => c.Id == b.Id);
            collectionB.D = collectionD.SingleOrDefault(d => d.Id == b.Id);

            // now map B to A.
            collectionA.B = collectionB.Where(b => b.Id == a.Id).ToList();
            return collectionA;
        }
    }
}

Example typed from memory so syntax could be a little off but you get the gist.

I agree with BlackjacetMack that you should rather have some form of pagination in the results (supported in Drapper as well).

Collectives™ on Stack Overflow

Create a multi-mapping query in Dapper with three levels deep mapping?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related