How to filter database record by a IList in EF

Question

The db contains product and color table with a many-to-many relation. So, The product has an IList<Colors> attribute. For example, I create an IList that contains two colors and I want get the products that this Colors contains other IList. Is that possible or I should get all products first and then filter them by a foreach?

Product :

public class Product
{
    public Product()
    {
        Colors = new List<Color>();
    }
    public int Id { get; set; }
    public string Name { get; set; }
    public string Code { get; set; }
    .
    .
    .
    public virtual ICollection<Color> Colors { get; set; }

}

Color:

public class Color
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string HexCode { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

Now I want the products that their color id is 2 or 3, These numbers are dynamic

Answer 1

Suppose you have the color Ids in a list colorIds then you can do

var query = from p in Products
            where p.Colors.Any(c => colorIds.Contains(c.Id))
            select p

This will return products having at least the requested colors. If you need products having only these colors, you can change Any into All.