c# - Linq query to find all orphan products

Question

I have three models for a product and product category system:

[Products]
Id
Title
Info
Price

[ProductCategories]
Id
ParentId
SortOrder
Title

[ProductsInCategories]
Id
ProductId
ProductCategoryId
SortOrder

Now, I want to make a list of all products which doesn't have an entry in the ProductsInCategories table. I.e., all orphan products. How can I do that in linq?

Answer 1

You can use !Any to get only those without an entry in ProductsInCategories:

List<Product> productList = db.Products
    .Where(p => !db.ProductsInCategories.Any(pc => p.Id == pc.ProductId))
    .ToList();