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
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
};
NULL
NULL
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.