LINQ Join not returning results if second or third table empty

Question

I've three 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 1

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 ...