6

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
user990423
  • 1,397
  • 2
  • 12
  • 32
Mrinal Kamboj
  • 11,300
  • 5
  • 40
  • 74
  • possible duplicate of [How do I write one to many query in Dapper.Net?](http://stackoverflow.com/questions/9350467/how-do-i-write-one-to-many-query-in-dapper-net) – juharr Sep 28 '15 at 18:41
  • I have tried Slapper.Automapper mentioned in the link you have referred, but its not working as expected, leading to aborting the call inside the dapper extension binary, without much exception information to debug – Mrinal Kamboj Sep 28 '15 at 18:51
  • I personally have never used Slapper.AutoMapper, but though that question is definitely a duplicate. It even has dictionary answers like mine. Also this [answer](http://stackoverflow.com/a/6380756/302918) is related and from one of the creators of Dapper. – juharr Sep 28 '15 at 19:05
  • Thanks @juharr, Sam's solution worked for me out of the box with a minor modification to handle the null key when GroupBy is done, else the Dictionary was failing for the null key – Mrinal Kamboj Sep 29 '15 at 06:40

1 Answers1

14

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.

juharr
  • 31,741
  • 4
  • 58
  • 93
  • Thanks it does work, let me see if I can get a response for Dapper directly handling the issue, but one question, though I am not using Dictionary, but your solution is also declaring AllSalesTerritory as a local variable, then how does it aggregate the SalesPerson internally in the mapping function. We are even fetching it from dictionary except the first time. I am surely missing something out here – Mrinal Kamboj Sep 28 '15 at 18:30
  • That's what the dictionary is for. It keeps track of the unique `AllSalesTerritory` items. I'll add some description to the answer. – juharr Sep 28 '15 at 18:34
  • Got it thanks a lot, I completely missed out the "out territory" inside TryGetValue, which does the job of reference assignment, so its local only for the first time during assignment to the dictionary and thereon it is fetched using dictionary and in fact that's why probably we are not using the result of _connection.Query, which would lead to similar result as my question – Mrinal Kamboj Sep 28 '15 at 18:49
  • If I get a solution via Dapper, then that shall provide this as a result of _connection.Query. I will wait for sometime for a pure Dapper answer, before marking yours as a correct answer, thanks for the help – Mrinal Kamboj Sep 28 '15 at 18:53
  • As far as I know there isn't a more native way to do this in Dapper. Also the results of the `Query` would contain the correct objects, but there would be duplicate references. – juharr Sep 28 '15 at 19:00