Map with Dapper one table to a nested object

Question

I have a table which has the following columns:

And I have the following C# classes

    public class User
{
    public int UserId { get; set; }
    public string UserName { get; set; }
    public Account Account { get; set; }
}

public class Account
{
    public string SortCode { get; set; }
    public string AccountNumber { get; set; }
}

I need to map the SortCode column to the Account.SortCode object. The same with the AccountNumber.

I was trying something like this

 const string query = @"SELECT UserId, UserName, SortCode AS [Account.SortCode], AccountNumber AS [Account.AccountNumber], FROM [dbo].[Users];";

 return db.Query<User>(query);

But is is not working. Any help?.

Thanks!!

Dapper doesn't try to understand the relationships between objects. You have to tell it how to relate them. Look at the Dapper documentation about "Multi Mapping". — , Jun 06 '19 at 14:24

score 0 · Answer 1 · answered Jun 07 '19 at 10:20

Following @Amy's comment, this did the trick:

    const string query = @"SELECT * FROM [dbo].[Users];";

    return db.Query<User, Account, User>(query, (user, account) => 
    {
       user.Account = account;
       return user;
    }, splitOn: "UserId");

Thanks!

Map with Dapper one table to a nested object

1 Answers1