Dapper query object with a child of IEnumerable

Question

I am trying query all countries and in each country object it would fill up the provinces.

I have the following Classes

public class Country
{
    public int Countryid { get; set; }
    public string CountryName { get; set; }

    public IEnumerable<Province> Provinces { get; set; }
}

public class Province
{
    public int ProvinceId { get; set; }
    public string ProvinceName { get; set; }
}

public IEnumerable<Country> GetCountries()
{
    var query = @"
          SELECT [Country].[CountryId], [Country].[Name] as CountryName, [Province].[ProvinceId], [Province].[Name] as ProvinceName
          FROM [Province]
            RIGHT OUTER JOIN [Country] ON [Province].[CountryId] = [Country].[CountryId]
          WHERE [Country].[CountryId] > 0";

    return _connection.Query<Country, Province, Country>(query, (country, province) =>
    {
        country.Provinces = country.Provinces.Concat(new List<Province> { province });
        return country;
    }, null);
}

The error that I get is the following:

System.ArgumentException: 'When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id Parameter name: splitOn'

I have been following this example:

https://gist.github.com/Lobstrosity/1133111

From my perspective, I don't see what I did much different besides mine being an outer join which I think should not matter, the result format is about the same.

Why do I need the split on and can this work without it?

In the gist, he uses Id is the primary key column, whereas you have CountryId and ProvinceId. Set the split on to be ProvinceId and it should work fine — Slicksim
@Slicksim I have tried that, however the problem I get is I get 100 records back as that is the total number of provinces in the database. There are 10 countries and 100 provinces, so how do I get back the 10 rows only and have provinces be part of respective countries? As for the Id, I see your point and how his is different. — Bagzli

Slicksim Slicksim · Accepted Answer · 2018-02-27T16:33:29

IIRC, I did something like this.

 var query = @"
          SELECT [Country].[CountryId], [Country].[Name] as CountryName, [Province].[ProvinceId], [Province].[Name] as ProvinceName
          FROM [Province]
            RIGHT OUTER JOIN [Country] ON [Province].[CountryId] = [Country].[CountryId]
          WHERE [Country].[CountryId] > 0";

    List<Country> countries = new List<Country>();      

    _connection.Query<Country, Province, Country>(query, (country, province) =>
    {           
        Country lastCountry = countries.FirstOrDefault(d => d.CountryId == country.Id);
        if(lastCountry == null)
        {
            countries.Add(country);
            lastCountry = country;

        }
        lastCountry.Provinces = lastCountry.Provinces.Concat(new List<Province> { province });
        return lastCountry;
    }, null);

    return countries;

I typed this out in LinqPad, so you will need to debug and check it is correct, been a long time since I used Dapper in anger

Dapper query object with a child of IEnumerable

1 Answers