Filling in a one-on-one relationship using Dapper or Linq

Question

Entity - AllSalesTerritoryContains a List<MySalesPerson>one to many relationship. I have a query Sqlto retrieve data that displays two objects using a column TerritoryId. I use the following code to populate an object with 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 starting column for the SalesPerson object when executing the Sql statement

The problem that I am facing is that such code helps to easily fill in one-to-one relationships, here I get only one value in each List<MySalesPerson>, instead of aggregating these values ​​in the collection, in fact, the same result as have SQL join query. I can easily solve the problem using a simple loop foreachand aggregating the values ​​for MySalesPerson. However, I want to find out:

• Can Dapper automatically help me with this, I tried several extensions, but they did not work as expected.
• Can Linq code do this for me, as this is somewhat the opposite of SelectManyfor an object with a one-to-many relationship.

Answer 1

AllSalesTerritory. , TerritoryId int, .

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();

, , Dapper AllSalesTerritory MySalesPerson . , , AllSalesTerritory (pd) TerritoryId. , territory . , pd territory, . MySalesPerson (pp) territory.SalesPersons.