EF query where List contains a lot of elements

Question

I'm doing an EF query with a list which contains many elements and I seem to be coming across the same issue listed here (where SQL Server throws an error because there are too many elements in the IN statement). I was wondering if there is another way to do it.

Would ExecuteStoreQuery or ExecuteStoreCommand work if the command set up a temporary table?

Thanks

score 1 · Answer 1 · answered May 03 '13 at 13:58

Yep, the best way to select from a large list of keys is to use a temp table.

http://explainextended.com/2009/08/18/passing-parameters-in-mysql-in-list-vs-temporary-table/

If you're using MsSQL and C# then SqlBulkCopy will get your list of keys in the fastest.

    public void bulkCopy(String tmpTableName, DataTable table)
    {
        using (SqlBulkCopy bulkCopy =
                       new SqlBulkCopy((SqlConnection)connection))
        {
            bulkCopy.DestinationTableName = tmpTableName;
            bulkCopy.WriteToServer(table);
        }
    }

Then have a stored procedure to match to the temp table by key.

score 0 · Accepted Answer · answered May 03 '13 at 13:45

If that list data comes from a database query, do not call .ToList() on your source query. Instead pass the IQueryable to the query.

var statuses = context.Statuses.Where(o => o.IsActive).Select(o => o.Id);
var data = context.Orders.Where(o => statuses.Contains(o.StatusId));

Alternatively you can use something like this

var sessionId = Guid.NewGuid();
foreach (var s in statusList)
    insert into SearchValues(sessionId, s); // pseudo code

var statuses = context.SearchValues.Where(o => o.SessionId == sessionId).Select(o => o.Id);
var data = context.Orders.Where(o => statuses.Contains(o.StatusId));

delete from SearchValues where SessionId == @sessionId // pseudo code

You might want to do the insert and delete using SQL queries (and not EF context operations) for optimal performance.

EF query where List contains a lot of elements

2 Answers2