using Dapper the way EF Core does it wrongly returns what SQL does

Question

Entities:

public record ProductAttribute
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string? Link { get; set; }
    public AttributeType Type { get; set; }
    public List<ProductAttributeValues> AttributeValues { get; set; } = new();
}

public record ProductAttributeValues
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public string? value { get; set; }
    public int ProductAttributeId { get; set; }

    public ProductAttribute ProductAttribute { get; set; } = new();
}

I am trying to use Dapper to get ProductAttributes with its ProductAttributeValues in it the way EF Core does it with .Include().

SQL:

select pa.Id,pa.Name,pa.link,pa.Type,pav.Id as AttributeId,pav.Name,pav.value,pav.ProductAttributeId
from productattributes pa
inner join productattributevalues pav
on pav.ProductattributeId = pa.Id 

C#:

var attributeResult = await connection.QueryAsync
<ProductAttribute, ProductAttributeValues, ProductAttribute>(sql, (attributes, attributevalues) => {
attributes.AttributeValues.Add(attributevalues);
return attributes;
}, splitOn: "AttributeId");

For each attributevalue in ProductAttribute, one row is returned:

It should return two Attributes and in one of them 3 attrValues. It returns the same as the SQL:

Answer 1

I agree with Chris Shaller, the EF became a very powerful tool in terms of performance.

But to follow on your approach, you have to group your results:

    var result = attributeResult.GroupBy(p => p.Id).Select(g =>
    {
        var productAttribute = g.First();
        productAttribute.AttributeValues = g.Select(p => p.AttributeValues).ToList();
        return productAttribute;
    });

Here is the documentation: https://www.learndapper.com/relationships