amey.rane86 Ответов: 0

Отображение списков вложенных объектов с помощью 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

0 Ответов