LINQ query with a join on an intersection table

January 8, 2010

I was working on my MVC blog engine when I needed to write a query that would return blog entries that contained a tag name. This query is simple to write in SQL. It’s a query from the blog entry, to the blog entry tag, to the blog tab tables.

##SQL

select 
be.*
from
BlogEntry be
left join BlogEntryCategory bec on bec.BlogEntry_ID = be.BlogEntry_ID
join BlogCategory bc on bc.BlogCategory_ID = bec.BlogCategory_ID
and bc.Category like '%' + @Category + '%'

I quickly discovered that this is not as straight forward as I thought it would be in LINQ. I first stared out trying to write joins but the whole “into” grouping was not cool. I then just used my sql proc but I lost my ability to leverage the IQueryable interface which I wanted to that I could chain other clauses against it later (such as the pagination I intend to implement).

I found a post on stackoverflow that talked about multiple left outer joins. Towards the bottom was a post was an answer from “aikr473” with an example of using Lambda expressions in the joins. This is exactly what I needed (I voted the answer up).

##LINQ

public IQueryable<BlogEntry> FindBlogEntriesByCategory(string category)
{
    return (from entries in db.BlogEntries
            from catlink in db.BlogEntryCategories.Where(e => e.BlogEntry_ID == entries.BlogEntry_ID).DefaultIfEmpty()
            from Category in db.BlogCategories.Where(c => c.BlogCategory_ID == catlink.BlogCategory_ID && c.Category.Contains(category))
            orderby entries.Published descending
            select entries);
}

This allows me to set up the base query the way I want it, which is a result set sorted by the most recent posts. It also allows me to chain on to this statement later so that I can do something like a take or skip in a paging scenario.

To give you an idea of how I’ll use this:

##Controller

//
// GET: /Blog/Listing/3
public ActionResult Listing(string id, int? page)
{
    var entries = repository.FindBlogEntriesByCategory(id);
    var paginatedEntries = new PaginatedList<BlogEntry>(entries, page ?? 0, pageSize);
    return View("Index", paginatedEntries);
}
PaginatedList class (example taken from NerdDinner)

public class PaginatedList<T> : List<T>
{
    public int PageIndex { get; set; }
    public int PageSize { get; set; }
    public int TotalCount { get; set; }
    public int TotalPages { get; set; }
 
    public PaginatedList(IQueryable<T> source, int pageIndex, int pageSize)
    {
        PageIndex = pageIndex;
        PageSize = pageSize;
        TotalCount = source.Count();
        TotalPages = (int)Math.Ceiling(TotalCount / (double)PageSize);
 
        this.AddRange(source.Skip(pageIndex * pageSize).Take(pageSize));
    }
 
    public bool HasPreviousPage
    {
        get
        {
            return (PageIndex > 0);
        }
    }
 
    public bool HasNextPage
    {
        get
        {
            return (PageIndex + 1 < TotalPages);
        }
    }
}

This gives me a lot of flexibility on how I can pull the data out of sql repository, which may change based on the context of the area in my site that a person is visiting.

Billy Coover

LINQ query with a join on an intersection table