Access result from first select in Dapper QueryMultiple (many to many)

Question

I'm trying to map a many to many relationship into a User with a list of Roles

I tried doing as this question Dapper Many-to-Many Query but it gave me multiple users with a single role each.

Instead I'm trying to use the QueryMultiple statement. My issue here is that I'm querying the user from an Email but need to query the Role from the user's Id. So I need to access the result of the first query statement to pass in the Id to the second. How can I do this?

        public async Task<User> GetUserByEmailAsync(string email, CancellationToken cancellationToken)
    {
        cancellationToken.ThrowIfCancellationRequested();

        using (var connection = new SqlConnection(_options.ConnectionString))
        {
            await connection.OpenAsync(cancellationToken);
            var results = await connection.QueryMultipleAsync(@"SELECT * FROM [User] WHERE Email = @email; " +
            "SELECT Id, Name, NormalizedName FROM [Role] JOIN [UserRole] ON [Role].Id = UserRole.RoleId" +
            " WHERE [UserRole].UserId = 2",  // <-- NEED TO INSERT USER ID DYNAMICALLY HERE
            new
            {
                email
            });

            var user = await results.ReadSingleAsync<User>();
            var roles = await results.ReadAsync<Role>();

            foreach (var role in roles)
            {
                user.Roles.Add(role);
            }

            return user;
        }
    }

Steve · Accepted Answer · 2019-04-14T17:20:24.923

Not able to fully test it at the moment but you can use this approach to fill all Roles for a specific user obtaining a single user as result.

public async Task<User> UserFromEmail(string email)
{
    Dictionary<int, User> result = new Dictionary<int, User>();

    string query = @"
            SELECT u.*, r.*
            FROM [User] u JOIN [UserRole] ur on u.UserId = ur.UserId
                          JOIN [Roles] r on ur.RoleId = r.Id
            WHERE u.Email = @email;";
    using (IDbConnection cnn = OpenConnection())
    {
        var users = await cnn.QueryAsync<User, Role, User>(query, (u, r) =>
        {
            // this lambda is called for each record retrieved by Dapper
            // receiving a user and a role created by Dapper from the record
            // and it is expected to return a user.
            // We look if the user passed in is already in the dictionary 
            // and add the role received to the roles list of that user
            if (!result.ContainsKey(u.UserId))
                result.Add(u.UserId, u);
            User working = result[u.UserId];
            working.roles.Add(r);
            return u;
        }, new { email }, splitOn: "RoleId");

        // Return the first element in the dictionary
        if (result.Values.Count > 0)
            return result.Values.First();
        else
            return null;
    }
}

I slightly tweaked your answer to `SELECT u.*, r.Id as RoleId...` to make it properly work with the splitOn property. Smart to use a dictionary with the lambda, thanks! — J.Kirk., Apr 14 '19 at 17:48
Ah, right, the PK for Roles is Id, In that case you could try to leave the original name and remove the splitOn parameter. Dapper defaults to Id field to divide records data — Steve, Apr 14 '19 at 18:43

Access result from first select in Dapper QueryMultiple (many to many)

1 Answers1