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

Question

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

dapper data-access-layer oop orm

Question

I'm building a custom ORM using Dapper. I have the following tables:

User
Role
Permission
RolePermission

My sproc has 2 selects: 1 for basic user info, 1 for user-role info. The basic user info gets deserialized into a user object. The RolePermission info gets deserialized into a custom RolePermission object and then programatically associated to the User object. How complex would you make the object graph implementation in a custom ORM? For example, EF takes a nice and easy OO approach to get role name like this: User.UserRoles.Role.Name. However, this is accomplished through the use of a complex and sophisticated f/w which automatically maps the entity joins based on db fk associations. It also comes with performance overhead. My thought is to create my entity classes as follows:

MyCompany.Entities.Entity.User.User
MyCompany.Entities.Entity.User.RolePermission

Therefore, my RolePermission object would be completely tailored to the User entity with no external dependencies. This would keep the RolePermission object as lightweight as possible in the context of the User object. It would not need any additional properties to support other entities/domains/usages. This seems like an elegant (easy, effective, efficient) solution. What are your thoughts on this type of approach for creating a complex object in a custom ORM?

user6604655

View more on Stack Overflow

BlackjacketMack · Accepted Answer · 8/24/2016 4:33:41 PM

I do this sort of thing all the time and it's really quite easy. You'll do it in two queries. The two queries can be in the same sproc and return different resultsets or can be two separate calls to the database. I typically do the latter even though we use mssql which allows multiple resultsets returned.

So first off: you're querying Users (shallow) and optionally details of a User including role information (deep).

public IEnumerable<User> GetUsers(int? userID, bool withDetails)
        {
            var users = db.Query<User>(@"   select * 
                                            from dbo.Users u 
                                            where (@userID IS NULL OR u.Id = @userID)", new { userID });

            if (withDetails)
            {
                var rolePermissions = db.Query<RolePermission>(@"   select * 
                                                                    from dbo.RolePermissions rp 
                                                                    where rp.UserId IN (select val from 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 is very well established and performs great. Search the web and you'll find it. I'll load it up as a gist if you can't find it.
RolePermission needs to have a reference to the outer User (I used UserId). Also, that query will likely map the additional data you mentioned (e.g. Role, Permission, etc.)
I have tons of extensions that tidy up the above. I kept it as raw as possible so you can see what's going on.
You now have a very light query (withDetails=false) for lists and such, and a heavier query with all the data nicely wrapped up into its aggregate root (User).

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

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator