Migrating stored procedure from LINQ to SQL to Entity Framework 6

Question

I have migrated the my project from Linq-to-SQL to Entity Framework 6. After resolving so many problems, I finally came to one I am not sure what to do with it. After conversion about one third of my unit tests are failing because stored procedures returning scalar values just work different way.

In Linq-to-SQL, the stored procedure return the returned value. In EF they return number of rows affected. There are workarounds which requires changing the stored procedure (generally from RETURN @retvalue to SELECT @retvalue). But this requires changes in T-SQL. The project still contains some legacy code from old days of ASP.NET like aspnet_Membership_CreateUser and so on. It means that I cannot change these stored procedures, because there are partly used by ASP.NET ADO.NET Membership provider, partly by the Linq-to-SQL code. The solution I consider is to make T-SQL wrappers of these parts legacy code.

Another reason why I would like to keep the stored procedure unchanged is the possibility of reverting to previous commit if the upgrade to EF is not successful. A don't want to change database forth and back again. But this reason is not so strong, it is only about my laziness.

I know that membership provider is old fashioned but I cannot rewrite entire application in one commit, I need to keep it stable.

The best way to finish the migration would be to call the stored procedure from EF like Linq-to-SQL, but I didn't found a way how to it. The EF disappointed me in this. How is possible that after years of development EF does not support returning scalar value from stored procedure?

How would you solved this issue?

Tim Norris · Accepted Answer · 2015-09-26 18:40:43Z

0

Hi Qub1n,

    // Create the gender parameter var param1 = new SqlParameter {
    ParameterName = "@userId",
    Value = _userId };

    // Create the return code var returnValue = new SqlParameter {
    ParameterName = "@ReturnCode",
    SqlDbType = System.Data.SqlDbType.Int,
    Direction = System.Data.ParameterDirection.Output };


    // Gender is an int column in the database.
    _context
    .Database
    .ExecuteSqlCommand("exec @ReturnCode = GetGenderOfUser     @userId", 
    param1,    returnValue);

    return (int)returnValue.Value;

This works for me. My stored proc is like this:

CREATE PROCEDURE [dbo].[GetGenderOfUser] 
    -- Add the parameters for the stored procedure here
    @userId uniqueidentifier 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    declare @retval int
    -- Insert statements for procedure here
    set @retval = (select top(1) Gender from Users where Guid = @userId)

    return @retval
END

Let me know if this works!

answered Sep 26, 2015 at 18:40

Tim Norris

2661 gold badge2 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tomas Kubes Over a year ago

Finally I end up with solution posted above. ExecuteSqlCommand would work, but I cannot automatically generate/update model from database.

Tomas Kubes · Accepted Answer · 2015-09-26 19:14:34Z

0

Finnaly I end up with wrappers, which preserve the original functionality of database (for ASP.NET MembershipProvider) and create version of stored procedures for EntityFramework:

CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_AddUsersToRolesEF]
    @ApplicationName  nvarchar(256),
    @UserNames        nvarchar(4000),
    @RoleNames        nvarchar(4000),
    @CurrentTimeUtc   datetime
AS
BEGIN
    DECLARE @return_value int

EXEC    @return_value = [dbo].[aspnet_UsersInRoles_AddUsersToRoles]
        @ApplicationName,
        @UserNames,
        @RoleNames,
        @CurrentTimeUtc

SELECT  @return_value
END

GO

Now I can use the compiler to check number and types of parameters in store procedure.

answered Sep 26, 2015 at 19:14

Tomas Kubes

25.5k18 gold badges121 silver badges163 bronze badges

Collectives™ on Stack Overflow

Migrating stored procedure from LINQ to SQL to Entity Framework 6

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related