Query Returns Nothing when using SqlParamter: Entity Framework

Question

I wrote a query to display jobs that meet specific criteria in a production line. Basically, it shows jobs that are "works in progress" (WIP) for a specific section of a production line at my work place.

I am using the following to execute my query:

var CurrentWIPQuery = context.Database.SqlQuery<JobList>(@" ....  ");
return CurrentWIPQuery.Count();

"JobList" is a model which holds a job number, customer name, and some date information on when certain stages were completed. So the query is supposed to return the proper fields to these properties in my model.

So this returns the COUNT of the rows from the query; the number of jobs currently classified as a work in progress. Due to the sensitivity of information, I didn't include the actual SQL Query. BUT I can tell you it is working fine. The issue arises when I attempt to paramaterize this query, since I would like to be able to switch the production line number in the query by a parameter rather than typing out the code for another line. When using parameters, the query returns nothing.

I am doing it this way because the query is quite lengthy and didn't seem to work well when I use the full entity framework method.

Here's how I am creating the parameters. I have tried 2 different ways of doing it and both ways fail. Method 1

var CurrentWIPQuery = context.Database.SqlQuery<JobList>(@"
...query here...",new SqlParameter[] { ("@Cell", CellName),("@line, SCHEDULE_GROUP_NAME)});

Method 2

SqlParameter WIPParam1 = new SqlParameter("@Cell", CellName);
SqlParameter WIPParam2 = new SqlParameter("@line", SCHEDULE_GROUP_NAME);
var CurrentWIPQuery = context.Database.SqlQuery<JobList>(@"
...query here...", WIPParam1, WIPParam2);

Method 2 prevents me from getting the the:

SqlParameter is already contained by another SqlParameterCollection

error (I have another query elsewhere which uses the same parameters, and they're somehow conflicting), but the query is still empty.

What's working It Works fine when I hardcode the Cell and line number into my sql query, so I'm stuck as to why it doesn't work when I paramaterize these.

Thanks for any help on this!

Answer 1

Your second method is likely working fine (hard to tell without the query and database structure) but the exception you get is because you are enumerating the results multiple times. Entity Framework uses deferred execution so doesn't actually hit the database until you need the data. This means that the return from your query (in this case CurrentWIPQuery) is effectively a promise to go and retrieve the data. Consider this:

var CurrentWIPQuery = ...;

//This should work fine
var count = CurrentWIPQuery.Count(); 

//This will throw an exception
foreach(var thing in CurrentWIPQuery)
{
}

The second run through CurrentWIPQuery tries to run the query again, but because the SqlParameter objects you passed in have already been associated with another query, it throws. The easiest solution is to ensure that CurrentWIPQuery contains the materialised data instead of an IEnumerable by adding ToList() to the end:

var CurrentWIPQuery = context.Database.SqlQuery<JobList>(
    @"...query here...", WIPParam1, WIPParam2)
    ToList();