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

c# entity-framework linq many-to-many

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.

1
35
5/26/2015 6:58:32 PM

Accepted Answer

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.

43
12/16/2018 3:22:50 PM

Popular Answer

How about

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

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow