'CREATE FUNCTION' must be the first statement in a query batch. Entity Framework Code First

Question

I have some inline SQL Scripts (functions and stored procedures) generated with Entity framework , Code first approach.

Update-Database -Script -SourceMigration:0

With the above command I get SQL Script file that I execute on test or production.

However I cannot run the generated script because of the following error:

'CREATE FUNCTION' must be the first statement in a  query batch. 

The script is generated as:

IF @CurrentMigration < '201410150019333_CreatefnGenerateRequestCode' BEGIN CREATE FUNCTION [dbo].[fnGenerateRequestCode] ( @userID varchar(max) )
RETURNS varchar(14)
as

How can I fix this?

Answer 1

You have to generate your code and execute it as dynamic sql.

DECLARE @Sql NVARCHAR(MAX)
SET @Sql = 
'
IF OBJECT_ID(''fn_Test'') IS NOT NULL DROP FUNCTION fn_Test
GO

CREATE FUNCTION fn_Test(@a INT)
RETURNS INT
BEGIN
    RETURN @a
END
'
IF 1 = 1
BEGIN
    EXEC(@Sql)
END