Deleting from a SQL database

Question

I'm trying to delete an assignment from a MySQL db. While doing so I'm deleting all elements and boguses as seen below. Problem is that when it gets to the for-loop in the deleteboguses method it throws an exception "EntityCommandExecutionException" any ideas why it does this? What am I doing wrong?

public static void DeleteAssignment(int id)
    {
        var deleteAssignment = from assignment in context.assignment
                               where assignment.id == id
                               select assignment;
        DeleteElement(id);
        foreach (assignment a in deleteAssignment)
        {
            context.assignment.DeleteObject(a);
        }
        context.SaveChanges();
    }

    public static void DeleteElement(int id)
    {
        var deleteElement = from element in context.element
                            where element.assId == id
                            select element;
        foreach(var e in deleteElement)
        {
            DeleteBoguses(e.id);
            context.element.DeleteObject(e);
        }
        context.SaveChanges();
    }

    public static void DeleteBoguses(int id)
    {
        var deleteBogus = from b in context.bogus
                          where b.elementId == id
                          select b;
        foreach(var b in deleteBogus)
        {
            context.bogus.DeleteObject(b);
        }
        context.SaveChanges();
    }

Hello! :) Please post the exception and the message associated with it too. — Ranhiru Jude Cooray, May 05 '12 at 15:20
System.Data.EntityCommandExecutionException was unhandled Message=An error occurred while executing the command definition. See the inner exception for details. — Drakthal, May 05 '12 at 16:45
There is already an open DataReader associated with this Connection which must be closed first. — Drakthal, May 05 '12 at 17:24
See [this question](http://stackoverflow.com/questions/4867602/entity-framework-there-is-already-an-open-datareader-associated-with-this-comma). You need to set `MultipleActiveResultSets ` to true. — Ranhiru Jude Cooray, May 05 '12 at 17:49
This indicates that an effort is done to do a database read while other objects are in a state of being read and materialized from the database. Multiple active result sets may help, but usually it can also be prevented by forcing execution of a linq statement. Here, doing `deleteBogus.ToList()` may help. — Gert Arnold, May 06 '12 at 20:46
Thx alot, the last answer helped when i tried doing it to deleteElement.ToList() in the deleteElement(int id) method — Drakthal, May 07 '12 at 14:19

score 2 · Accepted Answer · answered Mar 10 '13 at 04:54

Just as an explanation for the reason why you needed this fix, and this question is still technically open.

IEnumerable, like the deleteElement variable in your DeleteElement method, will not accept the user deleting items from its collection while enumerating through the values.

To get around this, you use the ToList() function to create a copy of the values. This make it safe to delete items in the original list while still looping though its values.

Deleting from a SQL database

1 Answers1