Dapper multiple objects from one row

Question

I have one row coming from the database

select "John" Name, 
       "Male" Gender,
       20 Age,
       "Rex" PetName,
       "Male" PetGender,
       5 PetAge
       // ... many more ...

Using Dapper, I'd like to pull this row into two objects:

class Person
{
    public string Name { get; set; }
    public string Gender { get; set; }
    public int Age { get; set; }
    // ... many more ...
}    
class Pet
{
    public string PetName { get; set; }
    public string PetGender { get; set; }
    public int PetAge { get; set; }
    // ... many more ...
}

Note: there is no hierarchical relationship here, I'm simply trying to map one database row into two (or more) objects.

How can I do this using dapper?

I know I can do this by returning a dynamic and mapping each object manually, which is painful as in my scenario we have a large number of columns. I'd rather not do this. (And no, it can't be redesigned to require less columns.)

What I've tried:

I've looked into QueryMultiple<Person,Pet>, but it assumes I am running multiple queries. In my real-life scenario, this is a very expensive query, and I'd like to just run it once.
I've also looked into returning Query<Person,Pet,Tuple<Person,Pet>>, but this requires an Id column, here there's no hierearchical relationship or Ids. I just want to take a single row and map it to multiple columns.

Eren Ersönmez · Accepted Answer · 2013-07-19 21:07:56Z

22

You were pretty close to solution with the Query method. If you don't have an Id column, then you can provide a splitOn argument:

connection.Query<Person, Pet, Tuple<Person, Pet>>(sql, 
    (person, pet) => Tuple.Create(person, pet), splitOn: "PetName");

answered Jul 19, 2013 at 21:07

Eren Ersönmez

39.2k8 gold badges76 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Jay Sullivan Over a year ago

Am I missing something? Even when I supply splitOn, I still get

ArgumentException: When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id. Parameter name: splitOn

Eren Ersönmez Over a year ago

that's odd. you might have to show us the exact sql query and the code. not likely but maybe your splitOn field exists in the first type as well, and you mean to include it in the second type, by any chance?

Jay Sullivan Over a year ago

You're right, when I use it with this toy scenario, it works fine. I'll have to dig in and figure out where my stuff is going wrong in my real code.

Eren Ersönmez Over a year ago

the logic is pretty simple really. It looks at the columns sequentially. In your example query, if you say splitOn="PetName", it will use the first 3 columns to build the Person, and the rest to build the Pet. If Person has more properties/fields that are not in the first 3 columns, then those props will remain at their default value(e.g null).

Eren Ersönmez Over a year ago

The best documentation, in my opinion, is the Tests.cs.

|

Collectives™ on Stack Overflow

Dapper multiple objects from one row

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related