How do you include an object that has a generic list property and a reference type property in dapper?

Question

my class is:

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Address> Addresses { get; set; }
    public BankAccount Account { get; set; }
}

public class Address
{
     public string Street { get; set; }
     public string City { get; set; }
     public string Zipcode { get; set; }
} 

public class BankAccount
{
      public decimal Balance {get; set;} 
      public DateTime LastDateWithdrawn { get; set;}
}

its not possible with multimapping or the multiple results i already tried. How do you do that in dapper? what I want to achieve is not to make roundtrips to the database getting the addresses and the bank account if I could use multi-mapping or multiple result or any other options.

By looking at your class, its hard to help you. Show us what you have done so far. What exactly you want to achieve ?? — Krishnraj Rana, Jul 17 '14 at 14:43
Okay, it means you want to get multiple results set and bind it to **Person** class right ?? — Krishnraj Rana, Jul 17 '14 at 14:47
@KrishnrajRana im trying to get the Person object from the database with its addresses and bank account with minimal or no roundtrips possible. — John Patrick Po, Jul 17 '14 at 14:47
@KrishnrajRana yes but in order for me to get the addresses and bank account (both which have foreign key: PersonId) i have to get the Person object first and use its Id property. The Sample in dapper dot net website is a predetermined id of customer, orders..etc — John Patrick Po, Jul 17 '14 at 14:49
See [this](http://stackoverflow.com/questions/6379155/multi-mapper-to-create-object-hierarchy#6380756) question. Dapper doesn't appear to be all that great at handling one to many relationships. The alternative seem to be a stored procedure which pulls back the addresses to the depth desired. — m.casey, Jul 17 '14 at 14:50
@m.casey Thanks! I could try some of the codes here.. looks good.. — John Patrick Po, Jul 17 '14 at 14:55

score 2 · Accepted Answer · answered Jul 17 '14 at 15:00

By read your comment, i have tried to give you answer.

Let say here you have one SP.

Create PROCEDURE [dbo].[GetPersonDetail]
(
    @PersonId int
)
AS  
SET NOCOUNT ON
BEGIN

    -- 1). Get Person detail
    Select * from PersonMaster Where PersonId = @PersonId


    -- 2). Get Person Addrss
    Select * from PersonAddress Where PersonId = @PersonId

    -- 1). Get Person BankAccount
    Select * from BankAccount Where PersonId = @PersonId

End

and here is your dapper method which uses QueryMultiple method of Dapper.

public Person GetPersonDetail(int PersonId)
{
    try
    {
        var oPara = new DynamicParameters();
        oPara.Add("@PersonId", PersonId, dbType: DbType.Int);

        var person = new Person();
        using (var multiResults = _connection.QueryMultiple(GetPersonDetail, oPara, commandType: CommandType.StoredProcedure))
        {
            person.Person = multiResults.Read<Person>().FirstOrDefault();
            person.Addresses = multiResults.Read<Address>();
            person.BankAccount = multiResults.Read<BankAccount>().FirstOrDefault();
        }
        return person;
    }
    catch (Exception ex)
    {
        thow;
    }
}

Hope this may help you... All the best.

I did but im going to modify this to just a sql query i dont want any SPs in my db.. but thanks! — John Patrick Po, Jul 17 '14 at 15:43

How do you include an object that has a generic list property and a reference type property in dapper?

1 Answers1