NHibernate Filters on Left Outer Joins

Question

I am running into an issue with an NHibernate filter. It works great until I do a left outer join to the object.

For example, Deal references PurchaseItem, but PurchaseItem has a CompanyId filter applied.

If I want to query deal with a left join to PurchaseItem, the CompanyId filter is applied in the WHERE clause, causing nothing to return.

Is there a way to apply the filter at the join instead of at the where clause?

Answer 1

I'm looking for this exact problem with nullable optional fields

Note your OR will avoid using indexes so it may be slower.

UPDATE: I think I found the fix:

                // register filters
                var filterDef = new FilterDefinition(
                    "f_CurrentTenant",
                    "TenantId = :f_tenantId",
                    new Dictionary<string, IType> { { "f_tenantId", NHibernateUtil.Guid } },
                    **false**);
                config.AddFilterDefinition(filterDef);

When you create the filter, you can set the last parameter called "useManyToOne" = false. This will tell NH to not use the filter on the joins.

So far it seems it has worked :D

Answer 2

Fixed this by using this filter condition: "CompanyId is NULL or CompanyId = :companyId"

This is ok because CompanyId is not-null in SQLServer so we should only get NULL if we are doing an outer join.