Query many to many relation with lambda expression

Question

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:

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; }
}

See this question: http://stackoverflow.com/questions/10822656/entity-framework-include-multiple-levels-of-properties and MSDN: https://msdn.microsoft.com/en-us/library/jj574232(v=vs.113).aspx — Peter B, Dec 13 '16 at 09:23
Can you show us the sets that are currently available in your oDB? As @PeterB pointed out, the key is to include the sets that you need. — Razvan Dumitru, Dec 13 '16 at 09:23

Alexander Taran · Accepted Answer · 2016-12-13T19:08:19.520

4

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> 
}

edited Dec 13 '16 at 19:08

answered Dec 13 '16 at 09:41

Alexander Taran

6,655
2
39
60

This is a good answer. But how could I access to Category.Title for example, in the view (Index.cshtml for instance)? – Muhamad Jafarnejad Dec 13 '16 at 10:29
@foreach(var item in model){
string.join(", ", item.Product_Categories.Select(pc=>pc.Category.Title))
} – Alexander Taran Dec 13 '16 at 19:05
Unfortunately I get this string: System.Linq.Enumerable+WhereSelectEnumerableIterator`2[oDB.Products_Categories,System.String] – Muhamad Jafarnejad Dec 14 '16 at 07:44

score 1 · Answer 2 · answered Dec 13 '16 at 09:40

1

You need to use something like this:

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

answered Dec 13 '16 at 09:40

Mihai Alexandru-Ionut

47,092
13
101
128

This way is appropriate for one to many relationship in which Products table and Status table are connected to each other directly and without any junction table. Is it possible to include Categories table in a way like this? – Muhamad Jafarnejad Dec 13 '16 at 10:11
Please post your classes. – Mihai Alexandru-Ionut Dec 13 '16 at 10:13
I've edit my question and added classes definitions. – Muhamad Jafarnejad Dec 13 '16 at 10:28
1

Please try this: `oDB.Products.Include("Products_Categories.Category").ToList();` – Mihai Alexandru-Ionut Dec 13 '16 at 10:30
Thanks but then how could I access to Category.Title for example, in the view (Index.cshtml for instance)? – Muhamad Jafarnejad Dec 13 '16 at 10:53
Try this: `@item.Products_Categories.Category.Title` – Mihai Alexandru-Ionut Dec 13 '16 at 11:02
Unfortunately I get this exception: 'ICollection' does not contain a definition for 'Category' and no extension method 'Category' accepting a first argument of type 'ICollection' could be found (are you missing a using directive or an assembly reference?) – Muhamad Jafarnejad Dec 13 '16 at 11:12
1

@item.Products_Categories.Single(a=>a.Product_ID==@item.ID).Category.Title – Mihai Alexandru-Ionut Dec 13 '16 at 11:27

Query many to many relation with lambda expression

2 Answers2

string.join(", ", item.Product_Categories.Select(pc=>pc.Category.Title))