Unable to force Left JOIN to execute linq query!

Question

I watched the next post and tried to apply it to mine, but no luck: LINQ Inner-Join vs Left-Join

I have a query below that returns 0 records every time I run it:

var tasks = from tt in d.luProjectTaskTypes join cbt in d.CostByTasks on tt.ProjectTaskTypeID equals cbt.ProjectTaskTypeID into temp from cbt in temp.DefaultIfEmpty() where cbt.ProposalID == Convert.ToInt32(this.StateItems["PropNumber"]) || cbt.ProposalID == null select new { ProposalId = (cbt.ProposalID == null ? Convert.ToInt32(this.StateItems["PropNumber"]) : cbt.ProposalID), TaskId = tt.ProjectTaskTypeID, CostByTaskId = (cbt.CostByTaskID == null ? 0 : cbt.CostByTaskID), TypeOfWork = tt.ProjectTaskType, AmountRequested = (cbt.AmountRequested == null ? 0 : cbt.AmountRequested), CostShare = (cbt.CostShareAmount == null ? 0 : cbt.CostShareAmount) }; 

Where luProjectTaskTypes is a lookup table that has a list of parameters. I want one record to be returned for each record in this table, regardless of whether it has a match in CostByTasks , but I always get 0. What am I doing wrong ?!

UPDATE:

This is the SQL that it generates -

 SELECT (CASE WHEN ([t1].[ProposalID]) IS NULL THEN @p1 ELSE [t1].[ProposalID] END) AS [ProposalId], [t0].[ProjectTaskTypeID] AS [TaskId], (CASE WHEN ([t1].[CostByTaskID]) IS NULL THEN @p2 ELSE [t1].[CostByTaskID] END) AS [CostByTaskId], [t0].[ProjectTaskType] AS [TypeOfWork], (CASE WHEN [t1].[AmountRequested] IS NULL THEN CONVERT(Decimal(33,4),@p3) ELSE CONVERT(Decimal(33,4),[t1].[AmountRequested]) END) AS [AmountRequested], (CASE WHEN [t1].[CostShareAmount] IS NULL THEN CONVERT(Decimal(33,4),@p4) ELSE CONVERT(Decimal(33,4),[t1].[CostShareAmount]) END) AS [CostShare] FROM [frgprop].[luProjectTaskType] AS [t0] LEFT OUTER JOIN [frgprop].[CostByTask] AS [t1] ON [t0].[ProjectTaskTypeID] = [t1].[ProjectTaskTypeID] WHERE ([t1].[ProposalID] = @p0) OR (([t1].[ProposalID]) IS NULL) 

Answer 1

Not sure if this will help, but you can try moving the where clause to join -

 var tasks = from tt in d.luProjectTaskTypes join cbt in d.CostByTasks on new {ptid = tt.ProjectTaskTypeID, pid = cbt.ProposalID } equals new { ptid = cbt.ProjectTaskTypeID, pid = Convert.ToInt32(this.StateItems["PropNumber"] } into temp from cbt in temp.DefaultIfEmpty() select new { ProposalId = (cbt.ProposalID == null ? Convert.ToInt32(this.StateItems["PropNumber"]) : cbt.ProposalID), TaskId = tt.ProjectTaskTypeID, CostByTaskId = (cbt.CostByTaskID == null ? 0 : cbt.CostByTaskID), TypeOfWork = tt.ProjectTaskType, AmountRequested = (cbt.AmountRequested == null ? 0 : cbt.AmountRequested), CostShare = (cbt.CostShareAmount == null ? 0 : cbt.CostShareAmount) }; 

You will need to worry about checking cbt.ProposalID == null , and instead it should create this sql -

  ... FROM [frgprop].[luProjectTaskType] AS [t0] LEFT OUTER JOIN [frgprop].[CostByTask] AS [t1] ON [t0].[ProjectTaskTypeID] = [t1].[ProjectTaskTypeID] AND [t1].[ProposalID] = @p0 

UPDATE: The following is an updated version that compiles. A few minor changes made it work.

 var tasks = from tt in d.luProjectTaskTypes join cbt in d.CostByTasks on new {tt.ProjectTaskTypeID, ProposalID = Convert.ToInt32(this.StateItems["PropNumber"]) } equals new {cbt.ProjectTaskTypeID, cbt.ProposalID} into temp from cbt in temp.DefaultIfEmpty() select new { ProposalId = (cbt.ProposalID == null ? Convert.ToInt32(this.StateItems["PropNumber"]) : cbt.ProposalID), TaskId = tt.ProjectTaskTypeID, CostByTaskId = (cbt.CostByTaskID == null ? 0 : cbt.CostByTaskID), TypeOfWork = tt.ProjectTaskType, AmountRequested = (cbt.AmountRequested == null ? 0 : cbt.AmountRequested), CostShare = (cbt.CostShareAmount == null ? 0 : cbt.CostShareAmount) }; 

Answer 2

Your problem in the WHERE clause

When you LEFT JOIN in a table, that’s fine, but if you set the criteria in the LEFT-JOINED table, it basically turns it into an INNER JOIN. You must allow the Nulls to overcome this.

 where cbt.ProposalID == Convert.ToInt32(this.StateItems["PropNumber"]) OR cbt.ProposalID is NULL 

I'm not sure what about the NULL syntax - maybe it will be db.null(field) etc. - you will need to check it; but the concept is valid.