Linq function based on stored procedure which does count(*) returns null

Question

I have a SQL Server 2008 stored procedure which does a select count(*) ... I have a LINQ function called GetCount() based on that SP.

When I do something like: int? count = datacontext.GetCount().Single().Column1;

count is null. The SP never returns null.

Why is count null?

Answer 1

It worked when I gave an alias to the count.

Answer 2

It appears that Linq-to-SQL doesn't like Stored Procs that return a single result as a SELECT statement. I had the same trouble you're seeing.

One solution for a case like this would be to create a stored function instead:

CREATE FUNCTION dbo.GetCountFunc()
RETURNS INT
AS BEGIN
    DECLARE @MyCount INT

    SELECT @MyCount = COUNT(*) FROM Customers

    RETURN @MyCount
END

and then pull that into your Linq-to-SQL data context and reference it. In this case, I get back an INT no problem.

Marc