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

I want to execute an SQL command from Entity Framework Core 2.0, but I can't figure out how to do so.

1.- The reason why I need to, is that I want to delete all data from a database table, and using Context.remove or Context.removeRange would produce many calls to DB (one for each data in the table).

2.- I've read that there is a method .ExecuteSqlCommand to accomplish that, but that method is not present in my Context.Database (maybe in Core 2.0 it was removed?). Here is the source of the info: Dropping table In Entity Framework Core and UWP

So, basically I need to delete a table from code using EF Core 2.0 and, as far as I know, I need to execute a SQL command for that.

Thank you.

Here is my .csproj, just in case i'm missing 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>

Answers


Ensure that you reference Microsoft.EntityFrameworkCore to include all the necessary extension methods that would allow you to execute raw SQL commands.

From the source repository I found ExecuteSqlCommand and related extension methods

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

Found an article that suggested using ADO.Net.

First you grab a connection from the context, create a command and execute that.

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

This will perform over any of delete row-per-row from table methods.

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

TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

ExecuteStoreCommand

TRUNCATE TABLE


Context.Database.ExecuteSqlCommand and Context.Database.ExecuteSqlCommandAsync are available in Microsoft.EntityFrameworkCore.Relational namespace. Make sure you have it's reference.

Then it will be available in your class and you can call it like below,

Context.ExecuteSqlCommand("TRUNCATE TABLE YOURTABLENAME");

You can try this

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

Need Your Help

Comparing the contents of two files in Sublime Text

compare sublimetext2 diff sublimetext sublime-text-plugin

I have two cloned repositories of two very similar open-source projects, which I have been working on in different instances in Sublime Text 2 to arrive at my desired result. Code from both of these

TFS: comparing shelveset files with local version

tfs tfs2008

When viewing files in a shelveset, I would like the option of comparing the files to my current local version. Unfortunately, the only options available are "With Unmodified", "With Workspace" (I g...