entity-framework - EF6 : Create stored procedure. 使用 Fluent API 还是 DBMigrations？

Question

我首先使用 EF6 代码来创建我的数据库。我了解语法、DbContext 和模型构建器。我使用 LINQ 进行了几个详尽的查询，一切正常。

但是现在我需要做一些使用 linq 在一个查询中无法完成的事情。我需要使用存储过程执行 Merge 语句。

我见过几个关于如何创建存储过程的问题，例如:
Create Stored Procedures using Entity Framework Code First?

大多数答案都在谈论为 DbMigrations 创建派生类并覆盖 Up() 函数。我明白我应该在 Up 函数中写什么来确保创建存储过程。

But what should I do to make that during database creation this Up function is called?

我应该在 DbContext.OnModelCreating 中做些什么吗？

我不认为我应该实例化 DbMigrations 的子类并调用 Up()。

上面提到的链接正在谈论“打开包管理器控件”。那是什么？或者你真的在从旧版本迁移到新版本时使用这种方法？

Answer 1

经过一番调查，我发现如何确保在创建数据库时创建存储过程。我发现了两种方法，每种方法都有自己的优点和缺点。因此我描述了他们两个。对不起，如果这使答案相当长。
这里描述的两种方法是:

创建数据库初始化器 ，一个实现 IDataBaseInitializer 的类。这可能是一个派生自 DropCreateDatabaseIfModelChanges 或类似的类。覆盖 Seed 函数并在该函数中使用 context.Database.ExecuteSqlCommand(...) 创建存储过程。

使用 Entity Framework 迁移 用于创建存储过程。

第一种方法更简单。无论何时创建数据库，都会调用 Seed 并创建存储过程。然而，这种方法的缺点是，每当存储过程的参数的名称或类型发生变化时，直到运行时才会检测到。
DbMigration 方法使用 lambda 表达式匹配存储过程的参数，因此只要参数的类型或名称发生变化，编译器就会检测远程过程的定义是否与参数匹配。
我将描述这两种方法。两个示例都具有相同的简单 Hello World!过程和一个带有很多参数的大合并过程。

The definition of the merge statement is not really important. What itdoes is that it checks if there is already a record matching severalproperties, and if so it adds costs to the existing costs. If not itcreates a record and initializes the costs with costs. This is atypical example where using linq statement and IQueryable wouldn't suffice.Using linq, one would have to retrieve the record, update it and callSaveChanges, with the problems (1) that inthe meantime someone else might have added a value and (2) it needs atleast two roundtrips. Hence the need for a stored procedure.

方法 IDatabaseInitializer
在您的项目中，您为要访问的数据库表创建实体类和派生自 DbContext 的类，该类具有 DbSet 属性。
例如:

public class UsageCosts
{
    public int Id {get; set; }
    public DateTime InvoicePeriod { get; set; }
    public long CustomerContractId { get; set; }
    public string TypeA { get; set; }
    public string TypeB { get; set; }
    public decimal VatValue { get; set; }

    // the value to invoice
    public decimal PurchaseCosts { get; set; }
    public decimal RetailCosts { get; set; }
}

public class DemoContext : DbContext
{
    public DemoContext(string nameOrConnectionString) : base(nameOrConnectionString) {}

    public DbSet<UsageCosts> UsageCosts { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        // add entity framework fluent api statements here          
    }
}

除了你的数据库类，创建一个数据库初始值设定项，它有一个在创建数据库时调用的函数 Seed。

internal class DataBaseInitializer : DropCreateDatabaseIfModelChanges<DemoContext>
{
    protected override void Seed(DemoContext context)
    {
        base.Seed(context);
        
        // create stored procedures here
        this.CreateStoredProcedureHelloWorld(context)
        this.CreateStoredProcedureUpdateUsageCosts(context)
    }

展示如何创建存储过程的简单示例(Hello World!)

    private void CreateStoredProcedureHelloWorld(DemoContext context)
    {
        context.Database.ExecuteSqlCommand("create procedure HelloWorld as begin Select 'Hello World' end;");
    }

创建一个带有输入参数的存储过程:

    private void CreateStoredProcedureUpdateUsageCosts(DemoContext context)
    {
        var x = new StringBuilder();
        x.AppendLine(@"create procedure updateusagecosts");
        x.AppendLine(@"@InvoicePeriod datetime,");
        x.AppendLine(@"@CustomerContractId bigint,");
        x.AppendLine(@"@TypeA nvarChar(80),");
        x.AppendLine(@"@TypeB nvarChar(80),");
        x.AppendLine(@"@VatValue decimal(18, 2),");
        x.AppendLine(@"@PurchaseCosts decimal(18, 2),");
        x.AppendLine(@"@RetailCosts decimal(18, 2)");
        x.AppendLine(@"as");
        x.AppendLine(@"begin");
        x.AppendLine(@"Merge [usagecosts]");
        x.AppendLine(@"Using (Select @InvoicePeriod as invoicePeriod,");
        x.AppendLine(@"              @CustomerContractId as customercontractId,");
        x.AppendLine(@"              @TypeA as typeA,");
        x.AppendLine(@"              @TypeB as typeB,");
        x.AppendLine(@"              @VatValue as vatvalue)");
        x.AppendLine(@"              As tmp ");
        x.AppendLine(@"On ([usagecosts].[invoiceperiod] = tmp.invoiceperiod");
        x.AppendLine(@"AND [usagecosts].[customercontractId] = tmp.customercontractid");
        x.AppendLine(@"AND [usagecosts].[typeA] = tmp.typeA");
        x.AppendLine(@"AND [usagecosts].[typeB] = tmp.typeB");
        x.AppendLine(@"AND [usagecosts].[vatvalue] = tmp.Vatvalue)");
        x.AppendLine(@"When Matched Then ");
        x.AppendLine(@"    Update Set [usagecosts].[purchasecosts] = [usagecosts].[purchasecosts] + @purchasecosts,");
        x.AppendLine(@"               [usagecosts].[retailcosts] = [usagecosts].[retailcosts] + @retailcosts");
        x.AppendLine(@"When Not Matched Then");
        x.AppendLine(@"    Insert (InvoicePeriod, CustomerContractId, typea, typeb, vatvalue, purchasecosts, retailcosts)");
        x.AppendLine(@"    Values (@invoiceperiod, @CustomerContractId, @TypeA, @TypeB, @VatValue, @PurchaseCosts, @RetailCosts);");
        x.AppendLine(@"end");
        context.Database.ExecuteSqlCommand(x.ToString());
    }
}

The hello world example can be found here on StackOverflow
使用 StringBuilder 的方法也可以在 StackOverflow 上的某个地方找到，但可惜我找不到它。
在创建数据库时 DatabaseInitializer.Seed(...) 被调用。这里的上下文被命令执行 SQL 语句。这个语句是一个字符串。
这就是为什么编译器不会注意到函数参数的名称或类型的变化。
数据库迁移方法
有关迁移，请参阅:

MSDN: Enabling Migrations

Creating and Calling Stored Procedure from Entity Framework 6 Code First

这个想法是让 Visual Studio 包管理器创建一个 DbManager 的派生类，它有一个 Up() 函数。每当数据库向上迁移到派生类的版本时，都会调用此函数。
在 Up() 中，您可以调用基类 DbMigration.CreateStoredProcedure。这种方法的好处是，从实体类型到参数的转换是使用委托(delegate)(使用 lambda 表达式)完成的，因此在编译时进行检查:属性是否仍然存在并且它们是否具有正确的类型？
唉，仅从 DbMigration 构造派生类并从 Seed() 函数内部调用 Up() 函数是不够的。
为了确保调用 Up() 函数，最容易让 Visual Studio 执行此操作。

创建您的项目

为 Entity Framework 添加 Nuget 包

使用实体类的 DbSet 属性创建实体类和 DbContext

在 Visual Studio 中，通过工具菜单启动 Nuget 包管理器控制台

使用 Nuget 包管理器控制台，使用命令 启用迁移启用迁移

使用 Nuget 包管理器控制台添加一个迁移并给出名称，例如使用命令 的 InitialCreation add-Migration InitialCreation

您会注意到有几个类添加到您的项目中。

配置从带有 Seed() 函数的 DbMigratinConfiguration 派生

初始创建 派生自带有函数 Up()(和函数 Down())的 DbMigration。在这个 Up 中，您将看到一个或多个 CreateTable 函数

如果您仍然有前面示例中描述的数据库播种器类，并且您使用 DataBase.SetInitializer 对其进行初始化，那么无论何时需要重新创建数据库，都会在下面调用各种 Up() 和 Seed() 函数命令:

配置构造函数

InitialCreation.Up()

DatabaseSeeder.Seed()

出于某种原因，没有调用 Configuration.Seed()。
这让我们有机会在 InitialCraeation.Up() 中创建存储过程

public override void Up()
{
    CreateTable("dbo.UsageCosts",
        c => new
            {
                Id = c.Int(nullable: false, identity: true),
                InvoicePeriod = c.DateTime(nullable: false),
                CustomerContractId = c.Long(nullable: false),
                TypeA = c.String(),
                TypeB = c.String(),
                VatValue = c.Decimal(nullable: false, precision: 18, scale: 2),
                PurchaseCosts = c.Decimal(nullable: false, precision: 18, scale: 2),
                RetailCosts = c.Decimal(nullable: false, precision: 18, scale: 2),
            })
        .PrimaryKey(t => t.Id);
}

“Hello World”存储过程创建如下:

    base.CreateStoredProcedure("dbo.HelloWorld3", "begin Select 'Hello World' end;");

带输入参数的存储过程:

    base.CreateStoredProcedure("dbo.update2", p => new
    {
        InvoicePeriod = p.DateTime(),
        CustomerContractId = p.Long(),
        TypeA = p.String(maxLength: 80),
        TypeB = p.String(maxLength: 80),
        VatValue = p.Decimal(10, 8),
        PurchaseCosts = p.Decimal(10, 8),
        RetailCosts = p.Decimal(10, 8),
    },
    @"begin
        Merge [usagecosts]
        Using (Select
            @InvoicePeriod as invoicePeriod,
            @CustomerContractId as customercontractId,
            @TypeA as typeA,
            @TypeB as typeB,
            @VatValue as vatvalue)
            As tmp 
        On ([usagecosts].[invoiceperiod] = tmp.invoiceperiod
        AND [usagecosts].[customercontractId] = tmp.customercontractid
        AND [usagecosts].[typeA] = tmp.typeA
        AND [usagecosts].[typeB] = tmp.typeB
        AND [usagecosts].[vatvalue] = tmp.Vatvalue)
    When Matched Then 
        Update Set [usagecosts].[purchasecosts] = [usagecosts].[purchasecosts] + @purchasecosts, [usagecosts].[retailcosts] = [usagecosts].[retailcosts] + @retailcosts
    When Not Matched Then
        Insert (InvoicePeriod, CustomerContractId, typea, typeb, vatvalue, purchasecosts, retailcosts)
        Values (@invoiceperiod, @CustomerContractId, @TypeA, @TypeB, @VatValue, @PurchaseCosts, @RetailCosts);
     end;");
}

记住 Down()方法:

    public override void Down()
    {
        this.DropStoredProcedure("dbo.update2");
    }

为了完整性:远程过程调用

using (var dbContext = new DemoContext())
{
    object[] functionParameters = new object[]
    {
        new SqlParameter(@"InvoicePeriod", usageCosts.InvoicePeriod),
        new SqlParameter(@"CustomerContractId", usageCosts.CustomerContractId),
        new SqlParameter(@"TypeA", usageCosts.TypeA),
        new SqlParameter(@"TypeB", usageCosts.TypeB),
        new SqlParameter(@"VatValue", usageCosts.VatValue),
        new SqlParameter(@"PurchaseCosts", 20M),
        new SqlParameter(@"RetailCosts", 30M),
    };
    string sqlCommand = String.Format(@"Exec {0} @InvoicePeriod, @CustomerContractId, @TypeA, @TypeB, @VatValue, @PurchaseCosts, @RetailCosts", functionName);
    dbContext.Database.ExecuteSqlCommand(sqlCommand, functionParameters);
    dbContext.SaveChanges();
}

在我看来，最好将它放在 DbSet 的扩展方法中。每当 UsageCosts 更改时，编译器就可以检查名称和属性类型。