Deleting Multiple Records Efficiently
I'm using EF4.1 POCO.
I have two tables
[Table("Parent")]
public class Parent
{
public int ParentId { get; set; }
/.. Other fields ../
public virtual List<Child> Children{ get; set; }
}
[Table("Child")]
public class Child
{
public int ChildId { get; set; }
/.. Other fields ../
public virtual Parent Parent { get; set; }
}
They are linked in my DbContext by
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new ParentConfiguration());
}
internal class ParentConfiguration: EntityTypeConfiguration<Parent>
{
internal ParentConfiguration()
{
HasMany(r => r.Children).WithRequired(o => o.Parent);
}
}
I currently have
var parent = (from p in Parents
where p.ParentId == parentId
select p).FirstOrDefault();
while (parent.Children.Count > 0)
{
Children.Remove(parent.Items[0]);
}
Context.SaveChanges();
The SQL generated for is is horrible.
I want to create a function that dele开发者_高级运维tes all the children of the parent without selecting them all first. In SQL this can be done with a very simple SQL call.
DELETE FROM Children WHERE ParentId = @ParentId
Is this possible with EF or I'm I going to have to go back to using SQL/Stored Procs for this function.
It's not possible with EF to delete them without selecting them first. What I do is I just use normal SQL. While using EF you can still execute normal SQL code like this:
YourContext.Database.ExecuteSqlCommand(@"DELETE FROM Children WHERE ParentId= {0}",TheParendId);
You can't use EF to do that bulk operation.ORMs like EF, L2SQL, NHibernate are doing great at managing single or a few objects.Bulk operations are not considered when they designed.You need to use SQL query or SP to do that in a efficient way. DbContext exposes Database and you can use it for your SQL queries.
精彩评论