How to handle linq join query when join on id is not available in the table c#

Question

I am not trying to join my table using left outer join in LINQ where one of my join conditions does not satisfy. How can I still get the default record in my dataset? My SQL query runs perfectly fine but my join query is not returning the data.

Below is my SQL query where my left outer join works fine:

 select 
  w.issueid as Issue
 from worklog w 
   join jiraissue as j on w.issueid=j.ID
   join issuetype AS ty ON ty.ID = j.issuetype
   join project AS p on p.ID=j.PROJECT
    left outer join customfieldvalue cfv on cfv.ISSUE = w.issueid

Below is my LINQ query in C# where if cfv.issue in the last join is not available in the table, I want to still return the default column.

  var data = (from w in worklogs
                    
                    join i in issuetypes on j.issuetype equals i.ID
                    join p in projects on j.PROJECT equals p.ID into table0
                    from c in table0.DefaultIfEmpty()
                    join cfv in customfieldvalues on w.issueid equals cfv.ISSUE into table1
                    from d in table1.DefaultIfEmpty()
                    {
                        IssueKey = l.Key.pkey + '-' + l.Key.issuenum,
                        Hours = l.Sum(w => w.timeworked)
                    }).ToList();

Any help is appreciated. Thank you.

Try removing `into table0` and `from c in table0.DefaultIfEmpty()` — Chetan, Mar 01 '21 at 06:16
Does this answer your question? [LEFT OUTER JOIN in LINQ](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — Peter Csala, Mar 01 '21 at 08:05
@PeterCsala Thanks for your suggestion peter but that doesn't help. I am joining on w.issueid equals cfv.ISSUE where issueid in cfv does not exist but still I want to see the default row. — Pooja, Mar 01 '21 at 14:02

How to handle linq join query when join on id is not available in the table c#

0 Answers0