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

Question

Is there an alternative to using .Contains()to select objects in the Entity Framework that exist in the specified list? Contains()works great if your list is small, however, as soon as you begin to receive several thousand items, the performance will be terrible.

return (from item in context.Accounts
        where accountIdList.Contains(item.AccountId)
        select item).ToList();

I use EF 4.0, .Net Framework 4.0 and SQL Server 2005. I am not against the SQL solution, as the query that generates EF takes just a second to work on SQL for about 10,000 elements.

Answer 1

, , SQL - . , 5+ , EF .Contains()

, :

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

StoredProcedure () :

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

User-Defined dbo.StringToNumSet() :

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

Answer 2

, .

, , , .

Answer 3

Containsalready translated into a massive WHERE INSQL statement, so this is not a problem. However, you should not look forward to evaluating the request, as this will execute the request each time you call this method. Take advantage of the nature of linq-to-entity and let the query be evaluated when you actually iterate over it.