Conditionally allowing for null in LINQ WHERE clause

Question

Working:

I am trying to perform a LEFT OUTER JOIN between my Contacts table and my Permissions table. I have the basis of this working correctly, and get back a list of contacts regardless of whether they have a corresponding permission.

// Query for Contacts
from contact in Contacts
join permission in Permissions on contact.Id equals permission.ObjectId into permissionGrp
from p in permissionGrp.DefaultIfEmpty()            
where p==null || (p!=null && /* ... condition based on the existence of a permission */)
select new { contact, permission = p };

Generated WHERE SQL:

WHERE
(t1.PermissionId IS NULL OR 
    ((t1.PermissionId IS NOT NULL AND ... other conditions ... )))

Problem:

I want to adapt the above to introduce 'fallback' check; Not working as expected.

Requirement:

• When there is no Permission corresponding to the Contact (i.e. p==null) then only include the row based on the predetermined bool value of allowed.

Attempt:

I thought I could do where (p==null && allowed) || ... like this:

// Fallback permission
bool allowed = false;

// Query for Contacts
from contact in Contacts
join permission in Permissions on contact.Id equals permission.ObjectId into permissionGrp
from p in permissionGrp.DefaultIfEmpty()

/* Added bool condition 'allowed' to control inclusion when 'p' is null */
where (p==null && allowed) || (p!=null && /* ... condition based on the existence of a permission */)
select new { contact, permission = p };

Expected:

When allowed = false (don't accept null permission)

WHERE
    ((t1.PermissionId IS NOT NULL AND ... other conditions ... ))

When allowed = true (accept null permission)

WHERE
(t1.PermissionId IS NULL OR 
    ((t1.PermissionId IS NOT NULL AND ... other conditions ... )))

Actual Result:

Always outputs as if allowed=false even when true?

WHERE
    ((t1.PermissionId IS NOT NULL AND ... other conditions ... ))

Summary:

I hope I am just doing something silly that's easily fixed.
How can I filter my null value records based on a given bool value?

Answer 1

You are performing a GroupJoin here. So the result of the first part, permissionGrp, is an anonymous type IGrouping<Permission>. This already is the eqivalent of an OUTER JOIN.

You can achieve what you want by conditionally testing whether or not the IGrouping<Permission> contains elements:

from contact in Contacts
join permission in Permissions on contact.Id equals permission.ObjectId
    into permissionGrp
where allowed || g.Any()
from p in permissionGrp.DefaultIfEmpty()
select new { contact, permission = p };

Note that from p in permissionGrp flattens the grouping again, so .DefaultIfEmpty() is still necessary for the case where allowed == true.

Answer 2

I suspect there is a bug in the ORM (LightSpeed) I am using, which I will bring to their attention.

Workaround

I have found a suitable work around, using the let clause.

// Fallback permission
bool allowed = false;

// Query for Contacts
from contact in Contacts
join permission in Permissions on contact.Id equals permission.ObjectId into permissionGrp
from p in permissionGrp.DefaultIfEmpty()

/* Work around for 'allowed' not being honoured properly, using 'let' */
let isAllowed = allowed

/* Added bool condition 'isAllowed' to control inclusion when 'p' is null */
where (p==null && isAllowed) || (p!=null && /* ... condition based on the existence of a permission */)
select new { contact, permission = p };

Result

It is now using comparison of a known value with itself as a boolean check. In this case t0.ContactId.

SQL When allowed=true ... t0.ContactId = t0.ContactId

WHERE
    ((t1.PermissionId IS NULL AND t0.ContactId = t0.ContactId) OR 
     (t1.PermissionId IS NOT NULL AND ... other conditions ...))

SQL When allowed=false ... t0.ContactId <> t0.ContactId

WHERE
    ((t1.PermissionId IS NULL AND t0.ContactId <> t0.ContactId) OR 
     (t1.PermissionId IS NOT NULL AND ... other conditions ...))