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