c# - 用Dapper映射嵌套对象的列表(3级嵌套对象)

我正在使用Dapper，并且有这样的类：

public class Region
{
    public Region()
    {
        Countries = new List<Country>(0);
    }
    public int RegionID { get; set; }

    public int RiskRank { get; set; }

    public string Name { get; set; }

    public int SiteID { get; set; }

    public string DestinationType { get; set; }

    public string HealixCode { get; set; }

    public string AreaCode { get; set; }

    public List<Country> Countries { get; set; }
}

public class Country
{
    public Country()
    {

    }

    public int CountryID { get; set; }

    public bool IsSecondary { get; set; }

    public string Name { get; set; }

    public string ISO2Code { get; set; }

    public string ISO3Code { get; set; }

    public ISOCountry ISOCountry { get; set; }

    public IList<CountryAlias> CountryAliases { get; set; }
}

public class CountryAlias
{
    public CountryAlias()
    {

    }
    public int CountryAliasID { get; set; }
    public int CountryID { get; set; }
    public string Alias { get; set; }
}

我可以获得有关所有国家/地区与地区的所有信息，但我想知道是否可以通过一个查询获得每个地区每个国家/地区的CountryAlias列表。其实我是这样的：

private const string GetAllForSiteWithoutCountriesSQL = @"SELECT * FROM Regions WHERE ChannelID = @channelID";

private const string GetAllForSiteWithCountriesSQL = @"SELECT c.*, rc.RegionID
                                                           FROM Regions r
                                                             JOIN RegionCountries rc ON rc.RegionID = r.RegionID
                                                             JOIN Countries c ON (rc.CountryID = c.CountryID AND c.IsSecondary = 0)
                                                           WHERE r.ChannelID = @channelID";

    public async Task<IEnumerable<Region>> GetAllAsync(int channelID, bool includeCountries = true)
    {
        var regions = await Database.QueryAsync<Region>(GetAllForSiteWithoutCountriesSQL, new { channelID });

        var regionMap = regions.ToDictionary(r => r.RegionID);

        if (includeCountries)
        {
            await Database.QueryAsync<Country, int, Country>(
                GetAllForSiteWithCountriesSQL,
                (country, regionID) =>
                {
                    regionMap[regionID].Countries.Add(country);
                    return country;
                }, new { channelID }, splitOn: "RegionID");
        }

        return regions;
    }

我还找到了一个很好的解释here，但由于我还有Group类，因此我不明白如何使用它。我应该如何使用Dapper做到这一点，有可能还是唯一的方法是执行不同的步骤？谢谢

最佳答案

我知道这个问题很老，但是前一段时间我也遇到过同样的问题，对于仍然有疑问的人，可以尝试以下方法。

这两个查询的SQL：

SELECT r.*, c.*, a.*
    FROM Regions as r
        LEFT JOIN Countries as c
            ON a.RegionID = c.RegionID
                LEFT JOIN CountryAliases as a
                    ON a.RegionID = a.RegionID
    WHERE r.ChannelID  = @ChannelID

仍然您的Region对象没有ChannelID属性，因此您可以这样做：

C＃

New {RegionID = channelID}

VB.NET

New With {.RegionID = channelID}

我也不知道这个频道是关于什么的，但似乎是您搜索的关键。

C＃

Dictionary<int, Region> RegionDictionary = new Dictionary<int, Region>();
Await Database.QueryAsync<Region, Country, CountryAlias, Region>(sql,
    (region, country, countryalias) => {
    Region _region = new Region();

    if(!RegionDictionary.TryGetValue(region.RegionID, out _region)){
        RegionDictionary.Add(region.RegionID, _region = region);
    }

    if(_region.Countries == null){
        _region.Countries = new List<Country>();
    }
    if(countryalias != null){
        // begin <this line might be discarded>
        if(country.CountryAliases == null){
            country.CountryAliases = new List<CountryAlias>();
        }
        // end
        country.CountryAliases.Add(countryalias);
    }
    _region.Countries.Add(country);
    return _region;
}, new {channelID}, splitOn: "CountryID, CountryAliasID, RegionID");

return RegionDictionary.Values.ToList();

VB.NET

Dim RegionDictionary As New Dictionary(Of Integer, Region)
Await Database.QueryAsync(Of Region, Country, CountryAlias, Region)(sql,
Function(region, country, countryalias)
    Dim _region As New Region();
    if(!RegionDictionary.TryGetValue(region.RegionID, _region)) Then
        _region = region
        RegionDictionary.Add(region.RegionID, region)
        If IsNothing(_region.Countries) Then
            _region.Countries = new List(Of Country)
        End If
        If Not IsNothing(countryalias) Then
            ' begin <this line might be discarded>
            If IsNothing(country.CountryAliases) Then
                country.CountryAliases = new List(Of CountryAlias)
            End If
            ' end
            country.CountryAliases.Add(countryalias)
        End If
        _region.Countries.Add(country)
    End If
End Function, New With {channelID}, splitOn: "CountryID, CountryAliasID, RegionID")

Return RegionDictionary.Values.ToList()

如果您想知道如何使用相同的逻辑work more than 7 types，可以执行以下操作：

C＃

Dictionary<int, Region> RegionDictionary = new Dictionary<int, Region>();
Await Database.QueryAsync<Region>(sql,
    new[]
    {
        typeof(Region),
        typeof(Country),
        typeof(CountryAlias)
    },
    obj => {
        Region region = obj[0] as Region;
        Country country = obj[1] as Country;
        CountryAlias countryalias = obj[2] as CountryAlias;
        Region _region = new Region();

        if(!RegionDictionary.TryGetValue(region.RegionID, out _region)){
            RegionDictionary.Add(region.RegionID, _region = region);
        }

        if(_region.Countries == null){
            _region.Countries = new List<Country>();
        }
        if(countryalias != null){
            // begin <this line might be discarded>
            if(country.CountryAliases == null){
                country.CountryAliases = new List<CountryAlias>();
            }
            // end
            country.CountryAliases.Add(countryalias);
        }
        _region.Countries.Add(country);
        return _region;
}, new {channelID}, splitOn: "CountryID, CountryAliasID, RegionID");

return RegionDictionary.Values.ToList();

这一切都是关于创建字典，然后将一些值放入字典中，具体取决于是否已经存在具有相应ID的值。