How with Dapper I can get values for child objects?

c#-4.0 dapper orm

Question

I'm obtaining profile information using the following:

var profiles = connection.Query<Models.PROFILE>(
    "SELECT * FROM PROFILES WHERE ID=@ID", 
    new { ID = profileID }); // IEnumerable
var profile = profiles.First<Models.PROFILE>();

Other collections, such as profileImages, are included in the profile object. The issue is that every child object has a zero item count. Additionally, I just want data for, let's say, profileImages.

Is there a setting that has to be made in order to query the child objects, and if so, how many layers may it be specified for?

Additionally, I've tried multi-mapping:

var profiles = connection.Query<Models.PHOTOS_PERMISSIONS,
                                Models.PROFILE,
                                Models.PHOTOS_PERMISSIONS>(sql,
                    (p1, p2) => { p1.ID = profileID; return p1; }, 
                    new { ID = profileID }, 
                    splitOn: "OWNER_PROFILESIDFK, ID").AsQueryable();

PHOTOS_PERMISSIONS.OWNER_PROFILESIDFK = PROFILE.ID

and experiencing the subsequent error:

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

I've tried modifying my splitOn wording, but I continue to see the same problem.

1
6
2/28/2019 11:00:04 PM

Accepted Answer

This kind of One-To-Many mapping is not supported by Dapper out of the box. Check out this query; it could be useful.

One-to-many, multi-mapping

You may do two queries and utilize the GridReader if your PROFILEIMAGES table contains an FK on PROFILES ID.

var sql = 
@"
select * from PROFILES where profileId= @id
select * from PROFILEIMAGES where OWNER_PROFILESIDFK = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
   var profile = multi.Read<Models.PROFILE>().Single();
   profile.ProfileImages = multi.Read<Model.PROFILEIMAGES>().ToList();
} 
8
5/23/2017 10:27:49 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow