asp.net core join query not showing all data

Question

I have two table, WOMain and WOPlanner, related to each other with ID. What I want, is to run a query and add to ViewModel data from both tables, even if is there is no related data in WOPlanner. Right now, I can only see data that has records in both tables. WOVL = await (from a in _context.WOMains.Where(s => s.OrgID == orgid) join b in _context.WOPlanners on a.WOMainID equals b.WOMainID into Temp1 from c in Temp1 select new WOViewIndex { WOMainID = a.WOMainID, WONumber = a.WONumber, WONumberS = c.WONumberS, CreateDate = a.CreateDate, Deficiency = a.Deficiency, WOStatus = a.WOStatus }).ToListAsync();

Rijwan Ansari · Answer

A full outer join is a logical union of a left outer join and a right outer join. LINQ does not support full outer joins directly. ( However, In sql Full Join is possible.) Sample: var leftOuterJoin = from e in Context.EmployeeMasters join d in Context.DepartmentMasters on e.DepartmentId equals d.DepartmentId into dept from department in dept.DefaultIfEmpty() select new { EmployeeCode = e.Code, EmployeeName = e. Name , DepartmentName = department. Name }; var rightOuterJoin = from d in Context.DepartmentMasters join e in Context.EmployeeMasters on d.DepartmentId equals e.DepartmentId into emp from employee in emp.DefaultIfEmpty() select new { EmployeeCode = employee.Code, EmployeeName = employee. Name , DepartmentName = d. Name }; leftOuterJoin = leftOuterJoin. Union (rightOuterJoin); Final union result will be full Join.

Marius Vasile · Answer

Thank you Rijwan. Yes, it is a good solution, a little more elaborated but it will provide the results. I will mark it as answer to question because it is more complete

Rijwan Ansari · Answer

Alternatively, you can use FULL JOIN. A FULL JOIN or FULL OUTER JOIN is essentially a combination of LEFT JOIN and RIGHT JOIN. This type of join contains all of the rows from both of the tables. Where the join condition is met, the rows of the two tables are joined, just as in the previous examples we've seen. For any rows on either side of the join where the join condition is not met, the columns for the other table have NULL values for that row.

Marius Vasile · Answer

I found a solution WONumberS = c == null ? 0 : c.WONumberS, and it is working