title: Entity Framework Core Run Custom Scripts
body: <p>Each time my site starts, I want to run X number of scripts.  The last thing the <code>Startup.Configure</code> method will do is call my method:</p>
  
  <pre><code>DbInitializer.Initialize(context);
  
  if (env.IsDevelopment()) {
    scopeFactory.SeedData(Configuration.GetConnectionString("AlmanacSQL"));
  }
  </code></pre>
  
  <p>Here is my method:</p>
  
  <pre><code>public static void SeedData(this IServiceScopeFactory scopeFactory, string connectionString) {
    using (var serviceScope = scopeFactory.CreateScope()) {
      var context = serviceScope.ServiceProvider.GetService&lt;AlmanacDb&gt;();
  
      foreach (var f in di.GetFiles("*.sql").OrderBy(x =&gt; x.Name)) {
        context.Database.ExecuteSqlQuery(File.ReadAllText(f.FullName));
      }
    }
  }
  </code></pre>
  
  <p>It works great until it tries to run the first script and fails with: </p>
  
  <p>'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.</p>
  
  <p>I have seen this <a href="https://stackoverflow.com/a/37750926/373438">answer</a> here but that is specific to a Migration.  Other answers did not help in my situation.  I simply want to run these each time.  The scripts will be smart enough to not duplicate what they are doing.</p>
  
  <p>I tried taking out the <code>GO</code> statements and that did not do the trick.</p>
  
  <p>Is there a way to execute SQL Scripts in Entity Framework <strong>Core</strong> that have <code>GO</code> statements within them?  Is there some setting I can swap in SQL or EF?  Is there a deeper underlying context like the following that I have used in EF 6?</p>
  
  <pre><code>public ObjectContext UnderlyingContext { get { return ((IObjectContextAdapter)this).ObjectContext; } }
  </code></pre>
  
  <p><strong>EDIT</strong>
  Right after I got back into my code I figure I should at least show the script:</p>
  
  <pre><code>IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'GetEnumTriplet') AND type IN (N'P', N'PC'))
    DROP PROCEDURE GetEnumTriplet
  GO
  
  CREATE PROCEDURE GetEnumTriplet
  (
    @Enumeration NVARCHAR(25),
    @EnumName    NVARCHAR(25) = NULL,
    @EnumValue   INT = NULL
  )
  AS
  BEGIN
  
    DECLARE @SQL     NVARCHAR(250)
    DECLARE @PDs     NVARCHAR(100) = N'@OutValue INT OUTPUT, @OutDisplay NVARCHAR(30) OUTPUT, @OutName NVARCHAR(25) OUTPUT'
    DECLARE @Value   INT = 0
    DECLARE @Display NVARCHAR(30)
    DECLARE @Name    NVARCHAR(25)
  
    IF (@EnumName IS NOT NULL)
      SET @SQL = 'SELECT @OutValue = e.ID, @OutDisplay = e.Name, @OutName = e.EnumName FROM ref.' + @Enumeration + ' AS e WHERE e.EnumName = ' + '''' + @EnumName + ''''
    ELSE IF (@EnumValue IS NOT NULL)
      SET @SQL = 'SELECT @OutValue = e.ID, @OutDisplay = e.Name, @OutName = e.EnumName FROM ref.' + @Enumeration + ' AS e WHERE e.ID = ' + CAST(@EnumValue AS NVARCHAR)
  
    EXECUTE sp_executesql
      @SQL, @PDs, @OutValue = @Value OUTPUT, @OutDisplay = @Display OUTPUT, @OutName = @Name OUTPUT;
  
    -- Test Content
  
    SELECT
      1            AS ID,
      @Value       AS Value,
      @Display     AS DisplayName,
      @Name        AS KeyName,
      @Enumeration AS EnumerationName
  
  /*
  GetEnumTriplet 'ProjectStatus', 'In Progress' -- Wrong
  GetEnumTriplet 'ProjectStatus', 'InProgress', 3 -- Will default to looking up InProgress and skip looking up 3
  GetEnumTriplet 'ProjectStatus', 'InProgress'
  GetEnumTriplet 'ProjectStatus', NULL, 2
  */
  END
  GO
  </code></pre>