“多级”使用SplitOn映射Dapper [英] "MultiLevels" Mapping Dapper using SplitOn

问题描述

大多数示例/问题只是介绍了使用split on映射查询仅一个级别的解决方案，例如：

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

如何使用dapper将其映射到我的模型/ Class？

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

推荐答案

在Dapper或其扩展中，没有针对您需求的即用型解决方案。 Dapper分别映射结果集中的每一行。因此，您需要一些额外的映射才能执行所需的操作。您可以在获得具有多个 splitOn 的 Query 结果后手动进行此操作。或使用一些映射工具。请考虑使用此问题各种答案。适应您的情况的解决方案（使用 Slapper.Automapper 映射）将是：

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

这篇关于“多级”使用SplitOn映射Dapper的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！