Dapper多映射

Question

我有个目标：

public class Species : IEntity<int>
{
     public int Id { get; set; }
     public string Name { get; set; }
     public SpeciesCategory Category { get; set; }
     public WetlandIndicator WetlandIndicator { get; set; }
}

public class SpeciesCategory : IEntity<int>
{
     public int Id { get; set; }
     public string Name { get; set; }
}

public class WetlandIndicator : IEntity<string>
{
     public string Id { get; set; }
     public string Designation { get; set; }
     public bool Status { get; set; }
}

但是，当我使用Dapper调用以下查询时：

SELECT 
    [Species].*, 
    [SpeciesType].*, 
    [WetlandIndicator].Code AS Id, 
    [WetlandIndicator].Designation 
FROM 
    ((([Watershed].[Vegetation].[Species] INNER JOIN [Vegetation].[SpeciesCategory] 
        ON [Watershed].[Vegetation].[Species].[SpeciesCategoryId] = [Vegetation].[SpeciesCategory].[Id]) INNER JOIN [Watershed].[Vegetation].[SpeciesType] 
        ON [Watershed].[Vegetation].[Species].[SpeciesTypeId] = [Vegetation].[SpeciesType].[Id]) INNER JOIN [Watershed].[Vegetation].[WetlandIndicator] 
        ON [Vegetation].[Species].[WetlandIndicatorCode] = [Vegetation].[WetlandIndicator].[Code])

在使用多个映射时，我会收到“确保”，请确保使用splitOn属性。我是，但我仍然收到错误。因此，我假设我有某种类型的用法错误或语法错误。不断出错的代码如下所示：

public async Task<IEnumerable<SpeciesDomain>> GetAllSpecies(string query) =>
        await dbConnection.QueryAsync<Species, SpeciesCategory, WetlandIndicator, SpeciesDomain>(query, (species, speciesCategory, wetlandIndicator) =>
        {
            species.SpeciesCategory = speciesCategory;
            species.WetlandIndicator = wetlandIndicator;
            return species;
        }, splitOn: "Id, Code");

注意:默认情况下，Dapper使用Id，这就是我将代码重命名为Id的原因，但是即使使用Code或重命名，我仍然会收到多个映射错误。

Answer 1

看起来您只需要从splitOn中删除'Code‘："Id，Code“。您的查询将其重命名为"Id“。

Dapper还使用"Id“作为缺省值，因此不需要指定。

Dapper可以通过假设您的id列名为Id或Id来拆分返回的行。如果主键不同，或者希望在Id以外的点拆分行，请使用可选的splitOn参数。

下面是验证的快速测试：

using (var conn = new SqlConnection(@"Data Source=.\sqlexpress;Integrated Security=true; Initial Catalog=foo"))
{
    var result = conn.Query<Species, SpeciesCategory, WetlandIndicator, Species>(
        "select Id = 11, Name = 'Foo', Id = 22, Name = 'Bar', Id = 33, Designation = 'House Cat' ", 
        (species, speciesCategory, wetlandIndicator) =>
    {
        species.Category = speciesCategory;
        species.WetlandIndicator = wetlandIndicator;
        return species;
    }).First();

    Assert.That(result.Id, Is.EqualTo(11));

    Assert.That(result.Category.Id, Is.EqualTo(22));
    Assert.That(result.Category.Name, Is.EqualTo("Bar"));

    Assert.That(result.WetlandIndicator.Id, Is.EqualTo(33));
    Assert.That(result.WetlandIndicator.Designation, Is.EqualTo("House Cat"));
}

更新以演示按不同字段和类型划分的

public class Species
{
    public int Id { get; set; }
    public string Name { get; set; }
    public SpeciesCategory Category { get; set; }
    public WetlandIndicator WetlandIndicator { get; set; }
}

public class SpeciesCategory
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class WetlandIndicator
{
    public string Code { get; set; }
    public string Designation { get; set; }
    public bool Status { get; set; }
}

using (var conn = new SqlConnection(@"Data Source=.\sqlexpress;Integrated Security=true; Initial Catalog=foo"))
{
    var result = conn.Query<Species, SpeciesCategory, WetlandIndicator, Species>(
        "select Id = 11, Name = 'Foo', Id = 22, Name = 'Bar', Code = 'X', Designation = 'House Cat' ", 
        (species, speciesCategory, wetlandIndicator) =>
    {
        species.Category = speciesCategory;
        species.WetlandIndicator = wetlandIndicator;
        return species;
    }, splitOn: "Id, Code").First();

    Assert.That(result.Id, Is.EqualTo(11));

    Assert.That(result.Category.Id, Is.EqualTo(22));
    Assert.That(result.Category.Name, Is.EqualTo("Bar"));

    Assert.That(result.WetlandIndicator.Code, Is.EqualTo("X"));
    Assert.That(result.WetlandIndicator.Designation, Is.EqualTo("House Cat"));
}

Answer 2

所以，我发现了失败的主要原因。Dapper不喜欢splitOn参数在int和string之间交替使用。通过强迫所有这些都统一起来，它起了作用。我注意到的另一项内容是，如果您有一个名为Code的列，它反映了一个主键，但是SQL没有设置为关系标识符，它也会出错。

在这些问题得到纠正后，就没有问题了。