NHibernate 3.2 Linq with correlated subquery

Question

Can anyone help with trying to do the following SQL in Linq to 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 beautifully in SQL but I just cannot fathom how to do the equivalent using Linq.

Did you have any luck with this? I'm trying to do pretty much the same thing. I have the Linq statement figured out and working with LinqPad but I can't get NHibernate to execute it. See http://stackoverflow.com/questions/15206860/nhibernate-subquery-in-where-with-linq — Ragesh, Mar 04 '13 at 17:01

score 0 · Answer 1 · answered Jul 29 '13 at 16:05

There is no need for a correlated sub-query here. All your outer query does is fetch EntitySecurityKey.Name when Allow == true. You can perform that logic with a simple if statement after your query.

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;
}

NHibernate 3.2 Linq with correlated subquery

1 Answers1

Linked