boolean Contains does not support conversion to SQL

Question

I got error boolean Contains does not support conversion to SQL

I have array of the rooms id which should be removed

I want to remove with this query but I can't succeed. How may I do this?

var rooms = from rooms in entity.Rooms 
            where myArray.contains(rooms.RoomID) select rooms;

What LINQ technology are you using? EF? L2S? – Gabe Jun 02 '12 at 11:53 — Gabe, Jun 02 '12 at 11:53

score 1 · Accepted Answer · edited Jun 02 '12 at 12:37

1

You are trying to mix the call. Array.Contains happens on client, but you are putting it into the middle of the expression that is sent to the sql server.

You need to either send the whole lot to sql for the comparison, or return the rows from sql and compare them locally.

rooms = from room in entity.Rooms.AsEnumerable()
        where myArray.contains(room.RoomID)
        select room;

or using lambda syntax:

rooms = entity.Rooms.AsEnumerable.Where(room => myArray.contains(room.RoomID));

edited Jun 02 '12 at 12:37

Gabe

84,912
12
139
238

answered Jun 02 '12 at 11:55

Preet Sangha

64,563
18
145
216

Be aware though that you are going to be pulling all the rooms back into memory and then filtering them, ie reading out the whole table. This could be ok for a small number of rooms. – Robert Wagner Jun 02 '12 at 12:32

Tono Nam · Answer 2 · 2012-06-02T12:26:55.733

0

   var roomslist =myArray.ToList();
   var roomsTemp = entity.Rooms;
   var rooms=roomsTemp.Where(x=>roomslist.Contains(x.RoomId)).ToList();

If that does not work then this should work but i dont recomend using this:

   var roomslist =myArray.ToList();
   var roomsTemp = entity.Rooms.ToList();
   var rooms=roomsTemp.Where(x=>roomslist.Contains(x.RoomId)).ToList();

edited Jun 02 '12 at 12:26

answered Jun 02 '12 at 11:54

Tono Nam

34,064
78
298
470

score 0 · Answer 3 · edited May 23 '17 at 12:20

It's been a while since L2S, but here goes. I could of sworn int[].Contains was built in.. are you using GUIDs by chance? Maybe I'm thinking of EF. Does this help: Linq query with Array in where clause?

If you were to write a SQL Procedure for that, you would pass in a CSV string of the IDs. Using the same idea you could (warning off the top of my head):

var ids = "," + string.Join(",",myArray) + ",";
var rooms = from rooms in entity.Rooms 
        where SqlMethods.Like("," + rooms.RoomID + ",", ids) select rooms;

Maybe ids.Contains("," + rooms.RoomID + ",") would work as that has a translation to SQL Built in (for strings)

Alternate is to write your own UDF that breaks the CSV into a table and does a SELECT EXISTS. There should be plenty of examples of this as it's the way you do it with Stored Procedures. Then you could:

var ids = "," + string.Join(",",myArray) + ",";
var rooms = from rooms in entity.Rooms 
        where MyMethods.Contains(ids,rooms.RoomID) select rooms;

boolean Contains does not support conversion to SQL

3 Answers3