I'm trying to perform a DELETE using LINQ that will generate a single query.
Here's how I'm doing it:
// NorthwintEntities is an ADO.NET Entitity Data Model
var northwindEntities = new NorthwindEntities();
northwindEntities.Order_Details.Delete(o => o.Order_ID == 11076);
Here's my Extension:
public static class EntityExtensions
{
private static Regex rxTableName = new Regex(@"^FROM\s+(?<table>\[[^\]]*\](\.\[[^\]]*\]){0,2})\s+AS\s+(?<alias>\[[^\]]*\])", RegexOptions.Multiline);
public static void Delete<T>(this ObjectSet<T> entity, Expression<Func<T, bool>> expression) where T : EntityObject
{
var selectQuery = entity.Where(expression).Select(x => 1);
string selectQueryString = ((ObjectQuery)selectQuery).ToTraceString();
string deleteQueryString = ConvertSqlSelectToDelete(selectQueryString);
entity.Context.ExecuteStoreCommand(deleteQueryString);
}
private static string ConvertSqlSelectToDelete(string selectQuery)
{
if (selectQuery.IndexOf(" JOIN ") > -1)
{
throw new Exception("Query with JOIN is not supported: " + selectQuery);
}
Match match = rxTableName.Match(selectQuery);
if (!match.Success)
{
throw new Exception("Unable to convert SELECT: " + selectQuery);
}
string deleteQuery = "DELETE \r\n" + selectQuery.Substring(match.Index);
deleteQuery = deleteQuery.Replace(match.Groups["alias"].Value + ".", "");
deleteQuery = deleteQuery.Replace("AS " + match.Groups["alias"].Value, "");
return deleteQuery;
}
}
This works, but I have a few comments.
So my questions is: Is there an easier way to do this? If so, what is it?
Any help at all will be appreciated.
Install Entity Framework Extended Library (PM> Install-Package EntityFramework.Extended)
Import EntityFramework.Extensions in your code
Delete records specified by inner query
context.Orders.Where(o=>o.User_ID == 1).Delete();
Deletes all records inside Orders with userID = 1
One way to have a batch delete is setting ON CASCADE DELETE on foreign keys. You need to set CASCADE on relation in designer and Set ON CASCADE DELETE on relation in database.
To delete Products navigation property from Order EF will do (number of Products properties)+1 statements to database.
I prefer to do it:
var order = context.Orders.Include(p=>p.Products).Where(o=>o.Order_ID == 11076);
foreach(Product product in order.Products.ToList())
{
context.Entry(product).State = EntityState.Deleted;
}
context.Entry(order ).State = EntityState.Deleted;
context.SaveChanges();
Hope it helps.