Dapper with Grandchildren

Question

My question is base upon the following schema:

public class EntityA
{
    // Primary Properties
    public Guid ID { get; set; }
    public string Name { get; set; }

    // Navigation Properties    
    public LinkingEntity LinkingEntity { get; set; }
}

public class EntityB
{
    // Primary Properties
    public Guid ID { get; set; }
    public string Name { get; set; }

    // Navigation Properties
    public LinkingEntity LinkingEntity { get; set; }
}

public class LinkingEntity
{
    // Primary Properties
    public Guid ID { get; set; }
    public Guid EntityAID { get; set; }
    public Guid EntityBID { get; set; }
    
    // Navigation Properties
    public EntityA EntityA { get; set; }
    public EntityB EntityB { get; set; }
}

The following is the dapper code:

string sql = @"SELECT 
                      [EntityA].[ID],
                      [EntityA].[Name],
                      [LinkingEntity].[ID],
                      [LinkingEntity].[EntityAID],
                      [LinkingEntity].[EntityBID],
                      [EntityB].[ID],
                      [EntityB].[Name]
                  FROM 
                      [dbo].[EntityA]
                  JOIN
                      [dbo].[LinkingEntity] ON [EntityA].[ID] = [LinkingEntity].[EntityAID]

                  JOIN
                      [dbo].[EntityB] ON [LinkingEntity].[EntityBID] = [EntityB].[ID];";
using (var connection = CreateConnection())
{
    return await connection.QueryAsync(
        sql: sql, 
        (entityA, LinkingEntity, entityB) =>
            {
                if(entityA.LinkingEntities == null)
                {
                    entityA.LinkingEntities = new List();
                }
                LinkingEntity.EntityB = entityB;
                entityA.LinkingEntities.Add(LinkingEntity);

                return entityA;
            }
        );
}

The problem is that I am only getting one entityB coming through when I know there is multiple EntityBs for each EntityA.

My question is that how do i construct a dapper statement to populate the make sure that the set of EntityB is populated?

Palle Due · Answer

First you need to understand that a join is like a table, it only has columns and rows, so the entityA you encounter in line 1, is not the same as the one you encounter in line 2 even if the guid's are the same. You therefore need to keep count of your entities in another way. Best practice is some kind of dictionary.

var entityALookup = new Dictionary();
var entityBLookup = new Dictionary();
using (var connection = CreateConnection())
{
    return await connection.QueryAsync(
        sql: sql, 
        (entityA, linkingEntity, entityB) =>
            {
                EntityA lookupEntityA;
                if (!entityALookup.TryGetValue(entityA.ID, out lookupEntityA)
                {
                    entityALookup.Add(entityA.ID, lookupEntityA = entityA);
                    lookupEntityA.LinkingEntities = new List();
                }
                EntityB lookupEntityB;
                if (!entityBLookup.TryGetValue(entityB.ID, out lookupEntityB)
                {
                    entityBLookup.Add(entityB.ID, lookupEntityB = entityB);
                    // I don't know if you need this
                    lookupEntityB.LinkingEntities = new List();
                }
                linkingEntity.EntityB = lookupEntityB;
                lookupEntityA.LinkingEntities.Add(linkingEntity);

                return null;
            }
        );
}

The mapping function is returning null, because the data you really need is now in entityALookup. I suppose you also need to fill the LinkingEntities of EntityB, but that should be possible using the same method.

Dapper with Grandchildren

1 Answers