locked
EF Core and SQL Functions RRS feed

  • Question

  • User-1188570427 posted

    Hello,

    I'm trying to update a SQL FUNCTION in my database via EF Core migration. 

    IF NOT EXISTS(SELECT * FROM [dbo].[_EFMigrationsHistory] WHERE [MigrationId] = N'20201108022316_UpdateCombineEventrFunction11072020')
    BEGIN
    
                                                /* Drop function if it already exists so we can recreate it */
                                                DROP FUNCTION if exists [dbo].[GetCombinedResources]                                
                                         
    END;
    
    GO
    
    IF NOT EXISTS(SELECT * FROM [dbo].[_EFMigrationsHistory] WHERE [MigrationId] = N'20201108022316_UpdateCombineEventrFunction11072020')
    BEGIN
    
                                                /* This will take the name of the Resource Type and use the Event id and Event event id to obtain a list of resources of the resource type and combine them together separated by a comma */
                                                CREATE FUNCTION [dbo].[GetCombinedResources]
                                                (@resourceTypeName VARCHAR(max),
                                                @EventId INT,
                                                @EventEventId INT)
                                                RETURNS VARCHAR(max)
                                                AS
                                                BEGIN													  
    
                                                Return (Select distinct dbo.EventResources.EventResourceId
                                                                                 FROM            dbo.Events INNER JOIN
                                                                                                           dbo.Events ON dbo.Events.EventId = dbo.Events.EventId INNER JOIN
                                                                                                           dbo.EventResourceTypes INNER JOIN
                                                                                                           dbo.EventResources INNER JOIN
                                                                                                           dbo.EventResourceEvents ON dbo.EventResources.EventResourceId = dbo.EventResourceEvents.EventResourceId ON 
                                                                                                           dbo.EventResourceTypes.EventResourceTypeId = dbo.EventResources.EventResourceTypeId ON 
                                                                                                           dbo.Events.EventId = dbo.EventResourceEvents.EventId
        														                                           where dbo.Events.EventId = @EventEventId and dbo.EventResourceTypes.Name = @resourceTypeName and dbo.Events.EventId = @EventId
                                                FOR JSON AUTO)
                                         
    END;
    
    GO
    
    IF NOT EXISTS(SELECT * FROM [dbo].[_EFMigrationsHistory] WHERE [MigrationId] = N'20201108022316_UpdateCombineEventrFunction11072020')
    BEGIN
        INSERT INTO [dbo].[_EFMigrationsHistory] ([MigrationId], [ProductVersion])
        VALUES (N'20201108022316_UpdateCombineEventrFunction11072020', N'2.2.1-servicing-10028');
    END;
    
    GO

    When I run `script-migration -i` to test it on my database, I see this error:

    Msg 156, Level 15, State 1, Line 1295
    Incorrect syntax near the keyword 'FUNCTION'.
    Msg 137, Level 15, State 2, Line 1311
    Must declare the scalar variable "@scheduleId".

    Incorrect syntax: 'CREATE FUNCTION' must be the only statement in the batch.

    Are FUNCTIONs not allow to be added within EF Core Migration?

    Sunday, November 8, 2020 2:57 AM

Answers

  • User-1188570427 posted

    DA924

    It looks to me that you are being explicitly told about the syntax error with the function. MS SQL Server is hollering about the syntax error. EF has nothing to do with it.

    You were correct.

    I ended up wrapping my CREATE FUNCTION using this:

    https://stackoverflow.com/questions/34994432/errorcreate-function-must-be-the-only-statement-in-the-batch

    EXEC sp_executesql N'
     -- your Create statement here
    ';
                migrationBuilder.Sql(@"
     EXEC sp_executesql N'          CREATE FUNCTION [DataTools].[GetCombinedResources]
                                                    .......
                                                    END  '
                                      ");



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 9, 2020 2:00 AM

All replies

  • User1120430333 posted

    It looks to me that you are being explicitly told about the syntax error with the function. MS SQL Server is hollering about the syntax error. EF has nothing to do with it.

    Sunday, November 8, 2020 9:03 AM
  • User-1188570427 posted

    DA924

    It looks to me that you are being explicitly told about the syntax error with the function. MS SQL Server is hollering about the syntax error. EF has nothing to do with it.

    You were correct.

    I ended up wrapping my CREATE FUNCTION using this:

    https://stackoverflow.com/questions/34994432/errorcreate-function-must-be-the-only-statement-in-the-batch

    EXEC sp_executesql N'
     -- your Create statement here
    ';
                migrationBuilder.Sql(@"
     EXEC sp_executesql N'          CREATE FUNCTION [DataTools].[GetCombinedResources]
                                                    .......
                                                    END  '
                                      ");



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 9, 2020 2:00 AM