DefaultIfEmpty and SQL joins

Tue, 13 May 2008 18:47:38 Z

Hi.
I am struggeling with doing a left outer join. I have 3 tables tblArticle, tblImages and a bridging table called tblArticleImages.
I have the LINQ down to retrieve articles with and image as:
    from a in tblArticle
    join ai in tblArticleImage on a.ID equals ai.articleID
    join i in tblImages on ai.imageID equals i.ID
    select new {
        articleID = a.ID,
        imageID = i.ID,
        imagePath = i.imagePath
    }

What I now want is to add a defaut imagePath if there is no image that is linked via tblArticleImages. I found some help on DefaultIfEmpty() but just cannot get it to work on 3 tables, the example only had 2 tables. Could someone please try and provide me with the LINQ to do this??

Then another question, maybe off toppic and it is a newbie question. when I do the select I either select from a, ai or i .... could I not instead of selecting new {....} select from all 3?? Something like select a, ai, i ....??

Thanks in advance, I am sure if I get the answer some newbie like me will Google this post and find it helpfull.

DefaultIfEmpty and SQL joins

Fri, 16 May 2008 19:13:55 Z

Here's a possible implementation:

Code Snippet

var query = from a in tblArticle

join image in (

from ai in tblArticleImage

join i in tblImages on ai.imageID equals i.ID

select new

{

articleID = ai.articleID,

imageID = i.ID,

imagePath = i.imagePath

}) on a.articleID equals image.articleID into joinedImages

from defaultIfEmptyImage in joinedImages.DefaultIfEmpty()

select new

{

articleID = a.articleID,

imageID = defaultIfEmptyImage.imageID,

imagePath = defaultIfEmptyImage.imagePath

};

DefaultIfEmpty and SQL joins

Mon, 19 May 2008 19:29:13 Z

That one might work but my solution for

tblArticle, tblArticleImages and tblImages that is as follows might work better(Or I read it easier??)

from a in tblArticles
join ac in tblArticleCategories on a.ID equals ac.articleID
join c in tblCategories on ac.categoryID equals c.ID
where c.name == “Africa insight”
select new
{
categoryName = c.name,
headline = a.heading,
articleID = a.ID,
synopsis = a.synopsis,
story = a.story,
imagePath = (
from a2 in tblArticles
join ai in tblArticleImages on a2.ID equals ai.articleID
join i in tblImages on ai.imageID equals i.ID
where i.isThumbnail == true && a2.ID == a.ID
select i.imagePath
).FirstOrDefault()
}