How to call parent child call using Dapper Stored Procedure

Question

Following is the object structure.

public class User{
   public string Name{get;set;}
   public IList<Address> Addresss {get;set;}
   ...
}

public class Addresss{
   public string Street {get;set;}
   ...
}

Using Dapper, how this can be written to retrieve User along with List Of Address and that's using Stored Procedure call.

Trying to call like, DbConnection.QueryAsync<User>("storedprocedure",param:null,commandType:CommandType.StoredProcedure)

Stored Procedure query is as, Select u.*,a.* from user u join address a on u.Id = a.UserId

Expected result as List Of Users where User.Address // should populate with list of associated address.

Answer 1

I assume that your stored procedure is something like this

SELECT u.Id, u.Name, a.UserId, a.Street FROM Users u JOIN Addresses a on u.Id = a.UserId

In this case you could call the stored procedure and set the elements of the Address list in this way

Dictionary<int, User> users = new Dictionary<int, User();
var result = connection.Query<User, Address, User>(spName, ((u, a) =>
{
    if (!users.ContainsKey(u.Id))
    {
        users.Add(u.Id, u);
        u.Addresses = new List<Address>();
    }
    User k = users[u.Id];
    k.Addresses.Add(a);
    return u;
}, splitOn:"UserId", commandType:CommandType.StoredProcedure);

So, what's happens here?. While Dapper processes the records returned by the SP it splits each record using the value of the splitOn parameter and builds two objects, the User (u) and the Address (a), finally passes these two objects to the lambda expression.
The lambda expression then checks if there is a user with that Id inside the dictionary and, if not, adds the User with its key and initializes the address list.
After the if, the lambda gets back the user from the dictionary and adds the address instance, finally returns the same User object received as input parameter.
When Dapper finishes to enumerate the results the internal IEnumerable is returned with the Address data in place.