This posts assumes you have some knowledge of LINQ TO SQL. If you do not I suggest you take a look at scott guthries LINQ articles
I had an issue today where for a while it seemed that I could not return a single row from a LINQ query that utilizes a stored procedure. After some digging around I found that it is possible and very easy.
I have an object in my data model called "User". I was retrieving a User object by using a LINQ query. My query looked like this:
Dim testDb As New TestDbDataContext
Dim returnUser As User = (From a In jobsDb.Users _
Where a.UserId.Equals(“GUID_GOES_HERE”) _
Select a).SingleOrDefault
After some discussion with my mentor I decided to use a stored procedure to retrieve the User object rather than grant select access to the application user. It was just one more safeguard against hacking and sql injection attacks.
The first thing I did was create my proc and add it to my model in LINQ to SQL:
create [dbo].[usp_users_get]
(
@userid uniqueidentifier
)
as
set nocount on
SELECT
UserId
,FirstName
,LastName
,DateOfBirth
,Address
,Address2
,City
,State
,PostalCode
,EmailAddress
,PhoneNumber
,Password
,SecretQuestion
,SecretQuestionAnswer
FROM dbo.users
where
userid = @userid
This will get all the columns from the table using the primary key "userid". This basically replaces the auto-generated sql statement from LINQ to SQL.
The next step was to add the new proc to the data model. After the proc is added, you can then change the "return type" to the User entity from the model. This will tell LINQ to map the columns returned from the proc to the properties in the User entity in the data model.
By default, the return type of the method created by LINQ TO SQL for the usp_users_get method is System.Data.Linq.ISingleResult(Of usp_users_getResult). ISingleResult implements IEnumerable which means that if the stored proc returns more than one record you can loop through the results using a For Each statement. "usp_users_getResult" is basically an object that has properties that are mapped to the columns returned by the select statement in the stored proc.
If we have "select userid, firstname, lastname from dbo.users" in the stored procedure the "usp_users_getResult" type will have three properties named userid, firstname and lastname. The sql server types are automatically converted to .NET types. While this is nice it's not what I wanted. I wanted a User type returned.
Changing the Return Type setting will change the return type of the method "usp_users_get" from System.Data.Linq.ISingleResult(Of usp_users_getResult) to System.Data.Linq.ISingleResult(Of Users). This makes getting a single User object a lot easier than trying to map usp_users_getResult type to a User type object.
Let's take a look at the code used to return a single object and then I'll explain what it does.
Partial Public Class TestDbDataContext
Public Function GetSingleUser(ByVal userId As System.Guid) As User
Return Me.usp_users_get(userId).SingleOrDefault
End Function
End Class
What I'm doing is basically extending the DataContext class generated by LINQ TO SQL. When I created my LINQ TO SQL class in VS 2008 I named it TestDb. LINQ TO SQL created a partial class named TestDbDataContext and put it in a file name TestDb.Designer.vb.
As you can see in the screen capture above I created a new class named TestDbDataContext. This is where the code example above resides.
Now let's explain what this code does. The new function named GetSingleUser will return a type of User from my data model. I then call the method generated by LINQ TO SQL "usp_users_get" which will execute the "usp_users_get" stored procedure and return the ISingleResult(Of User) type. I then add the call to .SingleOrDefault extension method. SingleOrDefault tells the User object to return a single user object OR a null object if no results are returned from the database. It also tells the User object to throw an exception if more than one row of data is returned by the stored proc.
To call my newly created method I can use the follow code:
Dim db As New TestDbDataContext
Dim userGuid As New System.Guid("95998A1B-F514-4c3b-B771-409FFCAECC1A")
Dim user As User = db.GetSingleUser(userGuid)
Until next time.
Technorati Tags:
Visual Studio 2008,
LINQ TO SQL
posted @ Monday, January 14, 2008 1:46 PM