使用Dapper映射嵌套对象列表(3级嵌套对象)
使用Dapper映射嵌套对象列表(3级嵌套对象)
提问于 2017-08-04 19:02:30
我正在使用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做到这一点，这是可能的，还是唯一的方法是做不同的步骤？谢谢
回答 1
Stack Overflow用户
发布于 2019-04-28 01:21:12
我知道这个问题很老，但是我之前也遇到过同样的问题，对于那些仍然有疑问的人，你可以尝试下面的方法。
这两个查询的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的值。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45505157
复制相关文章
相似问题