У меня есть 3 класса POCO, как показано ниже;
Континент имеет много ContinentPart
На континенте много стран
В стране много городов
Я хочу получить Continens с ContinentParts, Country and Cities
using (IDbConnection db = new SqlConnection(_conf["ConnectionStrings:WorkConStr"]))
{
string query = @"SELECT * FROM Continent as c
LEFT JOIN ContinentPart as cp ON c.ContinentId=cp.ContinentId
LEFT JOIN Country as co ON c.ContinentId=co.ContinentId
LEFT JOIN City ci ON co.CountryId=ci.CountryId
ORDER BY c.RecDate DESC";
var continentDictionary = new Dictionary<int, Continent>();
var list = db.Query<Continent, ContinentPart, Country, City, Continent>(
query,
map: (continent, continentPart, country, city) =>
{
Continent m;
if (!continentDictionary.TryGetValue(continent.ContinentId, out m))
{
continentDictionary.Add(continent.ContinentId, m = continent);
}
if (m.ContinentParts == null)
m.ContinentParts = new List<ContinentPart>();
m.ContinentParts.Add(continentPart);
if (m.Countries == null)
m.Countries = new List<Country>();
m.Countries.Add(country);
foreach (var h in m.Countries)
{
if (h.Cities == null)
h.Cities = new List<City>();
h.Cities.Add(city);
}
return m;
},
param: new { },
splitOn: "")
.Distinct()
.ToList();
return list;
}