'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();
                    }
                    
                }
            }

Error message Image enter image description here

sqlentity-framework-core


Solution 1:[1]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 M_Cole84

Related Questions