C# Linq to SQL Stored Procedures

Question

I have a stored procedure that returns a scalar int. I am using C# and Linq to get the resulting data, but I get the following error when I compile:

foreach statement cannot operate on variables of type 'int' because 'int' does not contain a public definition for 'GetEnumerator'

My question is how do I get the resulting data if the stored procedure only returns an int. I can do this using a datatable and dataset, but I need to do this in LINQ.

Here's a code sample:

AppDataContext app = new AppDataContext();
var lookup = app.spLookupFrom400(numberType, number);
foreach(lookupResult result in lookup)
{
    ...
}

Answer 1

If you are only returning an int why are you trying to iterate through the results in a foreach?

Lets say you have an Int marked as an output parameter you should be able to just do...

AppDataContext app = new AppDataContext();
Int32? lookup = 0;
app.spLookupFrom400(numberType, number, ref lookup);

Answer 2

Is the integer return really required or is the true intent to simply return a result set? If so remove the integer return and remap the stored procedure or map the return type of the SP in the context to the appropriate object type. Otherwise the return will be the int.

Answer 3

Assuming that your intent is to return a result set and not just the integer return code from the stored procedure my guess is that LINQ was not able to properly determine the type of the result set.

Check the properties of the stored procedure in your LINQ to SQL classes module. If LINQ was able to determine the result set type when it was added to your module it should say "(Auto-generated Type)". If not, it will probably say "Integer".

This can happen if the proc fails for some reason when LINQ attempts to get the result set type. It can also happen when the result set of your proc is derived from a temporary table. If this is true the case then try adding

SET FMTONLY OFF

to the top of your proc. Then refresh the proc in Server Explorer, then drop and re-add the proc to your LINQ to SQL classes to refresh it.

Answer 4

The answers so far miss the fact that SQL Server stored procedures can produce any number of result sets and also return a value. Mostly, of course, they don't do both, so this might be what's tripping up Linq to SQL.

Whenever "magic glue code" (such as Linq to SQL) stops working, I always look for ways to make its work easier.

So I would try writing another stored procedure that calls the troublesome one but discards the scalar value.