Post
Ask Question
Ameya Rane

Ameya Rane

  • NA
  • 37
  • 18.2k

Map lists of nested objects with Dapper (3 level nested obj)

Aug 5 2017 1:34 AM
I'm using Dapper and I have classes like this:
 
 
  1. public class Region  
  2.  {  
  3.      public Region()  
  4.      {  
  5.          Countries = new List<Country>(0);  
  6.      }  
  7.      public int RegionID { get; set; }  
  8.   
  9.      public int RiskRank { get; set; }  
  10.   
  11.      public string Name { get; set; }  
  12.   
  13.      public int SiteID { get; set; }  
  14.   
  15.      public string DestinationType { get; set; }  
  16.   
  17.      public string HealixCode { get; set; }  
  18.   
  19.      public string AreaCode { get; set; }  
  20.   
  21.      public List<Country> Countries { get; set; }  
  22.  }  
  23.   
  24. public class Country  
  25.  {  
  26.      public Country()  
  27.      {  
  28.   
  29.      }  
  30.   
  31.      public int CountryID { get; set; }  
  32.   
  33.      public bool IsSecondary { get; set; }  
  34.   
  35.      public string Name { get; set; }  
  36.   
  37.      public string ISO2Code { get; set; }  
  38.   
  39.      public string ISO3Code { get; set; }  
  40.   
  41.      public ISOCountry ISOCountry { get; set; }  
  42.   
  43.      public IList<CountryAlias> CountryAliases { get; set; }  
  44.  }  
  45.   
  46.   
  47.  public class CountryAlias  
  48.  {  
  49.      public CountryAlias()  
  50.      {  
  51.   
  52.      }  
  53.      public int CountryAliasID { get; set; }  
  54.      public int CountryID { get; set; }  
  55.      public string Alias { get; set; }  
  56.  }   

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:

  1. private const string GetAllForSiteWithoutCountriesSQL = @"SELECT * FROM Regions WHERE ChannelID = @channelID";   
  2.   
  3. 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";  
  4.   
  5. public async Task<IEnumerable<Region>> GetAllAsync(int channelID, bool includeCountries = true)  
  6. {  
  7.    var regions = await Database.QueryAsync<Region> GetAllForSiteWithoutCountriesSQL, new { channelID });  
  8.   
  9.     var regionMap = regions.ToDictionary(r => r.RegionID);  
  10.   
  11.      if (includeCountries)  
  12.       {  
  13.              await Database.QueryAsync<Country, int, Country>(  
  14.                GetAllForSiteWithCountriesSQL,  
  15.                     (country, regionID) =>  
  16.                     {  
  17.                         regionMap[regionID].Countries.Add(country);  
  18.                         return country;  
  19.                     }, new { channelID }, splitOn: "RegionID");  
  20.        }  
  21.   
  22.        return regions;  
  23.  }  
 
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
 
 
 

Answers (1)