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

d.b Published at Dev

D.B

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.

octavioccl

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-19

Comments

0 comments

From Dev

Many to many relationship in Entity Framework

From Dev

EntityFramework CodeFirst: CASCADE DELETE for same table many-to-many relationship

From Dev

MVC5 codefirst adding a user to many-many relationship

From Dev

Query many to many relationship with DetachedCriteria

From Dev

how to query a many to many relationship?

From Dev

eloquent: query many to many relationship

From Dev

Entity Framework CodeFirst many to many return object null

From Dev

Entity Framework CodeFirst many to many return object null

From Dev

Entity Framework many to many relationship Delete

From Dev

Saving Many to Many relationship - Entity Framework

From Dev

Entity Framework specify cardinality in a many to many relationship

From Dev

Entity framework many-to-many relationship

From Dev

How many to many relationship in Entity Framework is working

From Dev

Update a Many to Many relationship using Entity Framework?

From Dev

Entity Framework duplicate entries in many to many relationship

From Dev

Django Rest Framework implementing many to many relationship

From Dev

Updating many-to-many relationship entity framework

From Dev

Entity Framework Many TO Many Relationship with Primary Key

From Dev

Entity Framework with many to many relationship generetad tables

From Dev

Entity Framework Core Creating a Many to Many Relationship

From Dev

JPA criteria query in a many-to-many relationship

From Dev

Parse - Array of pointers - Many to Many relationship query

From Dev

CoreData: Query to one-to-many-to-many relationship

From Dev

Many-to-many relationship query alright?

From Dev

Advanced SQLite query on many-to-many relationship

From Dev

Laravel Many to Many query relationship with where clause

From Dev

Hibernate query on many to many relationship with extra column

From Dev

JPQL query with many-to-many relationship

From Dev

ios parse one query many to many relationship

Related Related

Article