Queries with local collections are not supported LINQ

Question

everyone! I'm trying to do a rather simple LINQ query to find available rooms in a hotel. (That is, find a available room from a pool of rooms, and check that there are no pending cleaning etc in the room).

But when I try to execute the third query, I get the exception you see in the title. I don't actually get the exception when I execute it, but when I try to use the "unfinishedTasksInPool" variable.

I tried turning the "unfinishedTasksInPool" into a list, too see if that would help, but whenever I try to use "unfinishedTasksInPool", I get the exception.

EDIT : Whenever I exclude "availableRoomsFromPool.Contains(tasks.roomId" in the where clause in the third query, everything seems to work normally. But that doesn't exactly solve the problem tho.

var pendingReservation = database.Reservations.Where(res => res.reservationID == resId).First();

var reservationsInSameGroup = from otherReservations in database.GetTable<Reservation>()
                              where (otherReservations.beds == pendingReservation.beds
                                    && otherReservations.rank == pendingReservation.rank
                                    && otherReservations.roomID != null)
                              select otherReservations.roomID;


var availableRoomsFromPool = from rooms in database.GetTable<Room>()
                     where (!reservationsInSameGroup.Contains(rooms.roomId)
                     && rooms.beds == pendingReservation.beds
                                    && rooms.roomRank == pendingReservation.rank)
                     select rooms.roomId;

var unfinishedTasksInPool = from tasks in database.GetTable<HotelTask>()
                         where (availableRoomsFromPool.Contains(tasks.roomId) 
                         && tasks.taskStatus < 2)
                         select tasks.roomId;

Gert Arnold Gert Arnold · Accepted Answer · 2016-03-03T00:21:27

It's a LINQ-to-SQL restriction. You can use local sequences in queries (as long as you use them in Contains), but you can't use a local sequence that itself is the result of a query using another local sequence.

So it's alright to do...

var availableRoomsFromPool = (from ....).ToArray();

...because the query contains one local sequence (reservationsInSameGroup).

But...

var unfinishedTasksInPool = (from ...).ToArray();

...throws the exception.

The solution is to use the result of var availableRoomsFromPool = (from ....).ToArray(); in the third query, because that reduces availableRoomsFromPool to one local sequence.

Queries with local collections are not supported LINQ

1 Answers