CODEWITHSUNDEEP
c#entity-frameworklinq-to-entities
Juliano
Juliano

Reputation: 2569

IQueryable<>.ToString() too slow

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.

Upvotes: 3

Views: 2478

Answers (3)

phil soady
phil soady

Reputation: 11308

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....

Upvotes: 1

Juliano
Juliano

Reputation: 2569

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.

Upvotes: 1

Kenn
Kenn

Reputation: 2479

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?

Upvotes: 2

Related Questions