Can Entity Framework model be passed a SQL script to be run against the database

Question

Is it possible to pass a SQL script to some method that Entity Framework has to run it against my model? e.g. equivalent of:

context.ExecuteStoreCommand(<tsql script path>); 

Background: I want a way to reset the database during unit tests, and making a call to run the EF generated TSQL script (from Generate Database from Model) seems one way to achieve this.

Answer 1

I have some simple code that fires sql like this:

if (!_context.CableSweepDebug.Any(rec => /* CHECK TO SEE IF SQL ALREADY RUN */ ))
{
    var sql = System.IO.File.ReadAllText("SqlScript.sql");
    _context.Database.ExecuteSqlCommand(sql);
}

Answer 2

I found a simple WAY:

1. Get your SQL script into a string variable:

   string result = "";
   using (Stream stream = assembly.GetManifestResourceStream(resourceName))
   {
       using (StreamReader reader = new StreamReader(stream))
       {
           result = reader.ReadToEnd();
       }
   }
   

2. Next, split your string using GO as the separator:

   string[] commands = result.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);
   

3. Last, execute each command using the exact same order, using the Database Connection from your Context (contains code from https://stackoverflow.com/a/1579220):

   YourContext context = new YourContext(); //Instance new Context
   DbConnection conn = context.Database.Connection; // Get Database connection
   ConnectionState initialState = conn.State; // Get Initial connection state
   try
   {
       if (initialState != ConnectionState.Open)
           conn.Open();  // open connection if not already open
   
       using (DbCommand cmd = conn.CreateCommand())
       {
           // Iterate the string array and execute each one.
           foreach (string thisCommand in commands)
           {
               cmd.CommandText = thisCommand;
               cmd.ExecuteNonQuery();
           }
       }
   }
   finally
   {
       if (initialState != ConnectionState.Open)
           conn.Close(); // only close connection if not initially open
   }
   

This is the way I made it work. Hope it helps!