I'm using Dapper and I have classes like this:
- public class Region
- {
- public Region()
- {
- Countries = new List
(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
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
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
> GetAllAsync(int channelID, bool includeCountries = true) - {
- var regions = await Database.QueryAsync
GetAllForSiteWithoutCountriesSQL, new { channelID }); - var regionMap = regions.ToDictionary(r => r.RegionID);
- if (includeCountries)
- {
- await Database.QueryAsync
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:-
https://stackoverflow.com/questions/7508322/how-do-i-map-lists-of-nested-objects-with-dapper/17748734#17748734
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
Gokhul VarmanPosted Aug 6, 2017, 9:40 AM