1

How do I read a many-to-many table via EF? I have no idea how to use the many-to-many table. Let's say Product_Category where it got ProductID and CategoryID.

How can I access it trough e.g. 

using(Entities db = new Entities)
{
    /* cant access these here.. */}

method?? I can however reach Product_Category, but cant access its ProductID or CategoryID.

I want to list every product e.g. where Product_Category.CategoryID == Category.ID.

I have never used many-to-many tables before, so I appreciate some simple examples how to access them trough EF in asp.net.

Thanks

Ezony
  • 141
  • 1
  • 3
  • 9
  • Possible duplicate of [Entity Framework code first many-to-many mapping table](http://stackoverflow.com/questions/11382783/entity-framework-code-first-many-to-many-mapping-table) – Red Mar 08 '16 at 04:47

2 Answers2

7

Navigation properties are your friend here. Unless you have other properties in the junction table, you don't need it. This is why there is no Product_Category in your models. So say your models are:

public class Product
{
    public Product()
    {
        this.Categories = new HashSet<Category>();
    }
    public int ProductId { get; set; }
    public string ProductName { get; set; }

    public virtual ICollection<Category> Categories { get; set; }
}

public class Category
{
    public Category()
    {
        this.Products = new HashSet<Product>();
    }

    public int CategoryId { get; set; }
    public string CategoryName { get; set; }

    public virtual ICollection<Product> Products { get; set; }
}

So now if you want all products in a category you can do something like:

var productsInCategory = db.Categorys
                      .Where(c => c.CategoryId == categoryId)
                      .SelectMany(c => c.Products);

If you do want an explicit junction tables see this: https://lostechies.com/jimmybogard/2014/03/12/avoid-many-to-many-mappings-in-orms/

Steve Greene
  • 12,029
  • 1
  • 33
  • 54
  • Thank you, this was exactly what I needed! I thought I had to do some complicated moves like adding another class like Product_Category and it was a total mess.. It was much easier than I thought. – Ezony Mar 08 '16 at 18:13
0

You have to join the product and category tables with the bridge table Product_Category to retrieve the required product info. 

using(eShopEntities db = new eShopEntities)
{
    var products = (from p in db.Product_Category 
                    join ProductTable pt on p.ID = pt.ProductID
                    join Category c on c.ID = P.CategoryID 
                    select new 
                           {
                                p.ID,
                                p.Name,
                                p.Description,
                                p.Price
                           }).ToList();
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mzh
  • 515
  • 8
  • 21
  • How do I access to Product_Category when entering "var products = (from p in db.", the Product_Category wont appear in the list. When I added the bridge table, the edmx design only showed a new relation between product and category as m-2-m, but a Product_Category class were not automatically created like the other tables. Do I've to manually create a Product_Category class? – Ezony Mar 08 '16 at 16:24
  • if Product_Category table exists in your db, than it should be shown on edmx design as well. – mzh Mar 08 '16 at 17:22
  • I added both ID in Product_Category as foreign key without any primary key, that resultet a many-to-many relation between these tables as a line like " [P-table]*----*[C-tabel] " in edmx design, but no Product_Category table was shown. Did I do it correctly or am I suppose to see Product_Category table as well? – Ezony Mar 08 '16 at 17:31
  • No, that's handled behind the scenes by EF. You don't have a DbSet for the junction table by default. – Steve Greene Mar 08 '16 at 18:22