ASP.NET (C#) Question
Left Join 2 tables with main table using LINQ
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
NULLThis 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.
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download