CODEWITHSUNDEEP
c#linq
Beau D'Amore
Beau D'Amore

Reputation: 3404

Get parent objects based on child object field value

I need to return all objects that have child objects with a certain field != null.

NOTE: EpicStoryId is nullable int (as in 'int?')

I have tried:

return _context.Features
            .Where(x => x.UserStories.Any(us => us.EpicStoryId.HasValue) 
                && x.Id == Id)
            .FirstOrDefault();

and I have tried:

return _context.Features
            .Where(x => x.UserStories.Any(us => us.EpicStoryId != null) 
                && x.Id == Id)
            .FirstOrDefault();

and for good measure:

return _context.Features
            .Where(x => x.UserStories.Any(us => us.EpicStoryId.HasValue == false) 
                && x.Id == Id)
            .FirstOrDefault();

and finally:

return _context.Features
            .Where(x => x.UserStories.Any(us => us.EpicStoryId > 0) 
                && x.Id == Id)
            .FirstOrDefault();

But none of these work. It's still returning every 'Feature' with Id=Id regardless if a child has a value for EpicStoryId or not. (FYI, I checked the data and there ARE null values for some EpicStoryId's.)

sample data:

enter image description here

Upvotes: 1

Views: 522

Answers (2)

Fabjan
Fabjan

Reputation: 13676

If you need to return all objects then don't use FirstOrDefault(), use combination of .Where() and .ToList() methods :

For any of us EpicStoryIds are not null use :

return _context.Features
            .Where(x => x.Id == Id && x.UserStories.Any(us => us.EpicStoryId.HasValue))
            .ToList();

For all of us EpicStoryIds are not null you can use :

return _context.Features
            .Where(x => x.Id == Id && x.UserStories.All(us => us.EpicStoryId.HasValue))
            .ToList();

If you want to return list of UserStories and not Features, you can use : 

return _context.Features
            .Where(x => x.Id == Id)
            .SelectMany(x => x.UserStories
              .Where(us => us.EpicStoryId.HasValue))
            .ToList();

Upvotes: 0

Rahul Singh
Rahul Singh

Reputation: 21825

Any will return true i any 1 EpicStoryId has value so your your condition is failing.

All should do:-

return _context.Features
               .FirstOrDefault(x => x.UserStories.All(us => us.EpicStoryId.HasValue) 
                    && x.Id == Id);

Upvotes: 2

Related Questions