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?

Not really an mvc question. you should add a linq-to-sql tag too. — µBio
– µBio, Commented Nov 18, 2009 at 22:55

J.W. · Accepted Answer · 2009-11-19 03:16:34Z

1

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  }

edited Nov 19, 2009 at 3:16

answered Nov 18, 2009 at 23:11

J.W.

18.2k7 gold badges47 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Over a year ago

I'm getting an error on the second "join" and "where" that says: Expected contextual keyword 'equals'

J.W. Over a year ago

sorry, edited my query, join should use equal on the foreign key.

µBio · Accepted Answer · 2009-11-18 23:55:03Z

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.

edited Nov 18, 2009 at 23:55

answered Nov 18, 2009 at 22:52

µBio

10.8k6 gold badges41 silver badges56 bronze badges

1 Comment

J.W. Over a year ago

when you join two tables, do you need specify some join column?

Collectives™ on Stack Overflow

ASP.NET MVC - Join Tables using LINQ

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related