Dapper nested list query

Question

Dapper nested list query

dapper list nested

Question

I have 3 POCO classes as below;

Continent has many ContinentPart

Continent has many Country

Country has many City

I want to get Continens with ContinentParts, Countries 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;
            }

murat kurtun

View more on Stack Overflow

mauridb · Accepted Answer · 4/30/2018 12:59:23 AM

You have two options here. You can use Multiple Resultsets feature:

https://medium.com/dapper-net/handling-multiple-resultsets-4b108a8c5172

Or, another option, better in my opinion, is to return the joined result as a JSON, for example with a query like the following:

select 
    continents.continent_id,
    continents.continent,
    countries.country_id,
    countries.country,
    cities.city_id,
    cities.city 
from 
    dbo.Continent continents
inner join 
    dbo.Country countries on continents.continent_id = countries.continent_id
inner join
    dbo.City cities on countries.country_id = cities.country_id
for 
    json auto

that returns a JSON like this:

{
    "continent_id": 1,
    "continent": "Africa",
    "countries":
    [
        {
            "country_id": 1,
            "country": "Nigeria",
            "cities":           
            [   
                {
                    "city_id": 1,
                    "city": "Lagos"
                }, {
                    "city_id": 2,
                    "city": "Abuja"
                }
            ]
        }
    ]
}

and that can then be turned into a complex object using Custom Type Handling:

https://medium.com/dapper-net/custom-type-handling-4b447b97c620

Links are to articles I've wrote on the subject.

Dapper nested list query

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator