Dapper splitOn and query not returning entire data from database into mapped objects

Question

I am trying to retrieve some result from the database using Dapper QueryAsync method and multi-mapping to return the data to a class. I query data from tables A, B and C and return result into class Result. Below is what I currently have: Querymethod

var queryReturn = (await connection.QueryAsync<A, B, C, Result>(
    query,
    (a, b, c) => map.BuildResultQueryReturn(a, b, c),
    splitOn: nameof(a.CodePlan) + "," + 
             nameof(c.CodePlanCouverture) + "," +
             nameof(b.CodePlanNum) + "," + 
             nameof(a.CodePlanNum))).ToList();

BuildResultQueryReturn Method where I do the mapping:

public Result BuildResultQueryReturn(A a, B b, C c)
{
    return new Result
    {
        LinkedBenefitGroupRule = _dataFormatter.GetEnum<XTypeEnum>(b.RegleGroupeGarantieLiee),
        AdditionalBenefitPlanCode = c.CodePlanGarantieLiee,
        AdditionalBenefitType = _dataFormatter.GetEnum<YTypeEnum>(c.TypeAlis),
        MaxIssueAge = c.AgeMaxEmission,
        ParentPlanCode = c.CodePlanCouverture
    };
}

When I check the variables being mapped, I have values for all of them except the 'b.RegleGroupeGarantieLiee' and when I check in the object 'b', the only returned value from the database I can see is CodePlanNum. 'RegleGroupeGarantieLiee' appears indeed null.

This is the SQL Query I'm running (which by the way gives correct values for every field in SQL Server db including 'RegleGroupeGarantieLiee' which is of type smallInt):

"Select A.CodePlan, C.CodePlanCouverture, B.CodePlanNum, A.CodePlanNum, C.CodePlanCouverture, C.TypeAlis, C.AgeMaxEmission, C.CodePlanGarantieLiee, B.RegleGroupeGarantieLiee " +
            $" FROM {nameof(C)}" +
            $" JOIN {nameof(A)} ON {nameof(A)}.CodePlan = {nameof(C)}.CodePlanCouverture" +
            $" JOIN {B)} ON {B)}.CodePlanNum = {A}.CodePlanNum " +
            $" WHERE {predicate}";

I'm suspecting the issue might be with the splitOn parameter of QueryAsync, but I'm not sure, since the query returns values for object C (typeAlis, AgeMaxEmission, ...). Don't know how to resolve this or where the problem lies.

After researching here, I found a somewhat similar post (Dapper doesn't fill entity, even with the right `splitOn`?), but the answer didn't work for me.

Answer 1

The select columns need to be in table order, the splitOn works as a pair of scissors cutting the result rows into chunks that belong in the different classes. You also only need your join columns once, so your query should be this:

"Select A.CodePlan, A.CodePlanNum, B.RegleGroupeGarantieLiee, C.CodePlanCouverture, C.TypeAlis, C.AgeMaxEmission, C.CodePlanGarantieLiee " +
            $" FROM {nameof(C)}" +
            $" JOIN {nameof(A)} ON {nameof(A)}.CodePlan = {nameof(C)}.CodePlanCouverture" +
            $" JOIN {B)} ON {B)}.CodePlanNum = {A}.CodePlanNum " +
            $" WHERE {predicate}";

To cut your result row in three pieces you only need to cut twice, so the splitOn should be this:

splitOn: nameof(B.RegleGroupeGarantieLiee) + "," + 
             nameof(c.CodePlanCouverture))).ToList();

Now the splitOn cuts into tables/classes.