0

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.

Cod29
  • 265
  • 4
  • 14
  • You just need to put the stored procedure name in place of the query text, then set the commandType parameter to CommandType.StoredProcedure – Steve Feb 27 '20 at 21:26
  • So you write a stored procedure and [call it from Dapper](https://stackoverflow.com/questions/5962117/is-there-a-way-to-call-a-stored-procedure-with-dapper)? Am I missing something? – stuartd Feb 27 '20 at 21:26
  • How can I have mapping of User and Addresses ? – Cod29 Feb 27 '20 at 21:29
  • From above given link, it can be done using `cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure); ` but, how to get mapping of User and Address ? Is it manually iteration and mapping with ? – Cod29 Feb 27 '20 at 21:40
  • Could you show the code of your stored procedure? How the two datatable are related? There is some kind of PK FK relation like an UserId on both tables to define which addresses belong to a specific user? – Steve Feb 27 '20 at 21:44
  • Updated question with stored procedure sql. Yes, User is primary and Address as FK of User Id. – Cod29 Feb 27 '20 at 21:48
  • As @Steve said originally, _"set the commandType parameter to CommandType.StoredProcedure"_? – stuartd Feb 27 '20 at 21:52
  • yes. added more comments on question. – Cod29 Feb 27 '20 at 21:55

2 Answers2

4

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.

Steve
  • 213,761
  • 22
  • 232
  • 286
1

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

JuanD
  • 41
  • 4