IQueryable<>.ToString() too slow

Question

I'm using BatchDelete found on the answer to this question: EF Code First Delete Batch From IQueryable<T>?

The method seems to be wasting too much time building the delete clause from the IQueryable. Specifically, deleting 20.000 elements using the IQueryable below is taking almost two minutes.

context.DeleteBatch(context.SomeTable.Where(x => idList.Contains(x.Id)));

All the time is spent on this line:

    var sql = clause.ToString();

The line is part of this method, available on the original question linked above but pasted here for convenience:

private static string GetClause<T>(DbContext context, IQueryable<T> clause) where T : class
{
    const string Snippet = "FROM [dbo].[";

    var sql = clause.ToString();
    var sqlFirstPart = sql.Substring(sql.IndexOf(Snippet, System.StringComparison.OrdinalIgnoreCase));

    sqlFirstPart = sqlFirstPart.Replace("AS [Extent1]", string.Empty);
    sqlFirstPart = sqlFirstPart.Replace("[Extent1].", string.Empty);

    return sqlFirstPart;
}

I imagine making context.SomeTable.Where(x => idList.Contains(x.Id)) into a compiled query could help, but AFAIK you can't compile queries while using DbContext on EF 5. In thesis they should be cached but I see no sign of improvement on a second execution of the same BatchDelete.

Is there a way to make this faster? I would like to avoid manually building the SQL delete statement.

Answer 1

The IQueryable isn't cached and each time you evaluate it you're going out to SQL. Running ToList() or ToArray() on it will evaluate it once and then you can work with the list as the cached version.

If you want to preserve you're interfaces, you'd use ToList().AsQueryable() and this would pass in a cached version.

Related post. How do I cache an IQueryable object?

Answer 2

It seems there is no way to cache the IQueryable in this case, because the query contains a list of ids to check against and the list changes in every call.

The only way I found to avoid the two minute delay in building the query every time I had to mass-delete objects was to use ExecuteSqlCommand as below:

var list = string.Join("','", ids.Select(x => x.ToString()));
var qry = string.Format("DELETE FROM SomeTable WHERE Id IN ('{0}')", list);
context.Database.ExecuteSqlCommand(qry);

I'll mark this as the answer for now. If any other technique is suggested that doesn't rely on ExecuteSqlCommand, I'll gladly change the answer.