Отображение списков вложенных объектов с помощью dapper (3-уровневый вложенный объект)
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'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; }
}
What I have tried:
<pre>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 <a href="https://stackoverflow.com/a/17748734/2204958">here</a>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