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.
精彩评论