Unable to make raw SQL calls from Entity Framework Core

I am working on a project where I am moving specific rows from a table between multiple different environments. The table has an Identity column and when I try to move it I get an error that Identity_Insert is off.

Looking at several of the questions on here one of the solutions has been to call Context.Database.ExecuteSqlCommand(), ExecuteSqlRaw() or the async variations of them. However when I attempt to call them in my code I get an error indicating

‘DatabaseFacade’ does not contain a definition for ‘ExecuteSqlCommand’ and no accessible extension method could be found

and the only execute option I even see in the intellisense list is CreateExecutionStrategy.

I have the Nuget packages Microsoft.EntityFrameworkCore, Microsoft.EntityFrameworkCore.Relational, Microsoft.EntityFrameworkCore.SqlServer and the tools and abstractions packages all installed so I am not sure why I cannot see any of these options which are present and set to version 5.0.16.

Any ideas on what package I may be missing in order to access these methods? I even tried installing the nuget package for EF6 but that didn’t help either.

Code snippet

using (SchoolSystemContext sourceContext = new SchoolSystemContext(sourceDB), targetContext = new SchoolSystemContext(targetDB))
            {
                var student = sourceContext.Students.FirstOrDefault(s => s.StudentId == 1000);

                if (student == null)
                {
                    try
                    { 
                        targetContext.Students.Add(student);
                        targetContext.Database.ExecuteSqlCommand("SET Identity_Insert Student ON");
                        targetContext.SaveChanges();
                        targetContext.Database.ExecuteSqlCommand("SET Identity_Insert Student OFF");
                    }
                    catch
                    {
                        Console.WriteLine();
                    }
                    
                }
            }

​x
 
using (SchoolSystemContext sourceContext = new SchoolSystemContext(sourceDB), targetContext = new SchoolSystemContext(targetDB))            {                var student = sourceContext.Students.FirstOrDefault(s => s.StudentId == 1000);​                if (student == null)                {                    try                    {                         targetContext.Students.Add(student);                        targetContext.Database.ExecuteSqlCommand("SET Identity_Insert Student ON");                        targetContext.SaveChanges();                        targetContext.Database.ExecuteSqlCommand("SET Identity_Insert Student OFF");                    }                    catch                    {                        Console.WriteLine();                    }                                    }            }​

Error message Image

Answer

Not Entirely sure what changed but coming back to this a few days ago I rebuilt the Application again and it was still giving me errors about ExecuteSqlCommand() not being defined, but I also added in calls to Database.OpenConnection() and Database.CloseConnection() which were missing originally. Once those were added in I could see the ExecuteSqlRaw() Method in intellisense and inserting that into the code fixed my issues.

Advertisement

Answer