Dapper multi-mapping error one-to-many query

Question

I am using Dapper to perform many of my queries in my application. I am currently getting an error performing a query against two table in a 1-to-many relationship. I get this error:

Multi-map error: splitOn column 'PortalId' was not found

Here is my query

public async Task<Result<PortalResponse?, Error>> Handle(GetEmailAddressesQuery request, CancellationToken cancellationToken)
{
    var parameters = new DynamicParameters();
    parameters.Add("@p_tenantid", request.TenantId);

    const string sql = """
        SELECT em.PortalId, em.Email
        FROM dbo.Portal p 
        JOIN dbo.PortalEmailAddress em ON p.Id = em.PortalId
        WHERE p.TenantId = @p_tenantid
    """;

    using var connection = _dapperContext.CreateConnection();

    var response = await connection.QueryAsync<PortalResponse, EmailAddressResponse, PortalResponse>
        (new CommandDefinition(sql, parameters, cancellationToken: cancellationToken),
          (portal, emailAddress) =>
          {
              portal.EmailAddresses.Add(emailAddress);
              return portal;
          }, splitOn: "PortalId");

    if (response is null)
    {
        return PortalErrors.PortalNotFound(request.TenantId);
    }

    return response.FirstOrDefault();
}

These are my response classes

public sealed class PortalResponse : IResponse
{
    public int Id { get; init; }
    public string Name { get; init; } = string.Empty;
    public List<EmailAddressResponse> EmailAddresses { get; set; } = [];
    public List<PhoneNumberResponse> PhoneNumbers { get; set; } = [];
    public AddressResponse? Address { get; set; }
    public string? WelcomeMessage { get; init; }
    public string? Logo { get; init; };
}

public sealed class EmailAddressResponse
{
    public int PortalId { get; init; }
    public string Email { get; init; } = string.Empty;
}

I thought I had this setup correctly, but alas I do not. Any help appreciated.

Answer 1

You need to change your query to add also the fields from the Portal class. Otherwise there is nothing to split on and create the two class instances specified in the QueryAsync.

const string sql = @"
    SELECT p.Id, p.Name, em.PortalId, em.Email
    FROM dbo.Portal p 
    JOIN dbo.PortalEmailAddress em ON p.Id = em.PortalId
    WHERE p.TenantId = @p_tenantid";

Of course you could add also the other fields from the Portal class that map to your database columns.

By the way, if a Portal has more than one EMailAddressResponse this code will not work as you expect. This code will return more than one instance of the same Portal following exactly the database output in a one to many relationship.