How to select List<B> from List<A> where A and B are many-to-many related using Entity Framework?

Question

I have 2 models, say Product and Category:

public class Category
{
    public Category() { }

    // primary key
    public long Id { get; set; }

    // other properties are omitted 

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


public class Product
{
    public Product() { }

    // primary key        
    public long Id { get; set; }

    // title/name
    public string Title { get; set; }

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

As you can see there is a many-to-many relationship between these models; each product can have multiple categories and for each category we can have multiple products.

The question is how can I select distinct categories associated with a list of products. I want something like this:

// making a list of products
var p = db.Products.Where(i => i.Title.Contains("Apple"));

// getting distinct categories of those products
var c = p.Select(i => i.Categories)... // stuck here

I just want to select categories of the products that their title contains a keyword. Am I doing it right at all?

Answer 1

something like:

var p = db.Products.
    Where(i => i.Title.Contains("Apple")).
    SelectMany(i => i.Categories).
    Distinct();

should do.