Alternative to using IList.Contains(item.Id) in Entity Framework for large lists?

Is there an alternative to using .Contains() to select objects in Entity Framework that exist in a specified list? Contains() works great if your list is small, however once you start getting a few thousands items the performance is terrible.

return (from item in context.Accounts
        where accountIdList.Contains(item.AccountId)
        select item).ToLi开发者_JAVA百科st();

I'm using EF 4.0, .Net Framework 4.0, and SQL Server 2005. I'm not opposed to a SQL solution either since the query that EF generates only takes a second to run on SQL for about 10k items.

---

I found an alternative that runs in about a second using a SQL Stored Procedure and a comma-delimited string for the parameter. Much better than the 5+ minutes EF was taking using .Contains()

It is run from my code using the following:

string commaDelmitedList = string.Join(",", accountIdList);
return context.GetAccountsByList(commaDelmitedList).ToList();

The StoredProcedure (simplified) looks like this:

SELECT *
FROM   Accounts as T1 WITH (NOLOCK)
INNER JOIN (
    SELECT Num FROM dbo.StringToNumSet(@commaDelimitedAccountIds, ',')
) as [T2] ON [T1].[AccountId] = [T2].[num]

And the User-Defined function dbo.StringToNumSet() looks like this:

CREATE FUNCTION [dbo].[StringToNumSet] (
 @TargetString  varchar(MAX),
 @SearchChar varchar(1)
 )
RETURNS @Set TABLE (
 num int not null
 )
AS
BEGIN
 DECLARE @SearchCharPos  int,  @LastSearchCharPos int
 SET @SearchCharPos = 0
 WHILE 1=1
 BEGIN
  SET @LastSearchCharPos = @SearchCharPos
  SET @SearchCharPos = CHARINDEX( @SearchChar, @TargetString, @SearchCharPos + 1 )
  IF @SearchCharPos = 0
  BEGIN
   INSERT @Set( num ) VALUES ( SUBSTRING( @TargetString, @LastSearchCharPos + 1, DATALENGTH( @TargetString ) ) )
   BREAK
  END
  ELSE
   INSERT @Set( num ) VALUES ( SUBSTRING( @TargetString, @LastSearchCharPos + 1, @SearchCharPos - @LastSearchCharPos - 1 ) )
 END
 RETURN
END

---

Would it be viable to just read you infomation into memory then do the searchs.

I've found that in most cases were you need to work with big amounts of data if you can get away with reading all the data into memory and then doing the lookups its much much faster.

---

Contains already gets translated to to a massive WHERE IN SQL statement, so that's not really a problem. However, you shouldn't eagerly evaluate the query, as this will execute the query everytime you call that method. Take advantage of the the nature of linq-to-entities and let the query get evaluated when you actually iterate over it.