Query a many-to-many relationship with linq/Entity Framework. CodeFirst

Question

How can I query a many-to-many relationship using Entity Framework code first and linq? The problem is that EF create automatically the relation table. So, I don't have it in my context.

This is the relational model:

enter image description here

I need a list of Articles for a specific Category_Id, basically replicate something like that:

select a.Id, a.Title,a.ShortDescription                       
from Articles a
join CategoryArticles ca on ca.Article_Id=a.Id
where ca.Category_Id  = @parameter

However my dbcontext only have :

public DbSet<Article> Articles { get; set; }
public DbSet<Category> Categories { get; set; }.

Thanks for any help.

ocuenca · Accepted Answer · 2018-12-16T15:22:50.267

60

You can do this:

var cat_id=1; // Change this variable for your real cat_id

var query= from article in db.Articles
           where article.Categories.Any(c=>c.Category_ID==cat_id)
           select article;

This way you will get the articles that satisfies the condition you want. This is the sql code that is generated by that query:

    SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Title] AS [Title]
    FROM [dbo].[Articles] AS [Extent1]
    WHERE  EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[ArticleCategories] AS [Extent2]
        WHERE ([Extent1].[Id] = [Extent2].[Article_Id]) AND ([Extent2].[Category_Id] = @p__linq__0))

Update

Another option could be using SelectMany extension method (as @Khaled pointed out) starting your query from Categories instead of Articles:

var query= db.Categories.Where(c=>c.Category_ID==cat_id).SelectMany(c=>Articles);

That would generate an Inner join instead of the EXIST that is product of Any extension method.

edited Dec 16 '18 at 15:22

answered May 26 '15 at 19:21

ocuenca

38,548
11
89
102

1

This is incorrect, EF is not smart enough to use joins. It will instead create an EXISTS statement. Performance can be an issue here. – Talon Aug 19 '16 at 10:42
@Talon, now I tested that query and it's true that EF 6.x Linq provider is generating an `Exists` instead of a `join`. Thanks for the feedback. Agreed with you about the performance but if you omit the junction table as part of your model this is the only way to do it. – ocuenca Aug 19 '16 at 14:37
2

@octavioccl agreed, I have not found a way around it without that junction table. I'm thinking that these implicit junction tables are bad news but it's going to require a lot of work to remove them with minimal reward. – Talon Aug 22 '16 at 06:28
1

For faster queries using INNER JOIN, refer to Khaled's answer below: https://stackoverflow.com/a/41244639/193634 – Rosdi Kasim Dec 16 '18 at 15:07

score 17 · Answer 2 · edited Aug 15 '17 at 16:32

17

How about

db.Categories.Where(c => c.Id == categoryId).SelectMany(c => c.Articles)?

This should work fine (produce the right joined sql statement.)

edited Aug 15 '17 at 16:32

CDspace

2,639
18
30
36

answered Dec 20 '16 at 14:29

Khaled Rashad

171
1
2

where is the 'a' from? Could you clarify what you mean please. – glenn garson Jan 14 '17 at 18:34
It should be `c => c.Articles` . This will join each article list of categories selected from Where query. – Burak Dobur Jul 27 '17 at 14:40
Great, this is right solution. EF generated INNER JOIN instead WHERE EXISTS. With EXISTS is very slow. – Pažout Oct 02 '17 at 10:26

score 4 · Answer 3 · answered Dec 04 '17 at 13:24

4

Example linq method syntax

int category_ID = 1;

var query = db.Articles
    .Where(a => a.Categories
    .Any(c => c.Category_ID == category_ID))
    .ToList();

answered Dec 04 '17 at 13:24

Mauricio Ferraz

191
3

JOpuckman · Answer 4 · 2017-02-20T18:24:47.470

2

I just ran into this and figured I'd post the solution I found for anyone stumbling across this page. This produces an INNER JOIN.

var category_id = 24;

var query = (from article in Articles
             from category in article.Categories.Where(x => x.Category_ID == category_id)
             select article);

edited Feb 20 '17 at 18:24

answered Feb 20 '17 at 17:52

JOpuckman

1,306
9
17

score 1 · Answer 5 · answered Oct 16 '17 at 09:53

If you just want the whole table including all the relationships, maybe try something like this:

List<CategoryArticle> rec = context.Category.SelectMany(a => a.Articles.Select(c => new CategoryArticle { Category_Id = c.Id, Article_Id = a.Id })).ToList();

score -1 · Answer 6 · answered Jan 18 '19 at 19:26

Add and query the junction table:

  var articles = (from ca in _context.CategoryArticles
                  inner join a in _context.Articles on a.Id equals ca.Article_Id
                  inner join c in _context.Catgories on c.Id equals ca.Category_Id
                  where ca.Category_Id equals catId
                  select c).ToList();

Query a many-to-many relationship with linq/Entity Framework. CodeFirst

6 Answers6

Update

Linked