Return value of stored procedure is correct but column values are all NULL

Question

I'm working with the C# membership provider and transferring it to LINQ along the way.

I'm having trouble pulling the results from a stored procedure in MS SQL. The procedure does some work to set variables and then selects the variables before setting the return value of 0.

When I run this in MS SQL to test it works fine. When I run it with a regular command object and a reader in C# it works fine. However, when I'm using LINQ to run it I'm not able to get the results back, just the return value.

SQL Procedure

ALTER PROCEDURE [dbo].[TEST] AS BEGIN

    **... DO WORK TO DECLARE AND SET VARIABLES ....**

    SELECT   @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount, FailedPasswordAnswerAttemptCount, @LastLoginDate, @LastActivityDate, @Status_Flag

    RETURN 0 
END

MSSQL Result Set

host | 0 | NULL | 0 | 0 | 2009-09-22 00:57:22.700 | 2009-12-09 16:35:05.607 | 1

(1 row(s) affected)

Return Value 0

(1 row(s) affected)

LINQ

var x = (from b in _linq.TEST()
         select b).Single();

LINQ Results

NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL

Return Value 0

I've also verified that the sql seems to be the same being sent from LINQ as what I'm typing into MSSql.

Here's all of the LINQ that I've tried (and viewed in debugger to see results):

// allow linq to create custom TESTResult type 
var result1 = _linq.TEST();

// get the return value from the type created above 
object p = result1.ReturnValue;

// get the single result set from SP into the TESTResult type 
TESTResult result = _linq.TEST().Single();

// try to see one column to ensure null values aren't causing issues 
var y = (from b in _linq.TEST() select b.Column1).Single();

// try querying result set from LINQ 
var x = (from b in _linq.TEST() select b).Single();

I've also tried playing with the database to change column values and remove nulls.

Answer 1

If you have one or more results generated before the result set you want to return try

Set NoCount On

at the beginning of your proc. I don't know how it works with LINQ, but with ADO and ADO.NET with intermediate results sets, the provider will not be able to return the result you want because it doesn't know what to do with the intermediate counts.

NOW with edits

I can't find any samples of LINQ working with a returned row that is solely composed of variables local to the stored proc. If you know it can work that way, great, let me know and I'll have learned something. What I did find was LINQ being able to bind to OUTPUT parameters.

http://msdn.microsoft.com/en-us/library/bb386975.aspx

If you're still not having any luck, try adding the variables you are returning as defaulted output parameters for the proc. (and, of course, remove that final select)

again, LINQ is not my forte, but I hope this helps.

Answer 2

Have you tried assigning column names to the result? Maybe Linq has an issue handling results with no column names.

SELECT @Password Password, @PasswordFormat PasswordFormat, etc...

Answer 3

Possibly you want the opposite

 set nocount off

I've seen numerous blog posts where people have said they had to turn nocount off. In your query tool it's obviously off, but what about your app connection? Explicitly set nocount off in the procedure and see if it works. Since you see the results in the debugger, maybe not getting a count it throwing LINQ off?

Answer 4

This was a combination of a few things that ultimately ended with the very first thing I tried and what Ron suggested. The columns must be named to be pulled with LINQ.

LINQ can return a row composed solely of variables (or whatever you select); however, I didn't realize that even when I changed info being passed or changed the SP in MSSql and re-added it to the LINQ designer, the connection in Vis Studio to the database was what was caching the structure of the stored proc. So... no matter what I tried changing when I deleted and re-added the stored proc in my LINQ designer it was always using a cached representation of the stored proc. After much testing I found the only way to get a fresh one was to actually close Vis Studio and restart it to open the new db connection and get the new SP structure (correct me if i'm wrong).

Thanks all for the help and suggestions.