CODEWITHSUNDEEP
c#sql-serverlinqlinq-to-sql
Stan
Stan

Reputation: 26511

Select all categories who has items that are active in them?

I didn't knew what title should I put, feel free to edit of course, English not my native language.

I have linked LINQ2SQL database that has Items and Categories in it. Items table has field IsActive.

How can I select all categories that has items in them who has IsActive set to true? I can select all items very easily var items = _db.Items.Where(x => x.IsActive == true); but can I somehow now select all categories that are associated with these items that I just selected?

My knowledge of LINQ is very limited so I have no idea how to do this.

Upvotes: 2

Views: 203

Answers (3)

Ani
Ani

Reputation: 113412

Assuming the appropriate association property is present, you can do:

var activeCategories = from category in _db.Categories
                       where category.Items.Any(item => item.IsActive)
                       select category;

If you must do a join, try something like:

var activeCategories = from category in _db.Categories
                       join item in _db.Items on category.Id equals item.CategoryId
                                                  into categoryGroup
                       where categoryGroup.Any(i => i.IsActive)
                       select category;

(or)

var activeCategories = from category in _db.Categories
                       join item in _db.Items.Where(i => i.IsActive)
                                 on category.Id equals item.CategoryId
                                 into categoryGroup
                       where categoryGroup.Any()
                       select category;

(or)

var activeCategories = from category in _db.Categories
                       where _db.Items
                                .Where(i => i.IsActive)
                                .Select(i => i.CategoryId)
                                .Contains(category.Id)
                       select category;

Upvotes: 0

Pranay Rana
Pranay Rana

Reputation: 176906

Try something like as below

For each items active

var items = _db.Cateogry.Where(x => x.Items.All( y=>y.IsActive == true));

for any item active 

var items = _db.Cateogry.Where(x => x.Items.Any( y=>y.IsActive == true));

Upvotes: 1

Jon Hanna
Jon Hanna

Reputation: 113272

Assuming each item has a CategoryID or similar, then:

from c in db.Categories
  where db.Items.Any(it => it.CategoryID == c.ID && it.IsActive)
  select c;

It's worth noting:

(from c in db.Categories join it in db.Items
  on c.ID equals it.CategoryID
  select c).Distinct();

Which is a bit needless in this case, but there are common variants upon this latter that gives you more than you can do with the former.

Upvotes: 0

Related Questions