Click here to Skip to main content
15,886,067 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900