CODEWITHSUNDEEP
c#linqentity-framework
ChristiaanV
ChristiaanV

Reputation: 5541

Nested linq query with contains

I have been working on a linq query for some hours but not getting it to work.

I'm trying to compare some list of data to another list of data and I don't see why it's not working as expected.

I have a DbSet and Room has a list of Facilities and every Facility has it's own Id.

So my method takes a list of facilityId's (IEnumerable) and I want to return all rooms that matches the list of facilities.

So I thought my query could look like this:

var rooms = DbSet.Include("Facility").Where(room => room.Facility.All(facility => facilityIds.Contains(facility.Id)));

But this query is giving me other results than expected. Anyone can give me hand with fixing this query?

Update sample

            var filterIds = new int[] {1,2};

            var facilities1 = new List<Facility> {new Facility() {Id = 1}};
            var facilities2 = new List<Facility> {};
            var facilities3 = new List<Facility> {new Facility() {Id = 1}, new Facility() {Id = 2}};

            var basicData = new List<Room>()
            {
                new Room() {Id = 1, Facility = facilities1},
                new Room() {Id = 2, Facility = facilities2},
                new Room() {Id = 3, Facility = facilities1},
                new Room() {Id = 4, Facility = facilities3},
                new Room() {Id = 5, Facility = facilities1},
                new Room() {Id = 6, Facility = facilities1},
            };

            var result = basicData.Where(r => (!filterIds.Any() || r.Facility.All(f => filterIds.Contains(f.Id))));

This query is giving me 6 results and I'm expecting only the room with Id 4.

Upvotes: 0

Views: 852

Answers (1)

Jcl
Jcl

Reputation: 28272

Would this work?

rooms.Where(
   room =>    room.Facilities.Any() 
           && filterIds.All(x => room.Facilities.Any(o => o.Id == x))
);

Update Updated fiddle with your sample data. Returns room 4 as expected for facilities 1,2. Returns rooms 1,3,4,5,6 for just facility 1:

Fiddle: https://dotnetfiddle.net/b24FbF

Upvotes: 3

Related Questions