CODEWITHSUNDEEP
c#dapper
GustavoAndrade
GustavoAndrade

Reputation: 247

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

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?

Upvotes: 2

Views: 1849

Answers (2)

David Sexton
David Sexton

Reputation: 9

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

Upvotes: 0

BlackjacketMack
BlackjacketMack

Reputation: 5692

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

Upvotes: 1

Related Questions