CODEWITHSUNDEEP
c#dapper
Cod29
Cod29

Reputation: 295

How to call parent child call using Dapper Stored Procedure

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.

Upvotes: 0

Views: 915

Answers (2)

Steve
Steve

Reputation: 216303

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.

Upvotes: 4

JuanD
JuanD

Reputation: 41

If you expect just 1 user maybe another approach could be using QueryMultiple which the stored procedure must return 2 DataTables, the first with the user info and the second with the user addresses.

using (SqlConnection conn = new SqlConnection(this.DbContext.CadenaConexion))
{
    using (var results = conn.QueryMultiple($"EXEC MyStoredProcedure @userId=@userId", new { userId= 123 }))
    {
        User user = results.ReadFirst<User>();
        user.Addresses = results.Read<Address>().ToList();
        
        return user;
    }
}

Stored procedure query like :

SELECT * FROM dbo.User WHERE Id = @userid
SELECT * FROM dbo.Address WHERE UserId = @userid

PS: I didn't run the code, it should not vary too much

Upvotes: 1

Related Questions