Question
I use Dapper and have the following classes:
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'm able to get all data on regions and all nations, but I'm curious whether it's also possible to obtain a list of country aliases for each area and each country with a single query. Actually, this is what I do:
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 decent explanation at here, but I'm not sure how to apply it to my situation as I also have the Group class. Is it feasible to do this using Dapper, or do I need to take additional steps? Thanks
Popular Answer
I realize this question is old, but I've run across this issue before. If you're still unsure, you may attempt the steps below.
Each query's SQL is as follows:
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 yourRegion
Since the object doesn't contain a ChannelID attribute, you may have done:
C#
New {RegionID = channelID}
VB.NET
New With {.RegionID = channelID}
Additionally, while I'm not sure what this channel is for, it seems to serve as a key for your search.
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()
If you're unsure of how to proceed using the same reasoning, try these steps:
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();
It all involves constructing a dictionary and adding values to it, depending on whether or not an item with the appropriate ID already exists.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow