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

clinqstored-procedures

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.

Best Answer

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...