在Entity Framework Core 2.0中执行SQL命令以删除表中的所有数据 [英] Execute SQL command in Entity Framework Core 2.0 to delete all data in a table

问题描述

我想从Entity Framework Core 2.0中执行SQL命令，但是我不知道该怎么做.

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

1.-我需要这样做的原因是，我想从数据库表中删除所有数据，并且使用Context.remove或Context.removeRange会产生许多对DB的调用(表中的每个数据一个)

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.-我已经阅读到有一种方法.ExecuteSqlCommand可以完成该任务，但是该方法在我的Context.Database中不存在(也许在Core 2.0中已被删除?).这是信息的来源:在Entity Framework Core和UWP中删除表

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

因此，基本上，我需要使用EF Core 2.0从代码中删除表，据我所知，我需要为此执行一条SQL命令.

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.

谢谢.

这是我的.csproj，以防万一我丢失了一些东西

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>

推荐答案

请确保您引用Microsoft.EntityFrameworkCore包括所有必要的扩展方法，这些方法将允许您执行原始SQL命令.

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

在源存储库中，我找到了ExecuteSqlCommand及其相关的扩展方法

From the source repository I found ExecuteSqlCommand and related extension methods

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

找到了一篇建议使用ADO.Net的文章.

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

这篇关于在Entity Framework Core 2.0中执行SQL命令以删除表中的所有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！