Query a many-to-many relationship with linq/Entity Framework. CodeFirst
Solution 1
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.
Solution 2
How about
db.Categories.Where(c => c.Id == categoryId).SelectMany(c => c.Articles)?
This should work fine (produce the right joined sql statement.)
Solution 3
Example linq method syntax
int category_ID = 1;
var query = db.Articles
.Where(a => a.Categories
.Any(c => c.Category_ID == category_ID))
.ToList();
Solution 4
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);
Solution 5
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();
Related videos on Youtube
D.B
Updated on January 19, 2020Comments
-
D.B over 4 years
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:
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.
-
Talon over 7 yearsThis is incorrect, EF is not smart enough to use joins. It will instead create an EXISTS statement. Performance can be an issue here.
-
ocuenca over 7 years@Talon, now I tested that query and it's true that EF 6.x Linq provider is generating an
Exists
instead of ajoin
. 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. -
Talon over 7 years@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.
-
glenn garson over 7 yearswhere is the 'a' from? Could you clarify what you mean please.
-
Burak Dobur almost 7 yearsIt should be
c => c.Articles
. This will join each article list of categories selected from Where query. -
Pažout over 6 yearsGreat, this is right solution. EF generated INNER JOIN instead WHERE EXISTS. With EXISTS is very slow.
-
Rosdi Kasim over 5 yearsFor faster queries using INNER JOIN, refer to Khaled's answer below: stackoverflow.com/a/41244639/193634