Alter Stored Procedure in DB Migration EF 6 Code First - how to pass through null as default for a parameter

c# ef-migrations entity-framework-6

Question

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?

1
4
5/7/2014 11:56:35 AM

Accepted Answer

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
4
8/20/2014 12:10:21 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow