CODEWITHSUNDEEP
c#entity-framework
Magpie
Magpie

Reputation: 7173

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

Upvotes: 3

Views: 345

Answers (2)

marvelTracker
marvelTracker

Reputation: 4969

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.

Upvotes: 1

Kevin Cloet
Kevin Cloet

Reputation: 2976

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);

Upvotes: 4

Related Questions