locked
EF Code First -- cannot get EF to delete rows in database RRS feed

  • Question

  • I have two classes that EF creates in the db:

    public class User
    { 
        public Guid ID { get; set; } 
        public string LastName { get; set; }
        public string FirstName { get; set; }
    
        public virtual List<Phone> Phones { get; set; }
    }
    
    public class Phone
    {
      public int ID { get; set; }
      public string PhoneNumber { get; set; } 
    
      public virtual User Owner { get; set; }
    }
    

    I would like to delete the user's phones thus:

    someUser.Phones.Clear();
    db.SaveChanges();
    

    But all that happens in the db is the Owner field of each phone row gets set to NULL. This disconnects them from the user but it doesn't delete them and I wind up with lots of orphan phone rows.

    How can I get EF to delete a phone row from the db when it is removed from its user?

    Thanks,

    Eric

     

    Wednesday, April 6, 2011 2:07 PM

Answers

  •  

    Hi Eric,

    Welcome!

    According to your description, the Clear() method is used to drop the relationship between to tables, I repro your scenario and add some records in my database. If you want to delete the rows, I think you could do in this way:

    //find the user then delete his phones from database.
     User user = context.Users.Find(new Guid("FBF92CA6-72B0-48CB-9A38-FDFBB8CF6EF8"));
            List<Phone> phone = user.Phones.ToList();
            foreach(var p in phone)
            {
              context.Entry(p).State = EntityState.Deleted;
            }
            context.SaveChanges();
    
    

    You can also use ExecuteSqlCommand to delete you rows:

    context.Database.ExecuteSqlCommand(@"delete Phones where Owner_ID='FBF92CA6-72B0-48CB-9A38-FDFBB8CF6EF8'");
            context.SaveChanges();
    
    

      

    ExecuteSqlCommand: you can refer here:  http://blogs.msdn.com/b/adonet/archive/2011/02/04/using-dbcontext-in-ef-feature-ctp5-part-10-raw-sql-queries.as px

     

    Please have a try and let me know your feedback.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by EricNelson Friday, April 8, 2011 9:06 PM
    Thursday, April 7, 2011 7:13 AM

All replies

  •  

    Hi Eric,

    Welcome!

    According to your description, the Clear() method is used to drop the relationship between to tables, I repro your scenario and add some records in my database. If you want to delete the rows, I think you could do in this way:

    //find the user then delete his phones from database.
     User user = context.Users.Find(new Guid("FBF92CA6-72B0-48CB-9A38-FDFBB8CF6EF8"));
            List<Phone> phone = user.Phones.ToList();
            foreach(var p in phone)
            {
              context.Entry(p).State = EntityState.Deleted;
            }
            context.SaveChanges();
    
    

    You can also use ExecuteSqlCommand to delete you rows:

    context.Database.ExecuteSqlCommand(@"delete Phones where Owner_ID='FBF92CA6-72B0-48CB-9A38-FDFBB8CF6EF8'");
            context.SaveChanges();
    
    

      

    ExecuteSqlCommand: you can refer here:  http://blogs.msdn.com/b/adonet/archive/2011/02/04/using-dbcontext-in-ef-feature-ctp5-part-10-raw-sql-queries.as px

     

    Please have a try and let me know your feedback.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by EricNelson Friday, April 8, 2011 9:06 PM
    Thursday, April 7, 2011 7:13 AM
  • Hi Alan,

    Thanks for your thoughtful response. I've marked it as an answer since it does solve the problem, but I'm afraid it's raised more questions. The first one is, "Why do I have to reach down into EF's private business to get the phones deleted; why isn't EF smart enough to figure that out on its own?"

    The second question is like the first: How am I supposed to know what things EF can do on its own and what things it can't." I have a Users in Groups example where the pattern below deletes the affected rows from the UserInGroups table. The pattern works for this example, but it doesn't work for the deleting the user's phones in the above example. I'm confused!!!

    someGroup.UsersInMyGroup.Clear();
    db.SaveChanges();
    

    The whole example is below. Just drop it into Program.cs of a windows command line project & set breakpoints on the "i = <number>;' lines to see the effect.

    Thanks for any insight.

    Eric

     

    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.Data;
    using System.Data.Entity;
    using System.Data.Metadata.Edm;
    using System.Data.Objects;
    using System.Linq;
    
    namespace LearnEFCodeFirst
    {
    
      public class User
      {
        public int ID { get; set; }
        public string UserName { get; set; }
        public virtual List<UserInGroup> GroupsIamIn { get; set; }
      }
    
      public class UserInGroup
      {
        public int ID { get; set; }
        public virtual User TheUser { get; set; }
        public virtual Group TheGroup { get; set; }
      }
    
      public class Group
      {
        public int ID { get; set; }
        public string GroupName { get; set; }
        public virtual List<UserInGroup> UsersInMyGroup { get; set; }
      }
    
      public class LearnContext : DbContext
      {
        public DbSet<User> Users { get; set; }
        public DbSet<Group> Groups { get; set; }
        public DbSet<UserInGroup> UsersInGroups { get; set; }
      }
    
      public class Result
      {
        public string GroupName { get; set; }
        public string UserName { get; set; }
      }
    
      class Program
      {
    
        static void ShowMembership()
        {
          var db = new LearnContext();
          var qry = db.Database.SqlQuery<Result>(
    @"SELECT Groups.GroupName, Users.UserName
    FROM     Users INNER JOIN 
               UserInGroups ON Users.ID = UserInGroups.TheUser_ID INNER JOIN 
               Groups ON UserInGroups.TheGroup_ID = Groups.ID 
    ORDER BY GroupName, UserName").ToList();
          qry.ForEach(x => Console.WriteLine("{0} {1}", x.GroupName, x.UserName));
          Console.WriteLine();
        }
    
        static void Main(string[] args)
        {
          Database.SetInitializer<LearnContext>(
            new DropCreateDatabaseAlways<LearnContext>());
    
          using (var db = new LearnContext())
          {
            var g = new List<Group>
            {
              new Group { GroupName = "Drifters"},
              new Group { GroupName = "Pleasers"},
              new Group { GroupName = "Slackers"}
            };
            g.ForEach(x => db.Groups.Add(x));
    
            var u = new List<User>
            {
              new User { UserName = "Axel" },
              new User { UserName = "Biff" },
              new User { UserName = "Chip" }
            };
            u.ForEach(x => db.Users.Add(x));
    
            int n = db.SaveChanges();
    
            // put every user in every group
            u.ForEach(x => g.ForEach(y => db.UsersInGroups.Add(new UserInGroup { TheUser = x, TheGroup = y })));
            n = db.SaveChanges();
            ShowMembership();
    
            //// find a group and see if the associated users show up (they do)
            //var m = db.Groups.FirstOrDefault(x => x.name == "Slackers");
    
            //// find a user and see if the associated group(s) show up (they do)
            //var q = db.Users.FirstOrDefault(x => x.name == "Chip");
            
            int i = 1;
    
            // now delete a user from group zero, starting from the group
            // this removes Axel from Drifters
            Console.WriteLine("delete user from group zero, starting from the Group");
            Console.WriteLine("this removes Axel from Drifters");
            var uig = g[0].UsersInMyGroup[0];
            db.UsersInGroups.Remove(uig);
            n = db.SaveChanges();
            ShowMembership();
    
            i = 4;
    
            // now delete a user from a group, starting from the user
            // this removes Axel from Pleasers
            Console.WriteLine("delete user from a group, starting from the User");
            Console.WriteLine("this removes Axel from Pleasers");
            var gim = u[0].GroupsIamIn[0];
            db.UsersInGroups.Remove(gim);
            n = db.SaveChanges();
            ShowMembership();
    
    
            i = 2;
    
            // delete all users from group two
            // removes Axel and Chip from Slackers but leaves Biff there ?!?!?!?!?
            Console.WriteLine("delete all users from group two");
            Console.WriteLine("removes Axel and Chip from Slackers but leaves Biff untouched");
            var uigs = g[2].UsersInMyGroup;
            uigs.ForEach(x => db.UsersInGroups.Remove(x));
           
            n = db.SaveChanges();
            ShowMembership();
    
            i = 3;
    
            // This seems to work reliably
            Console.WriteLine("delete all users from group one");
            Console.WriteLine("removes everybody from Pleasers (and works!)");
            g[1].UsersInMyGroup.Clear();
            n = db.SaveChanges();
            ShowMembership();
    
            i = 5;
          }
        }
      }
    }
    
    
    Friday, April 8, 2011 9:28 PM
  • Hi Alan,

    The code throw "Collection was modified; enumeration operation may not execute." exception after the first loop.


    Wednesday, December 26, 2012 11:14 PM