Geek asks and answers

NHibernate 3.2 Linq with correlated subquery

Can someone help with trying to execute the following SQL in Linq in NHibernate 3.2?

select act.Name from Activity act
where 1 = 
(
  select top 1 p.Allow
  from Permissions p inner join Operations o on p.OperationId = o.OperationId
  inner join Users u on p.UserId = u.UserId
  where p.EntitySecurityKey = act.ActivityId and o.Name = '/operation'
  and u.Name = 'user'
  order by p.Level desc, p.Allow asc
)

This works fine in SQL, but I just can't figure out how to do this using Linq.

+3
source share
1 answer

There is no need for a correlated subquery. All your external request does fetch EntitySecurityKey.Namewhen Allow == true. You can execute this logic with a simple statement ifafter your request.

private string GetEntitySecurityKeyNameIfAllowed(ISession session, string operationName, string userName)
{
    var result = session.Query<Permission>()
        .Where(p => p.Operation.Name == operationName
            && p.User.Name == userName)
        .OrderByDescending(p => p.Level)
        .ThenBy(p => p.Allow)
        .Select(p => new
        {
            p.Allow,
            p.EntitySecurityKey.Name
        })
        .FirstOrDefault();

    return result != null && result.Allow
        ? result.Name
        : null;
}
0
source

More articles:


All Articles