IQueryable where clause

Question

Was difficult for me to find a fitting title for this post. But I have the following:

IArticleRepository articleRepo = unitOfWork.ArticleRepository;
List<Article> articles = new List<Article>(
                         articleRepo.GetAll()
                         .Where(a => a.Title == searchTerm)
                         //.Where(a => a.Categories.Contains(Category.))
                         .OrderByDescending(a => a.CreatedDate));  

So some explanation: An article has , among other things, a Title and a CreateDate, and filtering through those is easy. But an article also has categories associated with it. So an article has an array property of type Category. Type Category has a property called CategoryId of type int.

So in my code where it's commented out, I'm trying to select an article, which has a category associated with it, who's CategoryId is equal to.. say 4.

But I'm finding it quite difficult to express this in my C# syntax. I'm also new to C# so that's not helping either.

Solution 1

You don't need to write two Where clauses; just add another condition to your first Where. The second condition should use Any function to search for the categories you're looking for.

IArticleRepository articleRepo = unitOfWork.ArticleRepository;
List<Article> articles = new List<Article>(
                     articleRepo.GetAll()
                     .Where(a => a.Title == searchTerm &&
                                 a.Categories.Any(c => c.CategoryID == 4))
                     .OrderByDescending(a => a.CreatedDate));  

For multiple categories, suppose you have your CategoryIDs in an int[] or List<int> named MyCatIDsList. They you can change the categories clause in the above query to this:

              a.Categories.Any(c => MyCatIDsList.Contains(c.CategoryID))

Solution 2

There is an alternative syntax when using LINQ queries, which is more like SQL. The code above is correct, but you may find this version more concise:

int categoryId = 4
IArticleRepository articleRepo = unitOfWork.ArticleRepository;
var articlesQuery = from article in articleRepo.GetAll()
                    from category in article.Categories

                    where category.CategoryId == categoryId
                    where article.Title == searchTerm
                    orderby article.CreatedDate descending

                    select article

List<Article> articles = articlesQuery.ToList();

Or its more common to do these all together in one step:

int categoryId = 4
List<Article> articles =   (   
                             from article in articleRepo.GetAll()
                             from category in article.Categories

                             where category.CategoryId == categoryId
                             where article.Title == searchTerm
                             orderby article.CreatedDate descending

                             select article
                           ).ToList()

Solution 3

You don't need to create a new list and you can use several where expressions in one Where clause. Can you try the following code:

List<Article> articles = articleRepo.GetAll()
                     .Where(a => a.Title == searchTerm && a.Categories.Contains(Category)).OrderByDescending(a => a.CreatedDate)).ToList();