Left Join 2 tables with main table using LINQ

Question

This is my Query in SQL :

Select distinct * from tr.Table1 
Left Outer join tr.Table2 on tr.Table1.ID = tr.Table2.ID
Left Outer join tr.Table3 on tr.Table2.AId= tr.Table3.ID
where tr.Table1.Deleted =1 and tr.Table1.Ready=1 and tr.Table1.Show=0

The query is working in SQL and gives the expected results.The thing here is that I want the equivalent of this using LINQ. I have tried some variations in LINQ queries such as :

var query = from p in _ctx.Table1
join s in _ctx.Table2 on p.Id equals s.Id into bag1
from to in bag1.DefaultIfEmpty()
join tx in _ctx.Table3 on to.AId equals tx.Id into bag2
from ts in bag2.DefaultIfEmpty()
select new
{
    ContactNo = to.Table1.ContactNo
};

But it always doesn't return all the field values. Some are returned as NULL. Also tried referring to some other link as well but they all focus on joining with the parent table whereas I have to join one of the joined tables with the other one. So here I am, struggling with this.

This is the output that I'm getting as of now. Some values are null. The field has values but due to some joining issue, they are returned as NULL.

Guidance here is appreciated. Thank you.

Answer 1

Your query looks fine to me, the reason why you must be getting the Nulls is because when we use DefaultIfEmpty, it returns null for non-matching rows, thus you need to handle that while fetching the actual results. Try doing something like this:-

var query = from p in _ctx.Table1
join s in _ctx.Table2 on p.Id equals s.Id into bag1
from to in bag1.DefaultIfEmpty()
join tx in _ctx.Table3 on to.AId equals tx.Id into bag2
from ts in bag2.DefaultIfEmpty()
select new
{
    ContactNo = to == null ? String.Empty  : to.Table1.ContactNo
};

Assuming, ContactNo to be of type String, I have used String.Empty you can use any default value.