CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworkrazorlambda
Muhamad Jafarnejad
Muhamad Jafarnejad

Reputation: 2571

Query many to many relation with lambda expression

I'm using entity framework database first.

For one to many relation, in sql server, I use the following sql query(Status_ID is foreign key):

SELECT Products.*, Status.Title as Pstatus
FROM Products, Status 
WHERE Products.Status_ID = Status.ID

In MVC I use the following code to retrieve the same data as above sql query and pass the list to the View:

Controller:

var products = oDB.Products.Include(m => m.Status)
                       .ToList();
// string test = products[0].Status.Title;
return View(products);

In the view I can access the desired data by the following code:

View:

@model List<myDB.Product>
...
@item.Status.Title // This works well for each item in the list

For MANY TO MANY RELATIONS WITH JUNCTION TABLE, this is my .edmx:

enter image description here

Now How could I retrieve list of products including related categories? I need the list of products and pas it to the view as a list, and access each product's categories in the view.

My Classes (These classes are generated automatically):

public partial class Category
{
    public int ID { get; set; }
    public string Title { get; set; }
    public virtual ICollection<Products_Categories> Products_Categories { get; set; }
}

public partial class Products_Categories
{
    public int ID { get; set; }
    public Nullable<int> Product_ID { get; set; }
    public Nullable<int> Category_ID { get; set; }
    public virtual Category Category { get; set; }
    public virtual Product Product { get; set; }
}

public partial class Product
{
    public int ID { get; set; }
    public string Title { get; set; }
    public virtual ICollection<Products_Categories> Products_Categories { get; set; }
}

Upvotes: 1

Views: 2458

Answers (2)

Alexander Taran
Alexander Taran

Reputation: 6725

var products = oDB.Products.Include(m => m.Product_Categories.Select(pc=>pc.Category))
                       .ToList();
// string test = products[0].Status.Title;
return View(products);

and in a view you could use it like this

@foreach(var item in model){ 
   <h3>string.join(", ", item.Product_Categories.Select(pc=>pc.Category.Title))</h3> 
}

Upvotes: 4

Mihai Alexandru-Ionut
Mihai Alexandru-Ionut

Reputation: 48357

You need to use something like this:

var products = oDB.Products.Include("Status")
                   .ToList();
// string test = products[0].Status.Title;
return View(products);

Upvotes: 1

Related Questions