asp.net core join query not showing all data

Marius Vasile

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rijwan Ansari5y ago
Accepted 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.
+1
Marius Vasile5y ago
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
+2
Rijwan Ansari5y ago
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.
+1
Marius Vasile5y ago
I found a solution
WONumberS = c == null ? 0 : c.WONumberS,
and it is working
+1