Linq conditional query on same table/object

Question

Hi I'm having a problem with getting a conditional query to work. I want all projects where project.Parent either is null or if it has a parent then it shouldn't be voided or closed.

My example will NOT bring back any projects where project.Parent == null.

We are using linq-to-nhibernate

var projects = (from project in this.Session.Query<Project>()
                where project.IsClosed == false
                   && project.IsVoided == false
                   && (project.Parent == null 
                   || (project.Parent.IsVoided == false
                       && project.Parent.IsClosed == false))
                select project).ToList();

Answer 1

That query won't work because inner joins are generated for the Parent property.

The easiest workaround is doing two queries and joining them client-side:

var projects = (from project in this.Session.Query<Project>()
                where project.IsClosed == false
                   && project.IsVoided == false
                   && project.Parent == null 
                select project)
               .AsEnumerable()
               .Concat(
               (from project in this.Session.Query<Project>()
                where project.IsClosed == false
                   && project.IsVoided == false
                   && project.Parent.IsVoided == false
                   && project.Parent.IsClosed == false
                select project))
               .ToList();

Answer 2

Doing the join client-side is not required:

var projects = (from project in this.Session.Query<Project>()
            where project.Parent == null || (project.IsClosed == false
               && project.IsVoided == false)
               && (project.Parent == null 
               || (project.Parent.IsVoided == false
                   && project.Parent.IsClosed == false))
            select project).ToList();

Answer 3

I'd suggest to fetch all project and to check what happens to projects that should be null. Without having any example data etc. I have to guess what is causing the problem. I'd say the project parents are initialized with some empty state.