"There is already an open DataReader associated with this command which must be closed first." - but where?

Question

This is literally my entire transaction:

adc.BeginTransaction();

// create/edit quote assocated with quote request
adc.CreateOrEditQuote(model.QuoteRequestID, model.Amount);

adc.CommitTransaction();

I don't see anywhere that I have an open DataReader lying around that could be interfering with anything else - there's only one database call inside the transaction! I do have another database call in another class which should be finished by the time this is called:

var model = new ViewQuoteRequestModel();

var qr = DataContextProvider.REDACTED.ListQuoteRequests().SingleOrDefault(q => q.QuoteRequestID == id);
if (null != qr)
{
    qr.CopyTo<IViewQuoteRequestModel>(model);
}

return model;

Calling SingleOrDefault on my data should be resolving it and not leaving it lazy-loaded, right? So that shouldn't be a problem anyway...

edit: if I comment out the line where I call CreateOrEditQuote then there is no error, but of course the data is not saved. So in case it helps, here is my stored procedure:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[REDACTED].[CreateOrEditQuote]') AND type in (N'P', N'PC'))
DROP PROCEDURE [REDACTED].[CreateOrEditQuote]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [REDACTED].[CreateOrEditQuote]
(
    @QuoteRequestID int,
    @Amount money
)
as
begin
    declare @qid int
    select @qid = QuoteID from QuoteRequest where ID = @QuoteRequestID
    if @qid is null
    begin
        -- quote does not exist, create it
        insert into quote (Amount) values (@Amount)
        select @qid = @@IDENTITY
    end
    else
    begin
        -- quote exists, edit it
        update quote set Amount = @Amount where ID = @qid
    end
    update QuoteRequest set QuoteID = @qid where ID = @QuoteRequestID
    select @qid as QuoteID
end
GO

Weird thing is, if I run the stored procedure manually though SQL Server Management Studio, it runs, but if I do it through my web app (calling the code I shared at the top of my post) it crashes...

*"Calling SingleOrDefault on my data should be resolving it and not leaving it lazy-loaded, right?"* -- can you link to the documentation for that? — 15ee8f99-57ff-4f92-890c-b56153, Sep 26 '19 at 18:32
Show the code for `CreateOrEditQuote` which seems to be an extension method. — juharr, Sep 26 '19 at 18:32
`CreateOrEditQuote` is not an extension method; it's a LINQ to SQL wrapper for a stored procedure. — ekolis, Sep 26 '19 at 18:34
@RufusL A `SingleOrDefault` at the end will evaluate the query and return the single resulting value, default, or throw an exception. — juharr, Sep 26 '19 at 18:34
Yeah, I tried adding `ToList()` before the `SingleOrDefault` and that didn't help... — ekolis, Sep 26 '19 at 18:36
@juharr doh! not sure what I was thinking. I was starting to update my comment and then I realized my mistake. thanks — Rufus L, Sep 26 '19 at 18:37
Are you properly disposing of your context? You really should wrap them in `using` statements. — juharr, Sep 26 '19 at 18:39
Not sure what other code to share - could there be something wrong with the stored procedures (ListQuoteRequests and CreateOrEditQuote)? — ekolis, Sep 26 '19 at 18:40
Lazy loading occurs after the query is executed (by `SingleOrDefault()` in your case). You could try [disabling lazy loading](https://learn.microsoft.com/en-us/ef/ef6/querying/related-data#turn-off-lazy-loading-for-all-entities), or the solution from [this SO post](https://stackoverflow.com/a/6064422/5803406) — devNull, Sep 26 '19 at 18:40
Wrapping a data context in a `using` statement? Hmm, but they're set up as something akin to singletons right now; don't want to destroy them and not let anyone else use them! — ekolis, Sep 26 '19 at 18:41
I tried replacing the declaration of `qr` with this simpler statement: `var qr = DataContextProvider.REDACTED.GetQuoteRequest(id).SingleOrDefault();` but I'm still getting the error saying a data reader is left open... — ekolis, Sep 26 '19 at 19:23
Why are you creating your datacontext as singletons? That is normally a bad idea and can cause problems like your error if two threads are using the same dataContext. You might want to read https://stackoverflow.com/questions/6592163/creating-static-datacontext-or-creating-whenever-its-needed-which-is-better or https://stackoverflow.com/questions/4081071/should-data-contexts-be-static — sgmoore, Sep 26 '19 at 19:37
I don't know, that's just the way things were set up; I didn't configure the data contexts that way. — ekolis, Sep 26 '19 at 19:38

score 0 · Answer 1 · answered Sep 27 '19 at 16:42

Because my stored procedure was returning data, I had to retrieve that data:

var qid = adc.CreateOrEditQuote(model.QuoteRequestID, model.Amount).SingleOrDefault()?.QuoteID;

rather than simply calling the CreateOrEditQuote method without assigning the result to a variable.

"There is already an open DataReader associated with this command which must be closed first." - but where?

1 Answers1