SqlParameter is already contained by another SqlParameterCollection, but I do not 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 where the error happens }; 

When I try to list the resultsQuery , I get the following error message.

SqlParameter is already contained by another SqlParameterCollection.

This error message seems clear enough, but I don’t see where I am adding SqlParameter to more than one. The only thing I can imagine is that the first parameter for both calls is identical. Can C # somehow combine them? In any case, I need them to contain the same data.

Can anyone see what is happening here?

EDIT:

Sorry, this turned out to be a debugging problem. I had another problem that interfered with the expected results. But when I break the debugger and look through my code, I get the error mentioned above.

It seems that the code is executing using SqlParameter , and then I try to check the contents of the request and the request starts again with the same SqlParameter , and that is what causes the error.

Unfortunately, now that I have the generosity, I can not delete the question.

Answer 1

I did not have a good understanding of what was going on when I posted this question. After further study, it turns out that:

• A separate problem was that my program did not display the expected (any) results.

• Using the Visual Studio debugger, I set a breakpoint in this code. When I passed, the requests were fulfilled. But then when I try to validate the data, it triggers a repeat request. It was this double execution that caused the error that I reported. In fact, this error did not occur when the code worked fine.

Thanks to everyone who took the time to look at this problem.

Answer 2

Clear the parameters before defining them: -

 cmd.Parameters.Clear() 

Answer 3

Try it. Instead:

  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 where the error happens }; 

Using:

  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 }; 

Performance

Defferred Query is another reason I'm not an EF fan. By moving the ToList () call, you force EF to execute immediately . In updating your question, you are reporting that your problem is with the debugging issue of the IDE repeating your request twice (causing a duplicate parameter exception).

However, your debugging problem can be fixed by bypassing the Defferred Query Execution and forcing the execution to run immediately, thereby changing the contents of the resultsQuery variable from the query waiting to be called into the actual result set.

To be clear, moving the ToList () call causes the query to be immediately executed in your "resultsQuery" variable, which changes its contents (and because of this I changed the name of the variable to reflect its changed contents). Now the variable will contain the returned results of your query, not the query itself. Therefore, when you list it when execution is paused, you will list a static list of returned results, instead of repeating the query. Therefore, this will prevent the exception from being thrown.

This is why I stated in my comment that this problem never happened to me (except that I avoid EF now when possible).

You can learn more about MSDN here:

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

In the above link, Microsoft states:

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