CODEWITHSUNDEEP
c#sql-serverentity-frameworkstored-procedures
Yoda
Yoda

Reputation: 339

Entity framework - Return GUID from database first stored procedure

I have a method which calls a stored procedure that is existing in the database

public Guid GetUserID(string userName)
{
  using (var entities = new entities ())
  {
    return  Guid.Parse(entities.Database.SqlQuery<Guid>(
                       "dbo.[GetUserID] @UserName",
                       new Object[] { new SqlParameter("@UserName",
                                                        userName)
                                                  }).ToString());
  }
}

And my stored procedure:

CREATE PROCEDURE [dbo].[GetUserID] 
 @UserName NVARCHAR(100)
 AS
 BEGIN 
 SET NOCOUNT ON;

 SELECT TOP 1 ID FROM [DBO].[USER] WHERE USERNAME = @UserName
        ORDER BY CREATEDON DESC
END
GO

I added some logs and getting the following error:

Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).
   at System.Guid.TryParseGuidWithNoStyle(String guidString, GuidResult& result)
   at System.Guid.TryParseGuid(String g, GuidStyles flags, GuidResult& result)
   at System.Guid.Parse(String input)

Whats causing the above error?

Upvotes: 2

Views: 1648

Answers (2)

Laughing Vergil
Laughing Vergil

Reputation: 3756

A GUID (Globally Unique Identifier) is a specific data type in SQL Server, Entity Framework, and other sources. It is represented as a 32 digit hexadecimal number with 4 dashes, like this: B98332DB-F5E0-4022-860F-9A42A7DEB965

You are attempting to assign a username, which is almost certainly nor a 32 digit hexadecimal number with four dashes, to a GUID data format when you use public Guid.

Change the data type of the proc from Guid to String or some other more correct data type.

Edit: I read the original entry incorrectly, and the above is incorrect. However, the error message indicates that the value being passed to Guid.Parse is not a string representation of a GUID.

So, what value is actually being stored in the Id field? Is it an identity field, an actual GUID (in which case you don't need to .Parse it), a string, or ??

Upvotes: 0

Zhaph - Ben Duguid
Zhaph - Ben Duguid

Reputation: 26956

Per the docs SqlQuery returns a collection of results. If you're only expecting one result back you need to call Single or First:

var userIdQuery = entities.Database.SqlQuery<Guid>(
                   "dbo.[GetUserID] @UserName",
                   new Object[] { new SqlParameter("@UserName", userName)});

// userIdQuery is a collection of Guids.
// We need to take either the first one, or ensure there
// is only one:    
Guid.Parse(userIdQuery.Single().ToString());

Be aware that Single() will throw an exception if no result is returned as well as if more than one result is returned.

Upvotes: 1

Related Questions