Dapper Multi Mapping

Question

Dapper Multi Mapping

c# dapper

Question

I've got an object:

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

However, when I use Dapper to call the following query:

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])

I receive the make sure when using multi-mapping, make sure you use the splitOn attribute. Which I am, but I still receive the error. So I assume I have some type of usage error or syntaxtual error. The code that continually errors is as follows:

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");

Important Note: By default Dapper utilizes Id, which is why I renamed Code to Id, but even with Code or the rename I still receive a multi-mapping error.

Greg

Popular Answer

So, I found out the primary reason it failed. Dapper didn't like the splitOn parameter alternating between int and string. By forcing all of them to be unified it worked. The other item I noticed, is that if you have a column called Code for instance, which mirrors a primary key but SQL isn't set to a relational identifier it will also error.

After those were rectified it pulled no problem.

Greg

View more on Stack Overflow

Void Ray · Accepted Answer · 8/7/2017 9:15:13 PM

Looks like you just need to remove the 'Code' from splitOn: "Id, Code". Your query renames it to "Id".

Dapper also uses "Id" as a default, so no need to specify.

Dapper is able to split the returned row by making an assumption that your Id columns are named Id or id. If your primary key is different or you would like to split the row at a point other than Id, use the optional splitOn parameter.

Below is a quick test to verify:

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

Update to demo split by different fields and types

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

Dapper Multi Mapping

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator