I'm building a custom ORM using Dapper. I have the following tables:
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:
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?
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: