将存储过程从LINQ迁移到SQL到实体框架6 [英] Migrating stored procedure from LINQ to SQL to Entity Framework 6

问题描述

我将项目从Linq-to SQL迁移到实体框架6.在解决了这么多问题之后，我终于找到了，我不知道该怎么做。转换约三分之一的单元测试失败，因为存储过程返回标量值只是以不同的方式工作。

在Linq-to-SQL中，存储过程返回返回的值。在EF中，它们返回受影响的行数。有一些解决方法需要更改存储过程（通常从 RETURN @retvalue 到 SELECT @retvalue ）。但这需要在T-SQL中进行更改。该项目仍然包含一些旧的ASP.NET代码，如 aspnet_Membership_CreateUser 等等。这意味着我不能更改这些存储过程，因为ASP.NET ADO.NET成员资格提供程序部分使用，部分由Linq-to-SQL代码使用。我认为的解决方案是使这些部分的T-SQL包装遗留代码。

我想保持存储过程不变的另一个原因是恢复到以前的提交如果升级到EF不成功。 A不想再次更改数据库。但是这个原因不是那么强大，只是关于我的懒惰。

我知道会员提供商是老式的，但是我无法在一次提交中重写整个应用程序，我需要以保持其稳定。

完成迁移的最佳方式是从EF中调用存储过程，如Linq-to-SQL，但是我没有找到方式怎么样EF让我失望了经过多年的发展，EF不可能支持从存储过程返回标量值？

您如何解决这个问题？

解决方案

Finnaly I end使用包装器保留数据库的原始功能（对于ASP.NET MembershipProvider），并为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 
  

现在我可以使用编译器检查商店程序中的参数的数量和类型。

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?

解决方案

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.

这篇关于将存储过程从LINQ迁移到SQL到实体框架6的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！