tsdev tsdev - 2 months ago 11
SQL Question

filter data with entity framework

I'm trying to get the right data

Rooms table

Id | Name
1 Room1
2 Room2


Resources table

Id | Name
1 Resource1
2 Resource2
3 Resource3


RoomResources table

Id | RoomId | ResourceId
1 1 1
2 1 2
3 1 3
4 2 2
5 2 3


I want select a room with Resource1 and Resource2
I'm using this code

int[] ids = sResources.Split(',').Select(s => int.Parse(s)).ToArray();

rooms = from r in context.Rooms
where r.Area.Office.Id == officeId
&& r.MaximumPeople >= numberOfPeople
&& r.RoomResources.Any(s => ids.Contains(s.ResourceId))
select r;


but it return Room1 and Room2 and the result should be Room1

Answer

Maybe this?

int[] ids = sResources.Split(',').Select(s => int.Parse(s)).ToArray();

rooms = from r in context.Rooms
    where r.Area.Office.Id == officeId
    && r.MaximumPeople >= numberOfPeople
    && ids.All(i => r.RoomResources.Any(s => s.ResourceId == i)) // try this here
    select r;
Comments