Filling one to many relationship using using Dapper or via Linq

Question

Entity - AllSalesTerritory contains List<MySalesPerson> representing one to many relationship. I have Sql query to fetch the data where the two entities are mapped using a column TerritoryId. I use a following code to fill the entity using Dapper micro ORM:

 List<AllSalesTerritory> allSalesTerrotories = _connection.Query<AllSalesTerritory, MySalesPerson, AllSalesTerritory>
                (query, (pd, pp) =>
                {
                    pd.SalesPersons.Add(pp);
                    return pd;
                }, splitOn: "BusinessEntityId")
                .ToList();

BusinessEntityId is the beginning column for SalesPerson entity on executing the Sql statement

Challenge that I face is, this kind of code helps in easily filling one to one relation, here I get just one value in each List<MySalesPerson>, instead of aggregating those values in the collection, essentially the same result as that of SQL join query. I can easily resolve the issue using a simple foreach loop and aggregating the values for MySalesPerson. However, I want to figure out:

1. Can Dapper automatically help me achieve it, tried few extensions, but they did not work as expected
2. Can a Linq code do it for me, since this is somewhat reverse of a SelectMany on an entity with one to many relationship

Answer 1

You can use a dictionary to keep track of the unique AllSalesTerritory objects. Assuming that the TerritoryId property is an int this would work.

var territories = new Dictionary<int, AllSalesTerritory>()
_connection.Query<AllSalesTerritory, MySalesPerson, AllSalesTerritory>
    (query, (pd, pp) =>
    {
        AllSalesTerritory territory;
        if(!territories.TryGetValue(pd.TerritoryId, out territory))
        {
            territories.Add(pd.TerritoryId, territory = pd);
        }       

        territory.SalesPersons.Add(pp);
        return territory;
    }, splitOn: "BusinessEntityId");

List<AllSalesTerritory> allSalesTerrotories = territories.Values.ToList();

Basically what happens here is that Dapper will return one AllSalesTerritory and one MySalesPerson for each row in the results of your query. We then use a dictionary to see if the current AllSalesTerritory (pd) has been seen before based on the TerritoryId. If so then the local territory variable is assigned the reference to that object. If not then we assign pd to territory and then add that to the dictionary. Then we just add the current MySalesPerson (pp) to the territory.SalesPersons list.