Kamran Sadin Kamran Sadin - 3 months ago 17
ASP.NET (C#) Question

LINQ Join not returning results if second or third table empty

i have 3 tables :

Module_Articles_Articles
Module_Articles_Categories
Module_Articles_Comments


and i want to display my articles in repeater my query :

var articles =
(from a in context.Module_Articles_Articles
join c in context.Module_Articles_Categories on a.CategoryID equals c.CategoryID
join co in context.Module_Articles_Comments on a.ArticleID equals co.ArticleID
where a.IsDraft == false
orderby a.ArticleID descending
select new
{
a.ArticleID,
a.ArticleTitle,
a.ArticleContent,
a.Image,
a.Sender,
a.SentDate,
a.Summary,
a.Likes,
a.Dislikes,
a.Tags,
a.PostMode,
c.CategoryID,
c.CategoryTitle,
AcceptedCommentsCount =
(from com in context.Module_Articles_Comments where com.ArticleID == a.ArticleID && com.Status select com)
.Count(),
DeniedCommentsCount =
(from com in context.Module_Articles_Comments where com.ArticleID == a.ArticleID
&& com.Status == false select com)
.Count()
}).ToList();


but when
Module_Articles_Categories
or
Module_Articles_Comments
are empty my query returns nothing!
is my code true? if not how can i do this?

Answer

you want an OUTTER JOIN, which can be accomplished in a query like this by simply adding .DefaultIfEmpty()

from a in context.Module_Articles_Articles
join c in context.Module_Articles_Categories on a.CategoryID equals c.CategoryID into ca
from c in cs.DefaultIfEmpty()
join co in context.Module_Articles_Comments on a.ArticleID equals co.ArticleID into com
from co in com.DefaultIfEmpty()
where a.IsDraft == false
orderby a.ArticleID descending
select new ...
Comments