“MultiLevels” Mapping Dapper using SplitOn

问题:

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?

回答1:

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; }
}