Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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).ToList();

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.

like image 433
Rachel Avatar asked Nov 13 '22 20:11

Rachel


1 Answers

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
like image 177
Rachel Avatar answered Dec 24 '22 13:12

Rachel