更改DB迁移中的存储过程EF 6代码首先 - 如何通过空值作为参数的默认值 [英] Alter Stored Procedure in DB Migration EF 6 Code First - how to pass through null as default for a parameter

问题描述

我正在使用空迁移来更新数据库中的存储过程。存储过程是在数据库的初始创建中添加的自定义存储过程。

我在DbMigration类中发现了AlterStoredProcedure方法，这适用于更新存储过程，但是我必须通过存储过程的参数，并且我想设置一个布尔值的默认值，并将一些ints设置为null，但是我似乎无法让这个工作正常。

  AlterStoredProcedure（
 name：[dbo]。[FT_People_PersonFullTextSearch]，
 parametersAction：
p => ; new {
 searchTerm = p.String（600），
 isArchived = p.Boolean（false），
 isActive = p.Boolean（null），
 genderFilter = p。 int（null），
 rankingFilter = p.Int（null）
 }，
 body：我的存储过程的正文....）; 
  

上述代码生成

  ALTER PROCEDURE [dbo]。[FT_People_PersonFullTextSearch] 
 @searchTerm [nvarchar]（600），
 @isArchived [bit] = 0，
 @isActive [bit] ，
 @genderFilter [int]，
 @rankingFilter [int] 
 AS 
 BEGIN 
  

而不是

  ALTER PROCEDURE [dbo]。[FT_People_PersonFullTextSearch] 
 @searchTerm nvarchar（600）
 @isArchived bit = 0，
 @isActive bit = null，
 @genderFilter int = null，
 @rankingFilter int = null 
 AS 
 BEGIN 
  

有人知道如何获取参数以生成 @ isActive bit = null ？

解决方案

我正在使用Entity Framework 6.1.1，通过执行以下操作来实现：

  AlterStoredProcedure（
 name：[dbo]。[FT_People_PersonFullTextSearch] 
 parametersAction：
 p =>新的{
 searchTerm = p.String（600），
 isArchived = p.Boolean（false），
 isActive = p.Boolean（null，null），
 genderFilter = p.Int（null，null），
 rankingFilter = p.Int（null，null）
}，
 body：我的存储过程的正文。）; 
  

请注意，我刚刚将我的解决方案插入到您的示例代码中，我没有实际尝试运行这个确切的代码。

我设置的具体参数是 defaultValueSql：null。 p>

这给了我一个看起来像这样的存储过程：

  ALTER PROCEDURE [dbo]。[FT_People_PersonFullTextSearch] 
 @searchTerm nvarchar（600），
 @isArchived bit = 0，
 @isActive bit = null，
 @genderFilter int = null， 
 @rankingFilter int = null 
 AS 
 BEGIN 
  

I am using an Empty Migration to update a stored procedure in my database. The stored procedure is a custom stored proc that was added in the intial creation of the database.

I have discovered the 'AlterStoredProcedure' method in the DbMigration class and this works to update the stored procedure, however I have to pass through the parameters of the stored procedure and I want to set the default value of a boolean and some ints to null, but I can't seem to get this to work.

    AlterStoredProcedure(
                    name: "[dbo].[FT_People_PersonFullTextSearch]",
                    parametersAction: 
                       p => new { 
                                   searchTerm = p.String(600), 
                                   isArchived = p.Boolean(false), 
                                   isActive = p.Boolean(null), 
                                   genderFilter = p.Int(null), 
                                   rankingFilter = p.Int(null) 
                                 },
                    body: "the body of my stored proc....");

The above code produces

ALTER PROCEDURE [dbo].[FT_People_PersonFullTextSearch]
    @searchTerm [nvarchar](600),
    @isArchived [bit] = 0,
    @isActive [bit],
    @genderFilter [int],
    @rankingFilter [int]
AS
BEGIN

instead of

ALTER PROCEDURE [dbo].[FT_People_PersonFullTextSearch]
    @searchTerm nvarchar(600), 
    @isArchived bit = 0,
    @isActive bit = null,
    @genderFilter int = null,
    @rankingFilter int = null
AS 
BEGIN

Does anyone know how to get the parameters to produce @isActive bit = null?

解决方案

I am using Entity Framework 6.1.1 and I was able to achieve this by doing the following:

AlterStoredProcedure(
    name: "[dbo].[FT_People_PersonFullTextSearch]",
    parametersAction: 
        p => new { 
            searchTerm = p.String(600), 
            isArchived = p.Boolean(false), 
            isActive = p.Boolean(null, "null"), 
            genderFilter = p.Int(null, "null"), 
            rankingFilter = p.Int(null, "null") 
        },
    body: "the body of my stored proc....");

Note that I've just plugged in my solution into your example code, I haven't actually tried running this exact code.

The specific parameter I'm setting there is defaultValueSql: "null".

This gave me a stored procedure which looked a little like this:

ALTER PROCEDURE [dbo].[FT_People_PersonFullTextSearch]
    @searchTerm nvarchar(600), 
    @isArchived bit = 0,
    @isActive bit = null,
    @genderFilter int = null,
    @rankingFilter int = null
AS 
BEGIN

这篇关于更改DB迁移中的存储过程EF 6代码首先 - 如何通过空值作为参数的默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！