使用Dapper或通过Linq来建立一对多关系

问题描述

实体-AllSalesTerritory包含表示一对多关系的List<MySalesPerson>.我有Sql查询，以获取使用列TerritoryId映射两个实体的数据.我使用以下代码使用Dapper micro ORM填充实体:

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

BusinessEntityId是执行Sql语句时SalesPerson实体的起始列

我面临的挑战是，这种代码有助于轻松地填充一对一的关系，这里我在每个List<MySalesPerson>中仅获得一个值，而不是在集合中汇总这些值，其结果与SQL连接查询.我可以使用简单的foreach循环并汇总MySalesPerson的值来轻松解决此问题.但是，我想弄清楚:

1. Dapper可以自动帮我实现吗，尝试了很少的扩展，但是它们没有按预期工作
2. Linq代码可以帮我做吗，因为这与具有一对多关系的实体上的SelectMany有点相反

解决方案

您可以使用字典来跟踪唯一的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.然后，我们使用字典来查看基于TerritoryId的当前AllSalesTerritory(pd).如果是这样，则将本地territory变量分配给该对象的引用.如果不是，则将pd分配给territory，然后将其添加到字典中.然后，我们只需将当前的MySalesPerson(pp)添加到territory.SalesPersons列表中即可.

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

解决方案

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.

这篇关于使用Dapper或通过Linq来建立一对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！