Tuesday, 29 March 2011

EF4 and bulk delete

When you want to delete records in EF4 typically you load the objects and delete them in the database context. This is a little slow. Microsoft recommends in such cases to use a stored procedure which I find not so cool. Here is an alternative way of doing this:

public void DeleteAll(IQueryable clause) where TEntity : class
string sqlClause = GetClause(clause);
this.context.ExecuteStoreCommand(string.Format(CultureInfo.InvariantCulture, "DELETE {0}", sqlClause));

private string GetClause(IQueryable clause) where TEntity : class
string snippet = "FROM [dbo].[";

string sql = ((ObjectQuery)clause).ToTraceString();
string sqlFirstPart = sql.Substring(sql.IndexOf(snippet));

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

// The above code works well for clauses like
// IQueryable clause = this.databaseContext.Query().Where(v => v.ModelRunId == 1);
// BUT as soon as you replace "v => v.ModelRunId == 1" with "v => v.ModelRunId == myId" you will get sql looking like:
// "SELECT \r\n[Extent1].[LOSS_FILE_ID] AS [LOSS_FILE_ID], \r\n ...WHERE [Extent1].[MODEL_RUN_ID] = @p__linq__0"
// To evaluate @p__linq__0 you need to use the code below:
foreach (ObjectParameter Param in (((ObjectQuery)clause)).Parameters) {
switch (Param.ParameterType.FullName)
case "System.Int32":
sqlFirstPart = sqlFirstPart.Replace("@"+Param.Name, Param.Value.ToString());
case "System.String":
sqlFirstPart = sqlFirstPart.Replace("@"+Param.Name, "'"+Param.Value.ToString().Replace("'","''")+"'");

throw new NotImplementedException("Parameter conversion for this type is not yet implemented");
return sqlFirstPart;

Now the delete command can be called using a fluent linq clause in the following way:

IQueryable clause = this.databaseContext.Query().Where(v => v.ModelRunId.Value == ModelRunId);