EF Core: Issue with creating FUNCTION in Ef Core Migration RSS

• • Reply
  tvb2727

  Participant

  1038 Points

  2817 Posts

  EF Core: Issue with creating FUNCTION in Ef Core Migration

  Jul 18, 2020 02:33 AM|tvb2727|LINK

  I can't seem to figure out why this error is occurring?Error SQL80001: Incorrect syntax: 'CREATE FUNCTION' must be the only statement in the batch. Miscellaneous Files 

  IF NOT EXISTS(SELECT * FROM [dbo].[_EFMigrationsHistory] WHERE [MigrationId] = N'20200718013559_CreateCombineResourcesFuncForScheduler07172020')
  BEGIN
      drop function if exists [dbo].[GetCombinedResources]
                     
                  
  END;
  
  GO
  
  IF NOT EXISTS(SELECT * FROM [dbo].[_EFMigrationsHistory] WHERE [MigrationId] = N'20200718013559_CreateCombineResourcesFuncForScheduler07172020')
  BEGIN
  
                              // ERROR IS RIGHT HERE
  
                              Create FUNCTION dbo.GetCombinedResources
                              (@resourceTypeName VARCHAR(max),
                              @scheduleId INT,
                              @scheduleEventId INT)
                              RETURNS VARCHAR(max)
                              AS
                              BEGIN													  
                              declare @result varchar(500)
                              set @result = ''

  I have not checked it into VSTS yet. I see this error when I complete:

  PM> script-migration -i

  Here is my migration:

          protected override void Up(MigrationBuilder migrationBuilder)
          {
              migrationBuilder.Sql(@"drop function if exists [dbo].[GetCombinedResources]
                 
              ");
  
              migrationBuilder.Sql(@"
                          
                          Create FUNCTION dbo.GetCombinedResources
                          (@resourceTypeName VARCHAR(max),
                          @scheduleId INT,
                          @scheduleEventId INT)
                          RETURNS VARCHAR(max)
                          AS
                          BEGIN													  
                          declare @result varchar(500)
                          set @result = ''
  
                          select @result = @result + query.Name + ', ' from
                          (
                          SELECT distinct Resources.Name   FROM 
  Events INNER JOIN Schedules ON Events.ScheduleId = ScheduleId and ScheduleEventId = @scheduleEventId
                          ) query
  
                          if (@result is null or @result = '')
  	                        BEGIN
  		                        return null	
  	                        END
  	
  	                    return LEFT(@result, NULLIF(LEN(@result)-1,-1))		
                      END
                    
              ");
          }