I have two models Vendors and Users. Each user may or may not have only one Vendor. Vendors may have several Users.

Models:

public class AppUser
{
    public int Id { get; set; }
    public string? FullName { get; set; }
    public string? Position { get; set; }
    public int StatusId { get; set; }
    public int VendorId { get; set; }
}


public class Vendor
{
    public int VendorId { get; set; }
    public string? VendorCode { get; set; }
    public string? VendorName { get; set; }
    public int StatusId { get; set; }
    public List<AppUser> CompanyUsers { get; set; }
}

SQL Tables:

I need to select all Vendors with related Users (if such exists). My query:

SELECT
    v.VendorId
   ,v.VendorCode
   ,v.VendorName
   ,v.Status StatusId
   ,(CASE
      WHEN v.Status = 0 THEN 'Draft'
      WHEN v.Status = 1 THEN 'Open'
      WHEN v.Status = 2 THEN 'Closed'
      WHEN v.Status = 3 THEN 'Blacklisted'
    END) StatusName
    ,au.Id
    ,au.FullName
    ,au.Position
    ,au.StatusId
   ,(CASE
      WHEN au.StatusId = 0 THEN 'Draft'
      WHEN au.StatusId = 1 THEN 'Open'
      WHEN au.StatusId = 2 THEN 'Closed'
    END) StatusName
  FROM Procurement.Vendors v
    LEFT JOIN Config.AppUser au
    ON v.VendorId = au.VendorId

and the result:

Because only Vendor with Id 20 has users, it appears 3 times, which is expected behavior. Now I want to use Dapper's splitOn function to map all users under vendor 20. I split by user's Id column.

public async Task<IEnumerable<Vendor>?> GetAllVendors(int businessUnitId)
{
    var currentUser = await _appUserService.GetCurrentUserAsync();

    var p = new DynamicParameters();
    p.Add("@UserId", currentUser.Id, DbType.Int32, ParameterDirection.Input);
    p.Add("@BusinessUnitId", businessUnitId, DbType.Int32, ParameterDirection.Input);

    using IDbConnection cn = new SqlConnection(_sqlDataAccess.ConnectionString);
    return await cn.QueryAsync<Vendor, AppUser, Vendor>("dbo.SP_ZZZTest",
        (vendor, user) =>
        {
            if (vendor.CompanyUsers == null && user != null) { vendor.CompanyUsers = new(); };
            if (user != null) { vendor.CompanyUsers.Add(user); };
            return vendor;
        },
        param: p,
        splitOn: "Id",
        commandType: CommandType.StoredProcedure);
}

And here is the result I get:

As a result, Dapper did not map Users under a single Vendor. But instead mapped each user as a List of users with a single item duplicating Vendor's data on 3 rows.

What did I wrong?

CodePudding user response：

Yes, this is a common "problem". But is really simple to solve once you understand the process behind the lambda call.

The lambda expression receives three records created by the query splitting the record in two at the point of the splitOn configuration, creating a Vendor and an AppUser.

But the Vendor instance received at the second/third call is not the same instance of the first/second call. Dapper doesn't have this kind of processing logic thus, the code above, adds the AppUser to three different instances of Vendor.

It is up to you to 'discover' that the Vendor received contains the same data of a previous call. But it easy to solve if there is some kind of unique key that identifies a Vendor (the PK of the record)

So this "problem" can be solved using a Dictionary where the key is the PK of the Vendor and you store each unique Vendor data passed by Dapper under that dictionary key. Then you could check if the Vendor data received is already in the Dictionary and use the dictionary instance to add the AppUser.

Dictionary<int, Vendor> result = new Dictionary<int, Vendor>();

.....

using IDbConnection cn = new SqlConnection(_sqlDataAccess.ConnectionString);
_ = await cn.QueryAsync<Vendor, AppUser, Vendor>("dbo.SP_ZZZTest",
    (vendor, user) =>
    {
        if(!result.ContainsKey(vendor.vendorId))
        {
            vendor.CompanyUsers = new();
            result.Add(vendor.vendorId, vendor);
        }
        Vendor current = result[vendor.vendorId];
        if (user != null) 
            current.CompanyUsers.Add(user); 
            
        return vendor;
    },
    param: p,
    splitOn: "Id",
    commandType: CommandType.StoredProcedure);

// You want to return the Vendors stored in the Values of the 
// Dictionary, not the Vendors returned by the QueryAsync call
return result.Values;