SqlParameter is already contained by another SqlParameterCollection, but I don't see how

Question

I have the following code.

// Get total row count and build Pagination object
var countQuery = ArticleServerContext.Database.SqlQuery<int>("GetFullTextSearchCount @SearchTerm",
    new SqlParameter("@SearchTerm", fullTextQuery));
Pagination pagination = new Pagination(countQuery.Single(), page ?? 1);
// Get search results for current page
var resultsQuery = ArticleServerContext.Database.SqlQuery<ArticleSummary>("GetFullTextSearchResults @SearchTerm, @SkipRows, @TakeRows",
    new SqlParameter("@SearchTerm", fullTextQuery),
    new SqlParameter("@SkipRows", pagination.SkippedRows),
    new SqlParameter("@TakeRows", pagination.RowsPerPage));
// Build model
SearchResultsModel model = new SearchResultsModel
{
    SearchTerm = searchTerm.Trim(),
    Pagination = pagination,
    Results = resultsQuery.ToList()   // <=== Here's where the error happens
};

When I attempt to enumerate resultsQuery, I get the following error message.

The SqlParameter is already contained by another SqlParameterCollection.

This error message seems clear enough, but I cannot see where I'm adding an SqlParameter to more than one anything. The only thing I can imagine is that the first parameter to both calls are identical. Could C# be combining them somehow? Either way, I need them to contain the same data.

Can anyone see what's happening here?

EDIT:

Sorry, this turned out to be a debugging issue. I had another issue that prevented the results I expected. But when I break in the debugger and step through my code, I get the error mentioned above.

It seems that the code executes using the SqlParameter in question, and then I attempt to inspect the contents of the query and the query runs again with the same SqlParameter, and that is what is causing the error.

Unfortunately, now that I have a bounty, I cannot delete the question.

Decade

Any chance ArticleServerContext shares a sql connection? I'm assuming it's a static class – Halfhardy

@Rodders: ArticleServerContext is my automatically generated DbContext class. – Decade

@DavidG: Although that article is about the same error message, the apparent issue is completely different. In fact, I cannot understand how the issue the answer describes how to resolve ever could produce the error message reported. – Decade

@DavidG: Not sure how I understand how that could be an issue here. – Decade

Problem has to be in the ArticleServerContext.Database class. I'm guessing it's caching those parameters. Try commenting out the countQuery line and use dummy data for it's results. – Centennial

@LarsTech: Quite surprisingly, I removed the code associated with countQuery and the error persists. The assumption was that this would stop the error and that helps explain why I couldn't find a solution before. But it still makes even less sense now. – Decade

You can still answer the question yourself. Mark it as answered and close it. You will not receive your bounty though. – Inaugural

Answer 1

I didn't have a good understanding of what was happening when I posted this question. After further study, it turns out that:

1. A separate issue was causing my program not to display the (any) results I expected.

2. Using the Visual Studio debugger, I had set a breakpoint in this code. As I stepped through, the queries were being executed. But then when I attempt to inspect the data, that caused the queries to be executed again. It was this dual execution that was causing the error I reported. In fact, this error was not occurring when the code ran normally.

Thanks to everyone who took time to look at this issue.

Decade

Answer 2

Clear the parameters before you define them:-

cmd.Parameters.Clear()

Infrangible

Clear the resultsQuery list – Infrangible

I don't know what you mean. I need the data in resultsQuery, so I can't clear it (whatever that means exactly) until after the error has occurred. – Decade

Answer 3

Try this. Instead of :

  var resultsQuery = ArticleServerContext.Database.SqlQuery<ArticleSummary>("GetFullTextSearchResults @SearchTerm, @SkipRows, @TakeRows",
    new SqlParameter("@SearchTerm", fullTextQuery),
    new SqlParameter("@SkipRows", pagination.SkippedRows),
    new SqlParameter("@TakeRows", pagination.RowsPerPage));
// Build model
SearchResultsModel model = new SearchResultsModel
{
    SearchTerm = searchTerm.Trim(),
    Pagination = pagination,
    Results = resultsQuery.ToList()   // <=== Here's where the error happens
};

Use:

      var results = ArticleServerContext.Database.SqlQuery<ArticleSummary>("GetFullTextSearchResults @SearchTerm, @SkipRows, @TakeRows",
    new SqlParameter("@SearchTerm", fullTextQuery),
    new SqlParameter("@SkipRows", pagination.SkippedRows),
    new SqlParameter("@TakeRows", pagination.RowsPerPage)).ToList();
// Build model
SearchResultsModel model = new SearchResultsModel
{
    SearchTerm = searchTerm.Trim(),
    Pagination = pagination,
    Results = results   // <=== Moved the call to ToList UP
};

Defferred Query execution is another reason why I'm not a fan of EF. By moving the ToList() call up, you are forcing EF to perform Immediate Execution instead. In the update to your question, you state that your issue was related to a debugging problem of the IDE rerunning your query twice (causing the duplicate parameter exception).

However, your debugging problem could be avoided by bypassing Defferred Query Execution and forcing the execution to happen immediately, thereby changing the contents of the variable resultsQuery from a query waiting to be called to the actual result set.

To be clear, moving the ToList() call up forces immediate execution of the query into your variable "resultsQuery", which changes its contents (and because of this I changed the variable name to reflect its changed contents). The variable now will hold the returned results of your query, instead of the query itself. Therefore when you enumerate over it when execution is paused you will be enumerating over a static list of returned results, instead of rerunning the query. This would therefore prevent the exception from being thrown.

That's also why I stated in my comment that said issue has never happened to me (apart from the fact that I steer clear of EF when possible nowadays).

You can read more on MSDN here:

https://msdn.microsoft.com/en-us/library/bb738633%28v=vs.100%29.aspx?f=255&MSPPError=-2147217396

From the above link, Microsoft states:

To force immediate execution of a query that does not produce a singleton value, you can call the ToList method, the ToDictionary method, or the ToArray method on a query or query variable.

Infiltrate

What possible difference does this make? (It doesn't that I can see.) Sorry, anyway see my updated question. This turns out to be an issue when running code in the debugger. – Decade

You would be very surprised... but I'm glad that wasn't your issue and its been resolved. Although from your update to the question it would seem that your debugger issue should be happening to everyone else who inspects their query code when execution is paused. That certainly has never happened to me. – Infiltrate

See the update to my answer.. there is a huge difference. – Infiltrate

I didn't down vote your answer, but it's really not helpful to me. I understand exactly what ToList() does and I use it all the time. In fact, it was in my original code. To address my question of what difference it makes to move it as you have done, you've added quotes about what it does. Sorry, I appreciate your attempt to help. But it's just not helpful to me. – Decade

No worries... however maybe I wasn't clear. I did in fact explain in my update how moving the ToList call up forces immediate execution of the query into your variable, which changes its contents. The variable now will hold the returned results of your query, instead of the query itself. Therefore when you enumerate over it when execution is paused you will be enumerating over a static list of returned results, instead of rerunning the query. This would therefore prevent the exception from being thrown. – Infiltrate

Yes, it may have made some differences with respect to inspecting data while the debugger is paused. Actually, I did try changes like that and the error didn't go away. But I didn't spent much time with it and perhaps it could've been used to resolve my debugger issues. – Decade