Displaying lists of nested objects with Dapper (3-level nested object)
I am using Dapper and I have classes like this:
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; }
}
I can get all the information about the regions with all countries, but I would like to know if it is possible with one query to get also the CountryAlias list for each country for each region. I actually do this:
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;
}
I also found a good explanation here , but I don't understand how to use it in my case because I also have a group class. How do I do this with Dapper, is this possible or the only way to do different steps? Thanks to
+3
source to share
1 answer
I know this question is old, but I had the same problem a while ago, for those who still have doubts, you can try the following.
SQL for both queries:
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
However, your object Region
doesn't have a ChannelID property, so instead you could do:
FROM#
New {RegionID = channelID}
VB.NET
New With {.RegionID = channelID}
Also, I don't know what this feed is about, but it seems to be the key to your search.
FROM#
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()
If you're wondering how you can work with more than 7 types with the same logic, you can do:
FROM#
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();
The whole point is to create a dictionary and put some values in it, depending on whether there is already a value with the corresponding identifier or not.
0
source to share