Entity framework removes all rows instead of one

Question

I am stuck on a small problem. I want to remove one row based on an ID. But I keep deleting all my rows instead.

This is my code in my datalayer. You can read the word "eigenaar" as "owner".

public void removeEigenaar(int eigenaarID)
    {
        transaction = connection.BeginTransaction();
        try
        {
            using (context = new MyDbContext(connection, false))
            {
                context.Database.Log = (string message) => { Console.WriteLine(message); };
                context.Database.UseTransaction(transaction);
                //----------------------------

                Eigenaar e =
                    (from s in context.Eigenaars
                     where s.ID == eigenaarID
                     select s).First();
                context.Eigenaars.Remove(e);

                //----------------------------
                context.SaveChanges();
            }
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }

If I debug the code, i can see that my "e" is filled with one owner. That's what i find so strange.

edit sql

My delete query works, it just deletes way to much.

Side tip: If you expect one result back, use `.Single()` instead of `.First()` to save headaches later — Joe Phillips, Apr 23 '18 at 14:58
Also, why do you use `s` as your variable name in the linq statement? Wouldn't `e` make more sense? — Joe Phillips, Apr 23 '18 at 14:59
What is the SQL being run here? You're printing it out so it should be trivial to figure out. — DavidG, Apr 23 '18 at 15:00
Is the property `Id` declared as the primary key or is another property a primary key and is shared by all other records with one value? — Chrᴉz remembers Monica, Apr 23 '18 at 15:06
EF 6.0, my id is infact my key. But i tested with a string that wasn't my key. And it keeps removing al my rows. — ToonDoe, Apr 23 '18 at 15:09
If you don't provide more information, I will be forced to vote to close this question as not having a [mcve]. Can you at least tell us what the actual SQL code being executed is? I'm talking about something that looks like `DELETE FROM SomeTable.....` — DavidG, Apr 23 '18 at 15:16
Yes, but EF will translate that into SQL which you are outputting with the `Console.WriteLine(message)` method. — DavidG, Apr 23 '18 at 15:19
Did it delete 4 rows? Hard to tell based on the output. If so, you really should change that .First() to a .Single(). I suspect you have multiple records in the db with the same ID. If that is not expected, a .Single() would have saved you here — Joe Phillips, Apr 23 '18 at 15:29
I added single but that didn't solve my problem. And can you even make a mysql tabel with duplicated keys? — ToonDoe, Apr 23 '18 at 15:33
You say "key" but that is ambiguous. Primary keys have to be unique but "keys" do not, in general... — Joe Phillips, Apr 23 '18 at 15:34
I am sure there are no duplicate keys. is my problem somting with my context? — ToonDoe, Apr 23 '18 at 15:39
No clue. You need to provide a lot more info I'm guessing. Since it's so open ended, you'll need to narrow down the problem and then ask a new question most likely. This one doesn't seem to be going anywhere — Joe Phillips, Apr 23 '18 at 15:41

score 0 · Answer 1 · answered Apr 23 '18 at 15:14

0

Try adding:

context.Entry(e).State = EntityState.Modified;

before

context.SaveChanges();

answered Apr 23 '18 at 15:14

Sushrut Kanetkar

363
3
13

yep, one minute – ToonDoe Apr 23 '18 at 15:29
FYI, I don't think this is related – Joe Phillips Apr 23 '18 at 15:30
That's very strange. Adding your code makes all my values in my table the same. Nothing gets deleted but all my other rows change in the row i wanted to delete. – ToonDoe Apr 23 '18 at 15:31
Try context.Entry(e).State = EntityState.Deleted; – Sushrut Kanetkar Apr 23 '18 at 15:34
That was already posted as an answer, downvoted and then deleted. – Reinstate Monica Cellio Apr 23 '18 at 15:35

score 0 · Accepted Answer · answered Apr 23 '18 at 15:43

Found the problem.

In my mydbcontext.cs i added:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Eigenaar>().MapToStoredProcedures();
        modelBuilder.Entity<Eigendom>().MapToStoredProcedures();
        modelBuilder.Entity<Eigenschap>().MapToStoredProcedures();
        modelBuilder.Entity<Overeenkomst>().MapToStoredProcedures();
        modelBuilder.Entity<Panden_has_eigenschappen>().MapToStoredProcedures();
    }

Removing this code fixed it.

score 0 · Answer 3 · answered Apr 23 '18 at 16:01

Some Modification of your code:

IEnumerable<Eigenaar> e= (from s in context.Eigenaars
                 where s.ID == eigenaarID
                 select s).ToList();    
context.Eigenaars.DeleteAllOnSubmit(e);
context.SubmitChanges();

OR

You could do a normal SQL truncate or delete command, using the DataContext.ExecuteCommand method:

context.ExecuteCommand("DELETE FROM Entity");

Or

context.ExecuteCommand("TRUNCATE TABLE Entity");

Entity framework removes all rows instead of one

3 Answers3