How can i Select many with contains in LINQ?

Question

My Product class is

public class Product
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public virtual ICollection<ProductColor> ProductColors { get; set; }
}

The Color class

public class Color
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ColorID { get; set; }
    public string ColorName { get; set; }
    public virtual ICollection<ProductColor> ProductColors { get; set; }
}

and the intermediate class for creating many to many relationship

public class ProductColor
{
    public int ProductID { get; set; }
    public int ColorID { get; set; }

    public virtual Product Product { get; set; }
    public virtual Color Color { get; set; }
}

Supposing my product model contains

ProductID    ProductName
1            Product 1
2            Product 2
3            Product 3

My color model contains

ColorID  ColorName
1        Red
2        Green
3        Blue

And the intermediate model contains

ProductID ColorID
1         1
1         2
2         3

How can i write a Linq query to get all the colors for each Product in a list?

You have many-to-many relationship. With EF you do not have to include `ProductColor` entity to your context. Then you won't require any query to get what you want. — xZ6a33YaYEfmv, Oct 04 '15 at 17:23
@ieaglle I am really stuck with this. I cannot include the intermediate entity in my context. — OrElse, Oct 04 '15 at 17:26
what do you mean? `ProductColor` is your linking entity and it seems to be already added (judging from your code). — xZ6a33YaYEfmv, Oct 04 '15 at 17:28

score 2 · Accepted Answer · answered Oct 04 '15 at 17:35

This should do what you need:

var res = Products.Select(s => new{ Product = s, Colors = s.ProductColors.Select(m => m.Color) }).ToList();

This will produce anonymous type with two properties: Product and array of Colors that this product has.

Or you can remove ProductColor entity, change Product to:

public class Product
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public virtual ICollection<Color> Colors { get; set; }
}

Color to:

public class Color
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ColorID { get; set; }
    public string ColorName { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

And then you will have already Products with their Colors.

I guess i am doing something terribly wrong, since i get the error... 'Models.Products' does not contain a definition for 'ProductColors' and no extension method 'ProductColors' accepting a first argument of type 'Models.Products' could be found (are you missing a using directive or an assembly reference? It looks that the intermediate table is hidden within the entity framework. Anyway, the problem is solved and your answer is really helpfull. — OrElse, Oct 04 '15 at 17:41
I actually solved the issue, please check the answer i posted. I just do not quite understand why the intermediate table is "hidden". I better start reading with the link you posted. Thank you for your effort. Have a nice day :) — OrElse, Oct 04 '15 at 17:47

score 0 · Answer 2 · answered Oct 04 '15 at 17:36

0

Ok. This is what i was looking for

var Colors =  context.Products.SelectMany(p => p.Colors);

and filtering by ProductID

var Colors =  context.Products.Where(p => p.ProductID == 1).SelectMany(p => p.Colors);

answered Oct 04 '15 at 17:36

OrElse

9,709
39
140
253

How can i Select many with contains in LINQ?

2 Answers2