"MultiLevels" Mapping Dapper using SplitOn

Question

Most of examples/question just introduce solutions to map "only one" level of the query using split on like this:

var sql = "SELECT P.Id, P.FirstName, P.LastName, " +
    "A.Id AS AddressId, A.StreetNumber, A.StreetName, A.City, A.State " +
    "FROM People P INNER JOIN Addresses A ON A.AddressId = P.AddressId; ";

db.Query<Person, Address, Person>( sql,  (person, address) => {
    person.Address = address;
    return person; }, splitOn: "AddressId" ).ToList();

I have a query like this one (just an example):

Select * from Country C 
inner join State S 
on C.CountryId = S.CountryId 
inner join City Ct 
on S.StateId = Ct.StateId

How could I map it using dapper to my Model/Class?

_splitOn_ can be used to split on many fields, not just one _splitOn="countryID,CityID"_ Of course the query should change to _db.Query(...., (c,s,t) =>....._ but you should also post your classes to better understand the relationship between these classes — Steve, Aug 21 '18 at 20:59
@Steve, this is just an example, I am looking for a manner to mapping my entity on "deepers level". your example "db.Query" is exactly what I am looking, but how can I map it on dapper? — Mario Guadagnin, Aug 21 '18 at 21:22
Not sure if question is not outdated already, but still. Did you mean that you need to get countries with filled collections of state, where every state has filled collection of cities? — KozhevnikovDmitry, Sep 05 '18 at 05:32

KozhevnikovDmitry · Accepted Answer · 2018-09-06T15:14:33.033

There is no out of box solution for your needs in Dapper or its extensions. Dapper maps every single row in result set separately. So you need some extra mapping in order to do something like what you want. You can do it manually after getting results of Query with multiple splitOn. Or use some mapping tool. Please, consider this question with various answers. Adapted to your case the solution(with Slapper.Automapper mapping) would be:

[Test]
public async Task MultipleSplitOn()
{
    // Arrange
    using (var conn =new SqlConnection("Data Source=YourDb"))
    {
        await conn.OpenAsync();

        var sql = @"SELECT TOP 10 c.[Id] as CountryId
                    ,c.[Name]
                    ,s.[Id] as States_StateId
                    ,s.[Name] as States_Name
                    ,ct.[Id] as States_Cities_CityId
                    ,ct.[Name] as States_Cities_Name
                FROM Country c 
                JOIN State s ON s.[CountryId] = c.[Id]
                JOIN City ct ON ct.[StateId] = s.[Id] ";

        // Act
        dynamic result = await conn.QueryAsync<dynamic>(sql);

        Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(Country), new [] { "CountryId" });
        Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(State), new [] { "StateId" });
        Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(City), new [] { "CityId" });

        var countries = (Slapper.AutoMapper.MapDynamic<Country>(result) as IEnumerable<Country>).ToList();

        //Assert
        Assert.IsNotEmpty(countries);
        foreach (var country in countries)
        {
            Assert.IsNotEmpty(country.States);

            foreach (var state in country.States)
            {
                Assert.IsNotEmpty(state.Cities);
            }
        }
    }
}

public class Country
{
    public int CountryId { get; set; }

    public string Name { get; set; }

    public List<State> States { get; set; }
}

public class State
{
    public int StateId { get; set; }

    public string Name { get; set; }

    public List<City> Cities { get; set; }
}

public class City
{
    public int CityId { get; set; }

    public string Name { get; set; }
}

I got the following error at the mapdynamic line: System.ArgumentException: 'An item with the same key has already been added. Key: Id' — Mario Guadagnin, Sep 06 '18 at 15:07
Sorry, this is my bad. Seemingly Slapper.AutoMapper requiers unique identifiers fields names. That spoils the impression, because properies of the entities should be renamed too. Please try the updated example. — KozhevnikovDmitry, Sep 06 '18 at 15:17
Now the code is working but the navigation properties is still empties :( — Mario Guadagnin, Sep 06 '18 at 15:32
The example is correct now, I have checked. Can you post your SQL text? Notice that aliases in result set are inportant and have to satisfy the convention. — KozhevnikovDmitry, Sep 07 '18 at 01:38
probably, my problem is my entities, both use "Id" for all Primary keys. if I alter theses keys for specific names I would had to alter the whole references on the application. — Mario Guadagnin, Sep 10 '18 at 18:34
Sure, it is not really elegant solution, but what did you want from micro orm?=) For primary keys issue you can use kinda alias property approach. For instance `public int CountryId { get => Id; set => Id = value; }` for `Country`. — KozhevnikovDmitry, Sep 11 '18 at 01:24

"MultiLevels" Mapping Dapper using SplitOn

1 Answers1

Linked