CODEWITHSUNDEEP
c#sqlentity-framework-coreasp.net-core-2.0
MorgoZ
MorgoZ

Reputation: 2052

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>

Upvotes: 35

Views: 56886

Answers (6)

Ogglas
Ogglas

Reputation: 69968

@Nkosi is correct but since EF Core 3.x you should use ExecuteSqlRaw like @KevinDimey says.

Addition to the answer of @KevinDimey is the obsolete messages:

ExecuteSqlCommand:

For the execution of SQL queries using plain strings, use ExecuteSqlRaw instead. For the execution of SQL queries using interpolated string syntax to create parameters, use ExecuteSqlInterpolated instead.

ExecuteSqlCommandAsync:

For the async execution of SQL queries using plain strings, use ExecuteSqlRawAsync instead. For the async execution of SQL queries using interpolated string syntax to create parameters, use ExecuteSqlInterpolatedAsync instead.

Methods to use:

context.Database.ExecuteSqlRaw("DELETE FROM [Blogs]");

await context.Database.ExecuteSqlRawAsync("DELETE FROM [Blogs]");

Upvotes: 5

Kevin Dimey
Kevin Dimey

Reputation: 729

For EF Core 3.x, use this namespace and this code :

using Microsoft.EntityFrameworkCore; 
...
context.Database.ExecuteSqlRaw("TRUNCATE TABLE [TableName]");

Upvotes: 22

Mohsin
Mohsin

Reputation: 732

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");

Upvotes: 4

CodeSmith
CodeSmith

Reputation: 3197

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

Upvotes: 7

Nkosi
Nkosi

Reputation: 247088

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

Upvotes: 39

Lalitha Sharma
Lalitha Sharma

Reputation: 19

You can try this

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

Upvotes: -4

Related Questions