你能使用Entity Framework 4.1 Code First方法创建SQL视图/存储过程吗？

Question

你能使用Entity Framework 4.1 Code First方法创建SQL视图/存储过程吗？

c#entity-framework-4.1entity-framework-migrationscode-first

50

Entity Framework 4.1 Code First在创建表和关系方面效果很好。使用Code First方法是否可以创建SQL视图或存储过程？如有任何指针，请提供帮助。非常感谢！

- user629161

7个回答

28

乍一看，我确实很喜欢Carl G的方法，但它需要大量手动交互。在我的情况下，每当数据库发生变化时，我总是删除所有存储过程、视图等，并重新创建它们。这样，我们可以确保一切都是最新版本。

重新创建是通过设置以下初始化程序完成的：

Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext, Configuration>());

当准备迁移时，我们的种子方法将被调用。

protected override void Seed(DeploymentLoggingContext context)
    {
        // Delete all stored procs, views
        foreach (var file in Directory.GetFiles(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Sql\\Seed"), "*.sql"))
        {
            context.Database.ExecuteSqlCommand(File.ReadAllText(file), new object[0]);
        }

        // Add Stored Procedures
        foreach (var file in Directory.GetFiles(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Sql\\StoredProcs"), "*.sql"))
        {
            context.Database.ExecuteSqlCommand(File.ReadAllText(file), new object[0]);
        }
    }

为了方便编辑，SQL语句被存储在*.sql文件中。确保您的文件的"Build Action"设置为"Content"，并将"Copy to Output Directory"设置为"Copy Always"。我们会查找文件夹并执行其中的所有脚本。不要忘记在您的SQL中排除"GO"语句，因为这些语句无法使用ExecuteSqlCommand()执行。

我的当前目录布局如下:

Project.DAL
+ Migrations
+ Sql
++ Seed
+++ dbo.cleanDb.sql
++ StoredProcs
+++ dbo.sp_GetSomething.sql

现在，您只需要将额外的存储过程放入文件夹中，一切都会得到适当的更新。

- emp

1

谢谢分享。这与我正在尝试做的类似。 - Chuck Conway

2

不错的回答，但是删除和创建索引？每次应用程序运行时都要这样做吗？真的吗？ - Martin Capodici

2

如果您将文件文本按“GO”拆分并逐个执行数组中的每个字符串，则可以在脚本中保留“GO”语句。我建议使用 new Regex("GO", RegexOptions.IgnoreCase) 并跳过执行空字符串。 - Kevin Rood

1

@emp 我们的部署由发布管理管理 - 它们只接受更改脚本。开发人员绝不可能被允许使用软件包管理器控制台将更改推送到生产环境。 - Dave Lawrence

1

当您存储过程的最新版本与先前版本的数据库不兼容并且必须返回到先前版本时，该如何处理？例如：您在迁移中添加了一个新列，并更新了存储过程以使用它。稍后，您发现应用程序存在问题，因此您回滚迁移并从先前发布的应用程序代码重新部署。现在您的新列不存在，但存储过程仍然是最新版本，那么它不会因缺少列而失败吗？ - Carl G

显示剩余6条评论

13

为了扩展bbodenmiller的答案, 在Entity Framework 6中，DbMigration类有一些方法，例如AlterStoredProcedure，允许修改存储过程而不必降到原始SQL的级别。

下面是一个示例Up()迁移方法，它修改了一个名为EditItem的现有SQL Server存储过程，该存储过程分别采用int、nvarchar(50)和smallmoney三种类型的参数。

public partial class MyCustomMigration : DbMigration
{
    public override void Up()
    {
        this.AlterStoredProcedure("dbo.EditItem", c => new
        {
            ItemID = c.Int(),
            ItemName = c.String(maxLength:50),
            ItemCost = c.Decimal(precision: 10, scale: 4, storeType: "smallmoney")
        }, @" (Stored procedure body SQL goes here) "   
    }

    //...
}

在我的电脑上，这个迁移脚本会生成以下SQL语句：

ALTER PROCEDURE [dbo].[EditItem]
    @ItemID [int],
    @ItemName [nvarchar](50),
    @ItemCost [smallmoney]
AS
BEGIN
    (Stored procedure body SQL goes here)
END

- Jon Schneider

虽然我喜欢Carl的答案，但维护起来似乎需要付出很多努力，而回报却不是很大。谢谢！ - Jon Peterson

12

EF的代码优先方法期望数据库中没有逻辑，也就是说没有存储过程和数据库视图。因此，代码优先方法不提供任何自动为您生成这些构造的机制。如果它意味着生成逻辑，那么它怎么能做到呢？

您必须通过手动执行创建脚本，在自定义数据库初始化程序中自己创建它们。我认为这些自定义SQL结构不能由SQL迁移处理。

- Ladislav Mrnka

是的 - 在EF Code First中，View的等效物是带有数据副本的表。您的代码负责维护该辅助表。 - Chris Moschini

29

很抱歉持不同意见，但我认为必须考虑创建存储过程。在我的观点中，这是一个缺失的功能，应该被添加。存储过程是数据库和视图的一部分。虽然它们不必被过度使用，但它们确实存在，并且必须被视为数据库世界中的重要组成部分。 - Eugenio Miró

3

是的，这是正确的，但在这种情况下，您应该使用数据库优先方法，而不是代码优先。如果您想创建数据库逻辑，请直接在数据库中进行操作，让EF从那个数据库创建模型，而不是在反向方向上进行操作。 - Ladislav Mrnka

可以先使用FluentAPI的数据库方法吗？ - Chuck Conway

可以使用迁移EF5+中的DbMigration.Sql方法将存储过程（或任何任意数据库结构）添加到数据库中。然而，Code First仍然没有跟踪它们的机制。 - Jesan Fafon

11

看起来文档不太好，但是似乎现在可以使用Entity Framework 6中的AlterStoredProcedure, CreateStoredProcedure, DropStoredProcedure, MoveStoredProcedure, RenameStoredProcedure进行一些存储过程操作。我还没有尝试过它们，所以还不能给出如何使用它们的示例。

- bbodenmiller

1

你可以在这里找到一个例子：http://www.c-sharpcorner.com/UploadFile/ff2f08/code-first-stored-procedure-entity-framework-6-0/ - Jas

文档仍然不够完善。感谢您的示例，Jas。 - Darrell Lloyd Harvey

2

emp的设计非常出色！我正在使用他的模式，但是我也将存储过程映射到我的DbContext类中，这样就可以简单地调用这些上下文方法，而不是使用SqlQuery()从我的仓库直接调用存储过程。随着应用程序的增长，有时会变得有点棘手，因此我在我的Seed方法中创建了一个检查，确保实际的存储过程参数数量与映射方法的参数数量匹配。我还更新了emp提到的DROP循环。不必再维护一个单独的文件夹/文件来获取删除语句，现在只需读取每个sql文件的第一行，将CREATE替换为DROP（请确保第一行始终只是CREATE PROCEDURE ProcName），这样StoredProcs文件夹中的所有存储过程都会在每次运行Update-Database时被删除并重新创建。删除还包装在try-catch块中，以防存储过程是新的。对于过程参数计数要正常工作，需要确保你在你的tsql周围包裹一个块，因为文件的每一行都会读到BEGIN。还要确保每个sp参数都在新行上。

        // Drop Stored Procs
        foreach (var file in Directory.GetFiles(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\DataContext\\SiteMigrations\\StoredProcs"), "*.sql"))
        {
            // Try to drop proc if its already created
            // Without this, for new procs, seed method fail on trying to delete
            try
            {
                StreamReader reader = new StreamReader(file);
                // Read first line of file to create drop command (turning CREATE [dbo].[TheProc] into DROP [dbo].[TheProc])
                string dropCommand = reader.ReadLine().Replace("CREATE", "DROP");

                context.Database.ExecuteSqlCommand(dropCommand, new object[0]);
            }
            catch { }

        }

        // Add Stored Procs
        foreach (var file in Directory.GetFiles(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\DataContext\\SiteMigrations\\StoredProcs"), "*.sql"))
        {
            // File/Proc names must match method mapping names in DbContext
            int lastSlash = file.LastIndexOf('\\');
            string fileName = file.Substring(lastSlash + 1);
            string procName = fileName.Substring(0, fileName.LastIndexOf('.'));

            // First make sure proc mapping in DbContext contain matching parameters.  If not throw exception.
            // Get parameters for matching mapping
            MethodInfo mi = typeof(SiteContext).GetMethod(procName);

            if (mi == null)
            {
                throw new Exception(String.Format("Stored proc mapping for {0} missing in DBContext", procName));
            }

            ParameterInfo[] methodParams = mi.GetParameters();
            // Finished getting parameters

            // Get parameters from stored proc
            int spParamCount = 0;
            using (StreamReader reader = new StreamReader(file))
            {
                string line;                    
                while ((line = reader.ReadLine()) != null) 
                {
                    // If end of parameter section, break out
                    if (line.ToUpper() == "BEGIN")
                    {
                        break;
                    }
                    else
                    {
                        if (line.Contains("@"))
                        {
                            spParamCount++;
                        }
                    }                        
                }
            }
            // Finished get parameters from stored proc

            if (methodParams.Count() != spParamCount)
            {
                string err = String.Format("Stored proc mapping for {0} in DBContext exists but has {1} parameter(s)" +
                    " The stored procedure {0} has {2} parameter(s)", procName, methodParams.Count().ToString(), spParamCount.ToString());
                throw new Exception(err);
            }
            else
            {
                context.Database.ExecuteSqlCommand(File.ReadAllText(file), new object[0]);
            }
        }

享受吧！

- Natas0007

1

正如Ladislav所指出的那样，DbContext通常会尽量减少数据库中的逻辑，但是可以通过使用context.Database.ExecuteSqlCommand()或context.Database.SqlQuery()来执行自定义SQL。

- Mark Stafford - MSFT

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Carl G · Accepted Answer

我们在Entity Framework Code First Migrations中支持存储过程。我们的方法是创建一个文件夹来保存.sql文件（例如~/Sql/）。在文件夹中创建用于创建和删除存储过程的.sql文件，例如Create_sp_DoSomething.sql和Drop_sp_DoSomething.sql。因为SQL在批处理中运行，CREATE PROCEDURE..必须是批处理中的第一条语句，所以将CREATE PROCEDURE...放在文件的第一条语句。另外，在DROP...之后不要加GO。如果您还没有资源文件，请将其添加到项目中。将.sql文件从解决方案资源管理器拖到资源设计器的文件视图中。现在创建一个空迁移（Add-Migration SomethingMeaningful_sp_DoSomething），并使用：

namespace MyApplication.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class SomethingMeaningful_sp_DoSomething : DbMigration
    {
        public override void Up()
        {
            this.Sql(Properties.Resources.Create_sp_DoSomething);
        }

        public override void Down()
        {
            this.Sql(Properties.Resources.Drop_sp_DoSomething);
        }
    }
}

~/Sql/Create_sp_DoSomething.sql

CREATE PROCEDURE [dbo].[sp_DoSomething] AS
BEGIN TRANSACTION
-- Your stored procedure here
COMMIT TRANSACTION
GO

~/Sql/Drop_sp_DoSomething.sql

DROP PROCEDURE [dbo].[sp_DoSomething]