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.

score 2 · Answer 1 · edited May 23 '17 at 11:58

2

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?

edited May 23 '17 at 11:58

Community

1
1

answered Dec 05 '12 at 03:23

Kenn

2,379
2
29
38

Would ToList().AsQueryable() only cache the clause resolution for that specific set of elements? If so it wouldn't be of much help. – Juliano Dec 05 '12 at 04:36

score 1 · Accepted Answer · answered Feb 19 '13 at 13:29

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.

score 1 · Answer 3 · answered Feb 19 '13 at 15:30

There is a EF pattern that works Ok.

it uses projection. to return ONLY keys from DB. (projections are not added to context, So this is pretty quick. Then You build the context with KEY only stub POCOs, and light the fuse....

basically.

 var deleteMagazine = Context.Set<DeadMeat>.Where(t=>t.IhateYou == true).Select(t=>t.THEKEY).toList

//Now instantiate a dummy POCO with KEY only for the list,
foreach ( var bullet in deleteMagazine)
{
context.Set<deadmeat>.attach(bullet);
context.set<deadmeat>.remove(bullet);
 // consider saving chnages every 1000 records .... performance, trial different values
if (magazineisEmpty)  // your counter logic here :-)
  context.SaveChanges
}
// shoot anyone still moving
context.SaveChanges

check SQL server profiler....

IQueryable<>.ToString() too slow

3 Answers3