Run SQL command in Entity Framework Core 2.0 to delete all data in a table

Question

I want to execute an SQL command from Entity Framework Core 2.0, but I cannot figure out how to do this.

1.- The reason I need it is because I want to delete all the data from the database table, and using Context.removeor Context.removeRangewill lead to numerous calls to the database (one for each information in the table).

2.- I read that there is a method for this .ExecuteSqlCommand, but this method is not in my Context.Database (maybe it was deleted in Core 2.0?). Here is the source of the information: Drop table in Entity Framework Core and UWP

So basically I need to remove the table from the code using EF Core 2.0, and as far as I know, I need to execute the SQL command for this.

Thanks.

Here is my .csproj, just in case I missed something

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>netcoreapp2.0</TargetFramework>
    <AssetTargetFallback>$(AssetTargetFallback);portable-net45+win8+wp8+wpa81;</AssetTargetFallback>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.0" PrivateAssets="All" />
    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.0.0" PrivateAssets="All" />    
  </ItemGroup>

  <ItemGroup>
    <!--<PackageReference Include="Microsoft.ApplicationInsights.AspNetCore" Version="2.1.1" />    -->
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />
    <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.0" />
  </ItemGroup>

</Project>

Answer 1

Make sure you refer to Microsoft.EntityFrameworkCoreto include all the necessary extension methods that will allow you to execute raw SQL commands.

From the source repository I also found ExecuteSqlCommandextension methods associated with it

int count = await context.Database.ExecuteSqlCommandAsync("DELETE FROM [Blogs]");

Found an article that suggested using ADO.Net.

First, you get the connection out of context, create the command, and execute it.

using (var connection = context.Database.GetDbConnection()) {
    await connection.OpenAsync();     
    using (var command = connection.CreateCommand()) {
        command.CommandText = "DELETE FROM [Blogs]";
        var result = await command.ExecuteNonQueryAsync();
    }
}

Answer 2

.

context.ExecuteStoreCommand("TRUNCATE TABLE [" + tableName + "]");

ExecuteStoreCommand

TRUNCATE

Answer 3

You can try this

 context.Patients.ToList().ForEach(v => v.ChangeTracker.State = ObjectState.Deleted);
 context.SaveChanges();