Problem with Contains

Question

Hello there,

I have a little problem which i can't solve. I want to use an SQL-In-Statement in Linq. I've read in this Forum and in other Forums that I have to use the .Contains (with reverse-thinking-notation :-)). As input i have a List of Guids. I first copied them into an array and then did something like that :

datatoget = (from p in objectContext.MyDataSet
             where ArrayToSearch.Contains(p.Subtable.Id.ToString())
             select p).ToList();

datatoget is the result in which all records matching the Subtable.Id (which is a Guid) should be stored. Subtable is a Detail-Table from MyData, and the Id is a Guid-Type. I've tried several things (Convert Guid to String, and then using .Contains, etc), but I always get an Exception which says :

'Linq to Entities' doesn't recognize the Method 'Boolean Contains(System.Guid) and is not able to Translate this method into a memory expression. (Something like that, because I'm using the German Version of VS2008)

I am using L2E with .NET 3.5 and am programming in C# with VS 2008.

I've read several Examples, but it doesn't work. Is it perhaps because of using Guid's instead of strings ? I've also tried to write my own compare-function, but I don't know how to integrate it so that .NET calls my function for comparing.

Answer 1

Could you use an Any() on the arrayToSearch?

 datatoget = objectContext
              .MyDataSet
              .Where(p => arrayToSearch.Any(i => i == p.Subtable.Id.ToString()) 
              .ToList();

Kindness,

Dan

Answer 2

I would try these two approaches:

First approach: don't convert anything. This assumes that p.Subtable.Id is defined as a GUID in the database.

Guid[] guids = GetGuids();

var dataToGet = (from p in objectContext.MyDataSet
                 where guids.Contains(p.Subtable.Id)
                 select p).ToList();

Second approach: convert all the GUIDs to strings first.

string[] guids = GetGuids().Select(g => g.ToString()).ToArray();

var dataToGet = (from p in objectContext.MyDataSet
                 where guids.Contains(p.Subtable.Id.ToString())
                 select p).ToList();

If neither of these approaches work, then we probably need to see more info about the problem, such as the schema of the database, the shape of the entities, etc.

Answer 3

Hello there again,

thanks for spending your time upon my question. The Link from Robert Havery made it :) As I can see the .Contains() doesn't work (well) with Linq for Entities and .NET3.5. There is no possibility to do it without writing an Extension Method. I've tried a little bit with LinqPad and when using Linq 2 SQL, no problem with .Contains(). It did it as promised. And with Linq to Entities there was no promise, except that I always got the Exception (but this is also a promise :)).

So special thanks to Robert Havery, and all others who helped me.