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!

Answer 3

Based on Juanu answer here is a complete solution that works with Entity Frame Core 5.

I use it to setup the database before running the test.

    private void SetupTestData()
    {
        var sql = System.IO.File.ReadAllText("SetupEntities.sql");
        string[] commands = sql.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);
        DbConnection conn = _dbContext.Database.GetDbConnection(); // Get Database connection
        var initialConnectionState = conn.State;
        try
        {
            if (initialConnectionState != 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 (initialConnectionState != ConnectionState.Open)
                conn.Close(); // only close connection if not initially open
        }
    }

Answer 4

Keep is simple

using (var context = new MyDBEntities())
{
    var m = context.ExecuteStoreQuery<MyDataObject>("Select * from Person", string.Empty);
    //Do anything you want to do with 
    MessageBox.Show(m.Count().ToString());
}

Answer 5

Cant believe no one answered this. I'm trying to figure this out now. I guess one way you can do this is to read the script into a string and then executestorecommand on that. What I am trying to figure out is what are the limits on this. Are all TSQL statements other than GO allowed? Or is this something you are better off use sqlcmd.exe to run. Here's best solution I found - Use SMO to run the script:

http://social.msdn.microsoft.com/Forums/en/sqlsmoanddmo/thread/44835d6f-6bca-4374-93e2-3a0d81280781

Answer 6

Why don't you simply make SqlConnection as a simple ADO.NET connection to execute SQL against your db in test environment. As there will be very few and simple SQL statements and you are not going to deploy or export your test anywhere outside your development premises. I don't think there is any need to do it through entity framework.

Answer 7

In Entity Framework Core 6 this works for me (inspired by other answers)

var connectionString = @"<connectionString>";

using var context = new DatabaseContext(
    new DbContextOptionsBuilder<DatabaseContext>()
        .UseSqlServer(connectionString).Options);

context.Database.EnsureDeleted();
context.Database.EnsureCreated();

var sql = File.ReadAllText(@"<path>/script.sql");

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

foreach (var command in commands)
{
    context.Database.ExecuteSqlRaw(command);
}