Complex queries in linq to nhibernate

Question

Hi,

We are using accountability pattern for organizational structure. I using linq to nhibernate to find some departments and position but I have two problem.

var query = 
  Repository<Party>.Find(p => p.IsInternal == true)
    .Where(p => p.Parents.Any(c => c.Parent.PartyId == id))
    .Where(p => 
      (
        p.PartyType.PartyTypeId == (int)PartyTypeDbId.Department && 
        _secretariat.Departments.Select(c => c.PartyId).Contains(p.PartyId)
      )
      || 
      (
        p.PartyType.PartyTypeId == (int)PartyTypeDbId.Position && 
        p.Children.Any(c => 
          c.AccountabilityType.AccountabilityTypeId == (int)AccountabilityTypeDbId.TenurePersonOfPosition &&  
          ((Person)c.Child).UserName != null)
        )
    );

First : I got 'Unhandled Expression Type: 1003' for this part of query : '_secretariat.Departments.Select(c => c.PartyId).Contains(p.PartyId)' and I got Property not found 'UserName'

We have many complex queries i think we need to use stored procedure.

Sorry for bad Inglish!

Answer 1

One nice thing that you can do with LINQ is break your queries into multiple parts. Since you are building an expression tree that won't get executed until the results are enumerated, you don't have to do it all in one line (like SQL).

You can even make some reusable "filters" that you can apply to IQueryable. These filter functions accept an IQueryable as an argument, and return one as a result. You can build these as extension methods if you like (I like to).

As for your immediate problem, you may want to try a join on _secretariat instead of attempting a subquery. I've seen them work in scenarios where subqueries don't.

Answer 2

In addition to Lance's comments you might want to look at a compiled Linq query and breaking up some of the responsibilties to follow SOLID principles.