Querying Is A Business Concern: Sample
In a recent post, I said that I believe that querying is a business concern. I gave an example there, but I think that this one is a more appropriate for the point that I am trying to make.
The model is very simple, and is shown below. Please do not mention AzMan, I already know about it.
Given a user, I need to find out whatever is has a particular operation with a particular permission. The permissions enum is a bit flag enumeration, with the usual suspects in increasing order (1,2,4,8).
The really simple thing to would simply move across the object graph, something like:
bool hasPermission =
user.Groups.Exists(grp => grp.Roles
.Exists(role => role.Operations
.Exists(op => op.Permissions == requiredPermission)));
I use Linq because it looks cool, here. This is simple, but it is also extremely ineffiecnt. It is much better to let the database do what it is good at, and run a query to get the result back.
Here is my first attempt.
select op from Operation op, Group group
where op.Name = :operationName
and (op.Permissions & :requiredPermission) != 0
and :user in elements(group.Users)
and op.Role in elements(group.Roles)
It resulted in pretty good SQL, but the op.Role in elements(group.Roles) bothered me. That is easy to write, but in SQL that translated to:
and permissions1_.Role in (select role1_.Id from
Roles role1 where role1.Group = grp1_.Id)
Looking into the execution plan, I thought that I could do better, and I re-wrote the query to be a bit
select op from Group group
join group.Users user
join group.Roles role
join role.Operations op
where user = :user
and (op.Permissions & :requiredPermission) != 0
and op.Name = :operationName
This turned out to be much more efficent (two clustered index scans less).
Anyway, I am letting the technology sway me away from a architectual discussion. This query is sitting on an AuthorizationService, and I consider it a 100% business logic.