More efficient Linq-to-SQL query

Question

I am looking for a more efficient way to do this...(see below)... I have to perform it seven times as I have seven feature articles. The id I am feeding off is the page id (aka featurearticles.fk_pageID_item1 featurearticles.fk_pageID_item2).

I am ok with doing a table join and not selecting * as it were. I am also ok with making a temporary object to hold information.

DataAccess.Page pgf1 = (from p in db.Pages
where p.pageID == featurearticles.fk_pageID_item1
select p).FirstOrDefault();

PageArticle paf1 =(from pa in db.PageArticles
where pa.page_art_pageID == pgf1.pageID &&
pa.page_art_isCurrent ==true
select pa).FirstOrDefault();

Article af1 = (from a in db.Articles
where a.articleID == paf1.page_art_articleID
select a).FirstOrDefault();

1. gets a page.
2. gets the current version.
3. gets the info for that version.

I am stuck with this table layout.

Thanks.

Answer 1

Have a look at this. These are all inner joins, and I am not sure if this is entirely what you require? If not let me know.

var tp = from p in Pages
    join pa in PageArticles on p.pageID equals pa.page_art_pageID
    join a in Articles on pa.page_art_articleID equals a.articleID
    where p.pageID == featurearticles.fk_pageID_item1
    && pa.page_art_isCurrent
    select new { p, pa, a };

I can also change the new select to what ever fields you require.

Answer 2

Most of the credit for this goes to astander.

Article af1 = (from p in db.Pages
               join pa in db.PageArticles on p.pageID equals pa.page_art_pageID
               join a in db.Articles on pa.page_art_articleID equals a.articleID
               where p.pageID == featurearticles.fk_pageID_item1
               && pa.page_art_isCurrent == true
               select a).FirstOrDefault();

You should be able to use this query as a drop in replacement for what you already have.