Returning Values from Stored Procedures using Linq

Linq is Microsofts new way of accessing databases, for the first time you can write database queries directly within your code instead of within a string.

I use stored procedures a lot, in fact I rarely have even a select statement in my code. Other people far more qualified than me will give the pros and cons of this method. I used to use the Microsoft Enterprise Library and they had a very clean way of accessing the data

For a single value returned from a stored procedure the code would go something like this

Dim db as database = DatabaseFactory.CreateDatabase
Dim RetVal as integer= CType(db.ExecuteScalar("sel_DoesUserNameAlreadyExist", UserName), Integer)

Easy, job done.

I've been trying to do the same thing using Linq

Here's the Stored Procedure, about as basic as can be

Create Procedure sel_DoesUserNameAlreadyExist(@Username nvarchar(50))
as
select count(UserName) as CountOfUser from tblUser where UserName = @UserName


1st Job after you create the stored procedure is you drag and drop the stored procedure onto the DataClassDataContext. This very cleverly maps the StoredProcedure to Linq and sets up a
class for the returned data

_
Public Function sel_DoesUserAlreadyExist( ByVal forename As String, ByVal surname As String) As ISingleResult(Of sel_DoesUserAlreadyExistResult)
     Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType MethodInfo.GetCurrentMethod,MethodInfo), forename, surname)
     Return CType(result.ReturnValue,ISingleResult(Of sel_DoesUserAlreadyExistResult))
End Function

Here's the Class definition

Partial Public Class sel_DoesUserNameAlreadyExistResult
Private _CountOfUser As System.Nullable(Of Integer)
     Public Sub New()
          MyBase.New
     End Sub
     _
     Public Property CountOfUser() As System.Nullable(Of Integer)
          Get
               Return Me._CountOfUser
          End Get
          Set
               If (Me._CountOfUser.Equals(value) = false) Then
                    Me._CountOfUser = value
               End If
          End Set
     End Property
End Class

Note how the CountOfUser field has been mapped to the class

My Linq code is now

Dim db As New My_DataClassDataContext
Dim RetVal as integer
For Each row In db.sel_DoesUserNameAlreadyExist(UserName)
     RetVal = row.CountOfUser
Next


This is the end result of my struggles, on the way I discovered a big gotcha

Here's my original Stored Procedure


Create Procedure sel_DoesUserNameAlreadyExist(@Username nvarchar(50))
as
select count(UserName) from tblUser where UserName = @UserName


Note there is no alias on field count(UserName)

When this was mapped to Linq there is no sensible field name so it gets mapped to Column1 instead of CountOfUser. This causes a problem when you try and run the stored procedure. The mapping fails, Column1 does not really exist and so the returned value does not get mapped, a row gets returned but Column1 has a value of Nothing.

Workaround, always alias your calculated fields when using Stored Procedures.