CODEWITHSUNDEEP
c#linqnhibernate
Sam
Sam

Reputation: 15770

NHibernate LINQ Where and Any throwing exception

I have the following code that is throwing a "Specified method is not supported" error:

var list = securityRepository.Query<IsAuthorized>().Where(
    ia => ia.SecurableObjectName == objectName
    && ia.PermittedActionName == permittedActionName
    && ia.ScopeName == scopeName).ToList();

var prins = repository.Query<Principal>().Where(
    p => list.Any(l => l.PrincipalId == p.Id)).ToList();

The second query with the Any is what is throwing.

I need to select a list of Principal whose id's are in the list<IsAuthorized>.

Upvotes: 2

Views: 1760

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

I would say, that the best what we can do with that type of query, is to move it to DB Server. Of course, only in case, that this is possible, that the first query is not returning results from external source. Then we have to use a bit different technique.. but let's assume, that both tables are inside the DB.

The second query, would be the OUTER Select, the first will be the INNER Select. To combine them, we will use the IN statement.

What we have to do is:

Firstly, let's adjust the first query, to return the One property/column, to be used later:

var list = securityRepository
    .Query<IsAuthorized>()
    .Where(
      ia => ia.SecurableObjectName == objectName
      && ia.PermittedActionName == permittedActionName
      && ia.ScopeName == scopeName)
    // .ToList();
    .Select(ia => ia.PrincipalId);

So, what we've done, is deferring the query execution (no ToList() call). Instead, we created a SELECT clause with projection containing only one property/column.

Let's adjust what we have and need for the second query:

var prins = repository
    .Query<Principal>()
    //.Where(p => list.Any(l => l.PrincipalId == p.Id))
    .Where(p => list.Contains(p.Id)) // our subQuery
    .ToList();

And this, will produce something like this:

SELECT ... 
 FROM Pincipal p
 WHERE p.Id IN (SELECT ia.PrincipalId FROM IsAuthorized ia......

Upvotes: 3

Related Questions