Retrieve objects from many to many relationship

Question

I'm using Entity Framework Code First and I have the following objects in my model:

public class Category {
    [DatabaseGenerated( DatabaseGeneratedOption.Identity ), Key]
    public int CategoryId { get; set; }

    [Required, MaxLength( 128 ), Index( IsUnique = true)]
    public string CategoryName { get; set; }

    public string Description { get; set; }

    public virtual ICollection<Article> Articles { get; set; }
}

public class Article {
    [DatabaseGenerated( DatabaseGeneratedOption.Identity ), Key]
    public int ArticleId { get; set; }

    [Required, MaxLength( 128 ), Index]
    public string ArticleName { get; set; }

    public string Description { get; set; }

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

I'm implementing a method in my Data Access Layer to retrieve all Articles that are in one of the selected Categories:

IEnumerable<Article> GetArticles( int[] categoryIds );

My problem: How do I build the query expression? If I were doing this in SQL, I'd write a query like this:

SELECT a.*
FROM Articles a
JOIN ArticlesInCategories AS aic ON a.ArticleId = aic.ArticleId
JOIN Categories AS c on aic.CategoryId = c.CategoryId
WHERE c.CategoryId IN ( . . . )

So does that mean I'd write this like this?

return ( from a in context.Articles
         join c in context.Categories ON a.CategoryId = c.CatgegoryId
         where categoryIds.Contains( c.CategoryId ) );

The only problem is that the Article class doesn't have a CategoryId property.

How do I build this query?

Answer 1

You need to reverse your query and use SelectMany():

return ( from c in context.Categories
         where categoryIds.Contains( c.CategoryId ) )
        .SelectMany(category => category.Arcticles);