LINQ joins columns AND variable NULL

Question

I am trying to join tables using LINQ by matching columns where the column in the joined table is equal to a variable or the variable is null (after which the join should still happen not in this field).

My LINQ is something like:

var data = ( from lt in cxt.CmsPageRow join page in cxt.CmsPage on new { lt.CmsPageID, cmsSiteID.Value } equals new { page.CmsPageID, page.CmsSiteID } ... 

cmsSiteID is the NULL value of INT.

I cannot compile my code because it complains about "Type input error in the" Join "call."

In addition, I only need to join the page. CmsSiteID when cmsSiteID is not null. If cmsSiteID is null, then a connection to lt.CmsPageID should still occur.

* EDIT *

Now the question has changed. I can get it to do what I want using the WHERE clause in the join in my LINQ.

 join page in cxt.CmsPage.Where(p=>(cmsSiteID==0||p.CmsSiteID==cmsSiteID)) on lt.CmsPageID equals page.CmsPageID 

However, it still works slowly. If I change the parameter passed to the literal, it will execute instantly.

Slow runner

 (@p__linq__1 = 0 OR [Extent2].[CmsSiteID] = @p__linq__1) 

Fast runner

 (267 = 0 OR [Extent2].[CmsSiteID] = 267) 

Is there any way to speed this up?

Answer 1

join in LINQ assumes an inner join (no zeros). Try pulling null stuff into separate where clauses. I think something in this direction should work for what you are 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 did not understand that performance was a problem for you. In this case, I would suggest creating another query structure based on values ​​that are known at runtime and are independent of individual lines:

 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(...); 

In addition, if your data context is configured correctly, you should be able to use navigation properties to simplify the code a bit.

 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(...);