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

Question

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

.net asp.net-web-api2 c# dapper orm

Question

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 also found a good explanation here 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

Ameya Rane

View more on Stack Overflow

Edilson · Accepted Answer · 4/27/2019 5:21:12 PM

i know this question is old, but i've had the same problem a while ago, for those who still have doubts, you can try the following.

The SQL for both queries:

SELECT r.*, c.*, a.*
    FROM Regions as r
        LEFT JOIN Countries as c 
            ON a.RegionID = c.RegionID
                LEFT JOIN CountryAliases as a
                    ON a.RegionID = a.RegionID
    WHERE r.ChannelID  = @ChannelID

Still your Region object don't have a ChannelID property, so instead you could've done:

C#

New {RegionID = channelID}

VB.NET

New With {.RegionID = channelID}

Also i don't know what this channel is about, but seems to act as a key for your search.

C#

Dictionary<int, Region> RegionDictionary = new Dictionary<int, Region>();
Await Database.QueryAsync<Region, Country, CountryAlias, Region>(sql,
    (region, country, countryalias) => {
    Region _region = new Region();

    if(!RegionDictionary.TryGetValue(region.RegionID, out _region)){
        RegionDictionary.Add(region.RegionID, _region = region);
    }

    if(_region.Countries == null){
        _region.Countries = new List<Country>();
    }
    if(countryalias != null){
        // begin <this line might be discarded>
        if(country.CountryAliases == null){
            country.CountryAliases = new List<CountryAlias>();
        }
        // end
        country.CountryAliases.Add(countryalias);
    }
    _region.Countries.Add(country);
    return _region;
}, new {channelID}, splitOn: "CountryID, CountryAliasID, RegionID");

return RegionDictionary.Values.ToList();

VB.NET

Dim RegionDictionary As New Dictionary(Of Integer, Region)
Await Database.QueryAsync(Of Region, Country, CountryAlias, Region)(sql,
Function(region, country, countryalias)
    Dim _region As New Region();
    if(!RegionDictionary.TryGetValue(region.RegionID, _region)) Then
        _region = region
        RegionDictionary.Add(region.RegionID, region)
        If IsNothing(_region.Countries) Then
            _region.Countries = new List(Of Country)
        End If
        If Not IsNothing(countryalias) Then
            ' begin <this line might be discarded>
            If IsNothing(country.CountryAliases) Then
                country.CountryAliases = new List(Of CountryAlias)
            End If
            ' end
            country.CountryAliases.Add(countryalias)
        End If
        _region.Countries.Add(country)
    End If
End Function, New With {channelID}, splitOn: "CountryID, CountryAliasID, RegionID")

Return RegionDictionary.Values.ToList()

If you're wondering about how can you work more than 7 types with this same logic, you can do:

C#

Dictionary<int, Region> RegionDictionary = new Dictionary<int, Region>();
Await Database.QueryAsync<Region>(sql,
    new[]
    {
        typeof(Region),
        typeof(Country),
        typeof(CountryAlias)
    },
    obj => {
        Region region = obj[0] as Region;
        Country country = obj[1] as Country;
        CountryAlias countryalias = obj[2] as CountryAlias;
        Region _region = new Region();

        if(!RegionDictionary.TryGetValue(region.RegionID, out _region)){
            RegionDictionary.Add(region.RegionID, _region = region);
        }

        if(_region.Countries == null){
            _region.Countries = new List<Country>();
        }
        if(countryalias != null){
            // begin <this line might be discarded>
            if(country.CountryAliases == null){
                country.CountryAliases = new List<CountryAlias>();
            }
            // end
            country.CountryAliases.Add(countryalias);
        }
        _region.Countries.Add(country);
        return _region;
}, new {channelID}, splitOn: "CountryID, CountryAliasID, RegionID");

return RegionDictionary.Values.ToList();

It's all about creating a dictionary then putting some values into it, depending on whether there's already a value with the respective ID or not.

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

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator