score:2

Accepted answer

join in LINQ assumes an inner join (no nulls). Try pulling the null stuff out into separate where clauses. I think something along these lines should work for what you're describing.

from lt in cxt.CmsPageRow
join page in cxt.CmsPage on lt.CmsPageID == page.CmsPageID
where cmsSiteID == null || 
      (cmsSiteID != null && (page.CmsSiteID == null || page.CmsSiteId == cmsSiteID.Value))
select ...

Update

I didn't realize that performance was an issue for you. In that case, I'd suggest creating a different query structure based on values that are known at run-time and don't depend on individual rows:

var rows = 
    from lt in cxt.CmsPageRow
    join page in cxt.CmsPage on lt.CmsPageID == page.CmsPageID
    select new {lt, page};
if (cmsSiteID != null)
{
    rows = rows.Where(r => r.page.CmsSiteID == null || 
                           r.page.CmsSiteId == cmsSiteID.Value));
}
var data = rows.Select(...);

Also, if your data context is set up right, you should be able to use navigation properties to simplify your code somewhat.

var rows = ctx.CmsPageRow;
if (cmsSiteID != null)
{
    rows = rows.Where(r => r.CmsPage.Any(p => p.CmsSiteID == null || 
                               p.CmsSiteId == cmsSiteID.Value));
}
var data = rows.Select(...);

Related Articles