C# 实体框架。删除表中的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15220411/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Entity Framework. Delete all rows in table
提问by Zhenia
How I can quickly remove all rows in table using Entity Framework?
如何使用实体框架快速删除表中的所有行？
I am currently using:
我目前正在使用：
var rows = from o in dataDb.Table
select o;
foreach (var row in rows)
{
dataDb.Table.Remove(row);
}
dataDb.SaveChanges();
However, it takes a long time to execute.
但是，执行需要很长时间。
Are there any alternatives?
有没有其他选择？
回答by Rudi Visser
Using SQL's TRUNCATE TABLEcommand will be the fastest as it operates on the table and not on individual rows.
使用 SQL 的TRUNCATE TABLE命令将是最快的，因为它对表而不是单个行进行操作。
dataDb.ExecuteStoreCommand("TRUNCATE TABLE [Table]");
Assuming dataDbis a DbContext(not an ObjectContext), you can wrap it and use the method like this:
假设dataDb是一个DbContext（不是一个ObjectContext），你可以包装它并使用这样的方法：
var objCtx = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)dataDb).ObjectContext;
objCtx.ExecuteStoreCommand("TRUNCATE TABLE [Table]");
回答by Manish Mishra
using (var context = new DataDb())
{
var ctx = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)context).ObjectContext;
ctx.ExecuteStoreCommand("DELETE FROM [TableName] WHERE Name= {0}", Name);
}
or
或者
using (var context = new DataDb())
{
context.Database.ExecuteSqlCommand("TRUNCATE TABLE [TableName]");
}
回答by Ron Sijm
For those that are googling this and ended up here like me, this is how you currently do it in EF5 and EF6:
对于那些像我一样在谷歌上搜索并最终来到这里的人，这就是您目前在 EF5 和 EF6 中的做法：
context.Database.ExecuteSqlCommand("TRUNCATE TABLE [TableName]");
Assuming context is a System.Data.Entity.DbContext
假设上下文是 System.Data.Entity.DbContext
回答by user3328890
var all = from c in dataDb.Table select c;
dataDb.Table.RemoveRange(all);
dataDb.SaveChanges();
回答by Zakos
if
如果
using(var db = new MyDbContext())
{
await db.Database.ExecuteSqlCommandAsync(@"TRUNCATE TABLE MyTable"););
}
causes
原因
Cannot truncate table 'MyTable' because it is being referenced by a FOREIGN KEY constraint.
无法截断表“MyTable”，因为它正被 FOREIGN KEY 约束引用。
I use this :
我用这个：
using(var db = new MyDbContext())
{
await db.Database.ExecuteSqlCommandAsync(@"DELETE FROM MyTable WHERE ID != -1");
}
回答by Ahmed Alejo
Warning:The following is only suitable for small tables (think < 1000 rows)
警告：以下仅适用于小表（想想 < 1000 行）
Here is a solution that uses entity framework (not SQL) to delete the rows, so it is not SQL Engine(R/DBM) specific.
这是一个使用实体框架（不是 SQL）删除行的解决方案，因此它不是 SQL Engine(R/DBM) 特定的。
This assumes that you're doing this for testing or some similar situation. Either
这假设您这样做是为了测试或一些类似的情况。任何一个
- The amount of data is small or
- The performance doesn't matter
- 数据量小或
- 性能无所谓
Simply call:
只需调用：
VotingContext.Votes.RemoveRange(VotingContext.Votes);
Assuming this context:
假设这个上下文：
public class VotingContext : DbContext
{
public DbSet<Vote> Votes{get;set;}
public DbSet<Poll> Polls{get;set;}
public DbSet<Voter> Voters{get;set;}
public DbSet<Candidacy> Candidates{get;set;}
}
For tidier code you can declare the following extension method:
对于更整洁的代码，您可以声明以下扩展方法：
public static class EntityExtensions
{
public static void Clear<T>(this DbSet<T> dbSet) where T : class
{
dbSet.RemoveRange(dbSet);
}
}
Then the above becomes:
那么上面的就变成了：
VotingContext.Votes.Clear();
VotingContext.Voters.Clear();
VotingContext.Candidacy.Clear();
VotingContext.Polls.Clear();
await VotingTestContext.SaveChangesAsync();
I recently used this approach to clean up my test database for each testcase run (it′s obviously faster than recreating the DB from scratch each time, though I didn′t check the form of the delete commands that were generated).
我最近使用这种方法为每次测试用例运行清理我的测试数据库（这显然比每次从头重新创建数据库要快，尽管我没有检查生成的删除命令的形式）。
Why can it be slow?
为什么可以慢？
- EF will get ALL the rows (VotingContext.Votes)
- and then will use their IDs (not sure exactly how, doesn't matter), to delete them.
- EF 将获得所有行 (VotingContext.Votes)
- 然后将使用他们的 ID（不确定具体如何，无所谓）来删除它们。
So if you're working with serious amount of data you'll kill the SQL server process (it will consume all the memory) and same thing for the IIS process since EF will cache all the data same way as SQL server. Don't use this one if your table contains serious amount of data.
因此，如果您正在处理大量数据，您将终止 SQL Server 进程（它将消耗所有内存）和 IIS 进程相同的事情，因为 EF 将以与 SQL Server 相同的方式缓存所有数据。如果您的表包含大量数据，请不要使用此选项。
回答by Kristian Nissen
If you wish to clear your entire database.
如果您想清除整个数据库。
Because of the foreign-key constraints it matters which sequence the tables are truncated. This is a way to bruteforce this sequence.
由于外键约束，表被截断的顺序很重要。这是一种强制执行此序列的方法。
public static void ClearDatabase<T>() where T : DbContext, new()
{
using (var context = new T())
{
var tableNames = context.Database.SqlQuery<string>("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT LIKE '%Migration%'").ToList();
foreach (var tableName in tableNames)
{
foreach (var t in tableNames)
{
try
{
if (context.Database.ExecuteSqlCommand(string.Format("TRUNCATE TABLE [{0}]", tableName)) == 1)
break;
}
catch (Exception ex)
{
}
}
}
context.SaveChanges();
}
}
usage:
用法：
ClearDatabase<ApplicationDbContext>();
remember to reinstantiate your DbContext after this.
请记住在此之后重新实例化您的 DbContext。
回答by Hekmat
This works Properly in EF 5:
这在 EF 5 中正常工作：
YourEntityModel myEntities = new YourEntityModel();
var objCtx = ((IObjectContextAdapter)myEntities).ObjectContext;
objCtx.ExecuteStoreCommand("TRUNCATE TABLE [TableName]");
回答by Rob Sedgwick
This avoids using any sql
这避免了使用任何 sql
using (var context = new MyDbContext())
{
var itemsToDelete = context.Set<MyTable>();
context.MyTables.RemoveRange(itemsToDelete);
context.SaveChanges();
}
回答by DBB
var data = (from n in db.users select n);
db.users.RemoveRange(data);
db.SaveChanges();
