Can't get Left JOIN linq query to work!

Question

I've been looking at the following post and trying to apply it to mine but with no luck: LINQ Inner-Join vs Left-Join

I have the query below that returns 0 records everytime 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 options. I want one record returned for each entry 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 it is generating-

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)

As you can see, it's definitely generating a LEFT JOIN... so I suggest you start hacking at the SQL itself until that produces what you want, and then try to convert that back into LINQ. — Jon Skeet, Aug 11 '10 at 22:55

score 2 · Answer 1 · answered Aug 11 '10 at 22:35

2

Your problem is in the WHERE clause

When you LEFT JOIN a table, that's fine, but if you set criteria on the LEFT-JOINED table, it basically turns it into an INNER JOIN. You should allow for Nulls to get past this.

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

I'm not sure how about the is NULL syntax -- maybe have to be db.null(field), etc. -- you'll have to check on that; but the concept is valid.

answered Aug 11 '10 at 22:35

dave

1,344
10
16

Also -- you need to turn it back into a *left* join. – dave Aug 11 '10 at 22:36
I assume you mean add `|| cbt.ProposalID == null`. I did this and still no records. I've updated my LINQ query to reflect this change, is that what you meant? – Abe Miessler Aug 11 '10 at 22:43
Note that a simpler way to do this would be to put the where clause into the join: "from cbt in d.CostByTasks.Where(x => x.ProposalId == ...)" – Jon Skeet Aug 11 '10 at 23:00
Arg... yes -- that's what I meant. I see this problem all the time in regular SQL queries. OK -- still looking. I guess I'd next try pulling the WHERE clause off, to see if your left join is working as it should -- at least that could help narrow it down. – dave Aug 11 '10 at 23:01
unfortunately it pulls a bunch of records that i don't want if I take off the `WHERE` clause. I'm playing with the SQL now to see if I can resolve the issue, let me know if you find anything. – Abe Miessler Aug 11 '10 at 23:04
Try writing the null sentence like this: where cbt.ProposalID != null ? Convert.ToInt32(this.StateItems["PropNumber"]) : null – dave Aug 11 '10 at 23:14
1

I'd also recommend doing a response.write on your this.StateItems["PropNumber"], just to make sure the value you *think* is in there really is... – dave Aug 11 '10 at 23:15

score 2 · Accepted Answer · edited Aug 16 '10 at 19:04

Not sure if this'll help, but you could try moving your where clause into the 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 would need to worry about checking for cbt.ProposalID == null and It should produce this sql instead -

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

UPDATE: Below is the updated version that compiles. A few minor changes got it working.

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)
                        };

Looks like you have the right idea but it doesn't quite compile. I went ahead and added a post of my own with working changes for future users. — Abe Miessler, Aug 15 '10 at 02:00
That's fine - just let me know where the compilation error is an i can fix it here, that way there's no duplicate answer and the correct answer gets credit. I wasn't going to check for compilation errors since setting up the types would be overkill on my part. Or you could even edit it yourself since you have over 2000 reputation. — Frank Tzanabetis, Aug 16 '10 at 00:04

Can't get Left JOIN linq query to work!

2 Answers2