Question
I'm 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 information about Regions with all countries with but I would like to know if is possible with one query get also the list of CountryAlias for each country for each region. Actually what I do is 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 have also the Group class. How should I do this with Dapper, is it possible or the only way is to do different steps? Thanks
Popular Answer
i know this question is old, but i've had the same problem a while ago, for those who still have doubts, you can try the following.
The 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
Still your Region object don't have a ChannelID property, so instead you could've done:
C#
New {RegionID = channelID}
VB.NET
New With {.RegionID = channelID}
Also i don't know what this channel is about, but seems to act 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 wondering about how can you work more than 7 types with this same logic, you can do:
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's all about creating a dictionary then putting some values into it, depending on whether there's already a value with the respective ID or not.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow