ASP.NET MVC - Join Tables using LINQ

Question

My application is structured as:

namespace DomainModel.Abstract
{
    public interface IContentItemsRepository
    {
        IQueryable<Content> ContentItems { get; }
        IQueryable<Category> Categories { get; } 
        IQueryable<ContentCategory> ContentCategories { get; }
    }
}

namespace DomainModel.Entities
{
    [Table(Name = "Content")]
    public class Content
    {
        [Column(IsPrimaryKey = true, 
              IsDbGenerated = true, 
              AutoSync = AutoSync.OnInsert)]
        public int content_id { get; set; }
        [Column]
        public string type { get; set; } // article, video, recipe, tool
        [Column]
        public string title { get; set; }
...

namespace DomainModel.Entities
{
    [Table(Name = "Content_Categories")]
    public class ContentCategory
    {
        [Column(IsPrimaryKey = true, 
              IsDbGenerated = true, 
              AutoSync = AutoSync.OnInsert)]
        public int content_category_id { get; set; }
        [Column]
        public int content_id { get; set; }
        [Column]
        public int category_id { get; set; }
...

namespace DomainModel.Entities
{
    [Table(Name = "Categories")]
    public class Category
    {
        [Column(IsPrimaryKey = true, 
              IsDbGenerated = true, 
              AutoSync = AutoSync.OnInsert)]
        public int category_id { get; set; }
        [Column]
        public string category_name { get; set; }
        [Column]
        public string type { get; set; } //recipe,tool,other 
        [Column]
        public int ordering { get; set; }
...

I can do this:

var articlesInCategory = _contentRepository.ContentItems
            .Where(x => x.type == "article");

and get a list of articles. No problem.

However, I now need to select Content based on categories. So, I need to join Content to ContentCategory to Category.

I have no idea how to do this. Any help will be much appreciated.

Thanks.

EDIT:

I think part of my problem is that I don't even know how to call what I'm doing, so it's hard to search for this. Am I even doing LINQ to SQL, or LINQ to Entities, or is it LINQ to Objects?

Answer 1

The concept you are looking for is called SelectMany in linq, and there are a number of ways to accomplish it.

One is:

var content = 
from category in _categoryRepository.CategoryItems
join contCat in _contentCategoryRepository.Items
on category.category_id == conCat.category_id
where category.category_id == parameter
select contCat.content_id;

From here you should be able to extend it into pulling out all the data you need...look into the into keyword and check out this link if you haven't already.

Answer 2

The join query will be something like this.

var content=
from category  in _contentRepository.Category
join  contentCategory in _contentRepository.ContentCategory
on category.category_id equals contentCategory.category_id 
join content in _contentRepository.Content
on content.content_id equals contentCategory.content_id
where category.category_id==@yourcategoryId 
select new {type , title  }