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.

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.

Answer 2

Clear the parameters before you define them:-

cmd.Parameters.Clear()

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.