oop - is this an elegant way to implement this association scenario in a custom orm? -

i'm building custom orm using dapper. have following tables:

• user
• role
• permission
• rolepermission

my sproc has 2 selects: 1 basic user info, 1 user-role info. basic user info gets deserialized user object. rolepermission info gets deserialized custom rolepermission object , programatically associated user object. how complex make object graph implementation in custom orm? example, ef takes nice , easy oo approach role name this: user.userroles.role.name. however, accomplished through use of complex , sophisticated f/w automatically maps entity joins based on db fk associations. comes performance overhead. thought create entity classes follows:

• mycompany.entities.entity.user.user
• mycompany.entities.entity.user.rolepermission

therefore, rolepermission object tailored user entity no external dependencies. keep rolepermission object lightweight possible in context of user object. not need additional properties support other entities/domains/usages. seems elegant (easy, effective, efficient) solution. thoughts on type of approach creating complex object in custom orm?

i sort of thing time , it's quite easy. you'll in 2 queries. 2 queries can in same sproc , return different resultsets or can 2 separate calls database. typically latter though use mssql allows multiple resultsets returned.

so first off: you're querying users (shallow) , optionally details of user including role information (deep).

public ienumerable<user> getusers(int? userid, bool withdetails)         {             var users = db.query<user>(@"   select *                                              dbo.users u                                              (@userid null or u.id = @userid)", new { userid });              if (withdetails)             {                 var rolepermissions = db.query<rolepermission>(@"   select *                                                                      dbo.rolepermissions rp                                                                      rp.userid in (select val dbo.udf_convertintlisttotable(@userid_list))", new { userid_list = string.join(',', users.select(s => s.userid)) });                 foreach(var user in users)                 {                     user.rolepermissions = rolepermissions.where(w => w.userid == user.userid);                 }             }         } 

a few notes:

• dbo.udf_convertintlisttotable established , performs great. search web , you'll find it. i'll load gist if can't find it.
• rolepermission needs have reference outer user (i used userid). also, query map additional data mentioned (e.g. role, permission, etc.)
• i have tons of extensions tidy above. kept raw possible can see what's going on.
• you have light query (withdetails=false) lists , such, , heavier query data nicely wrapped aggregate root (user).