CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6
Fred Johnson
Fred Johnson

Reputation: 2695

I need a fast work-around for "There is already an open DataReader associated with this Command which must be closed first"

I have the following class, which produces the error in the question title for line msgHistory.Messages.Add(pd);.

public void StoreMessage(string UserTo, string UserFrom, string Message)
{
    var collection = from pm in context.PrivateMessageHeader select pm;

    foreach (var msgHistory in collection)
    {
        string user1 = msgHistory.User1;
        string user2 = msgHistory.User2;

        if ((user1 == UserTo && user2 == UserFrom) || (user1 == UserFrom && user2 == UserTo))
        {
            PrivateMessageDetail pd = new PrivateMessageDetail();
            pd.FromUser = UserFrom;
            pd.Message = Message;

            msgHistory.Messages.Add(pd);

            context.Entry(msgHistory).State = System.Data.Entity.EntityState.Modified; // saves modification
            return;
        }
    }

From what I gather, Entity Framework has an open connection still to the item, but I don't understand why it doesn't let me modify it.... I want as fast an execution as possible and I am afraid that if I take an approach such as getting the id, closing the loop with a break, and then re-finding the privateMessageHeader model I am executing way more commands than required.

Many thanks

Upvotes: 0

Views: 78

Answers (1)

Steve
Steve

Reputation: 216243

Usually this message is caused by the underlying code that tries to execute a SqlCommand when you are looping over a result from a previous operation. In this situation the loop works using an SqlDataReader and this object has an exclusive use of the connection that cannot be used to execute commands. (See Remarks on SqlDataReader)

The simple workaround is to add to your connection string 

"MultipleActiveResultSets=True;"

This allows to share the connection between the SqlDataReader and SqlCommand

A more detailed explanation could be found here on MSDN

Upvotes: 4

Related Questions