C# Linq to Sql：如何快速清除表

Linq to Sql: How to quickly clear a table

提问by Svish

To delete all the rows in a table, I am currently doing the following:

要删除表中的所有行，我目前正在执行以下操作：

context.Entities.DeleteAllOnSubmit(context.Entities);
context.SubmitChanges();

However, this seems to be taking ages. Is there a faster way?

然而，这似乎需要很长时间。有没有更快的方法？

采纳答案by CMS

You could do a normal SQL truncate or delete command, using the DataContext.ExecuteCommandmethod:

您可以使用DataContext.ExecuteCommand方法执行普通的 SQL 截断或删除命令：

context.ExecuteCommand("DELETE FROM Entity");

Or

或者

context.ExecuteCommand("TRUNCATE TABLE Entity");

The way you are deleting is taking long because Linq to SQL generates a DELETE statement for each entity, there are other type-safeapproaches to do batch deletes/updates, check the following articles:

您删除的方式需要很长时间，因为 Linq to SQL 为每个实体生成一个 DELETE 语句，还有其他类型安全的方法可以进行批量删除/更新，请查看以下文章：

• Batch Updates and Deletes with LINQ to SQL
• LINQ to SQL Extension: Batch Deletion with Lambda Expression

• 使用 LINQ to SQL 批量更新和删除
• LINQ to SQL 扩展：使用 Lambda 表达式批量删除

回答by Kirk Broadhurst

Unfortunately LINQ-to-SQL doesn't execute set based queries very well.

不幸的是，LINQ-to-SQL 不能很好地执行基于集合的查询。

You would assume that

你会假设

context.Entities.DeleteAllOnSubmit(context.Entities); 
context.SubmitChanges(); 

will translate to something like

将转化为类似的东西

DELETE FROM [Entities]

but unfortunately it's more like

但不幸的是它更像是

DELETE FROM [dbo].[Entities] WHERE ([EntitiesId] = @p0) AND ([Column1] = @p1) ...
DELETE FROM [dbo].[Entities] WHERE ([EntitiesId] = @p0) AND ([Column1] = @p1) ...
DELETE FROM [dbo].[Entities] WHERE ([EntitiesId] = @p0) AND ([Column1] = @p1) ...

You'll find the same when you try to do bulk update in LINQ-to-SQL. Any more than a few hundred rows at a time and it's simply going to be too slow.

当您尝试在 LINQ-to-SQL 中进行批量更新时，您会发现相同的情况。一次超过几百行，它只是太慢了。

If you need to do batch operations & you're using LINQ-to-SQL, you need to write stored procedures.

如果您需要进行批处理操作并使用 LINQ-to-SQL，则需要编写存储过程。

回答by Bill Roberts

I like using an Extension Method, per the following:

我喜欢使用扩展方法，如下所示：

public static class LinqExtension
{
  public static void Truncate<TEntity>(this Table<TEntity> table) where TEntity : class
  {
    var rowType = table.GetType().GetGenericArguments()[0];
    var tableName = table.Context.Mapping.GetTable(rowType).TableName;
    var sqlCommand = String.Format("TRUNCATE TABLE {0}", tableName);
    table.Context.ExecuteCommand(sqlCommand);
  }
}

回答by Bill Roberts

The Below c# code is used to Insert/Update/Delete/DeleteAll on a database table using LINQ to SQL

下面的 c# 代码用于使用 LINQ to SQL 在数据库表上插入/更新/删除/DeleteAll

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace PracticeApp
{
    class PracticeApp
    {        
        public void InsertRecord(string Name, string Dept) {
            LinqToSQLDataContext LTDT = new LinqToSQLDataContext();
            LINQTOSQL0 L0 = new LINQTOSQL0 { NAME = Name, DEPARTMENT = Dept };
            LTDT.LINQTOSQL0s.InsertOnSubmit(L0);
            LTDT.SubmitChanges();
        }

        public void UpdateRecord(int ID, string Name, string Dept)
        {
            LinqToSQLDataContext LTDT = new LinqToSQLDataContext();
            LINQTOSQL0 L0 = (from item in LTDT.LINQTOSQL0s where item.ID == ID select item).FirstOrDefault();
            L0.NAME = Name;
            L0.DEPARTMENT = Dept;
            LTDT.SubmitChanges();
        }

        public void DeleteRecord(int ID)
        {
            LinqToSQLDataContext LTDT = new LinqToSQLDataContext();
            LINQTOSQL0 L0;
            if (ID != 0)
            {
                L0 = (from item in LTDT.LINQTOSQL0s where item.ID == ID select item).FirstOrDefault();
                LTDT.LINQTOSQL0s.DeleteOnSubmit(L0);
            }
            else
            {
                IEnumerable<LINQTOSQL0> Data = from item in LTDT.LINQTOSQL0s where item.ID !=0 select item;
                LTDT.LINQTOSQL0s.DeleteAllOnSubmit(Data);
            }           
            LTDT.SubmitChanges();
        }

        static void Main(string[] args) {
            Console.Write("* Enter Comma Separated Values to Insert Records\n* To Delete a Record Enter 'Delete' or To Update the Record Enter 'Update' Then Enter the Values\n* Dont Pass ID While Inserting Record.\n* To Delete All Records Pass 0 as Parameter for Delete.\n");
            var message = "Successfully Completed";
            try
            {
                PracticeApp pa = new PracticeApp();
                var enteredValue = Console.ReadLine();                
                if (Regex.Split(enteredValue, ",")[0] == "Delete") 
                {
                    Console.Write("Delete Operation in Progress...\n");
                    pa.DeleteRecord(Int32.Parse(Regex.Split(enteredValue, ",")[1]));
                }
                else if (Regex.Split(enteredValue, ",")[0] == "Update")
                {
                    Console.Write("Update Operation in Progress...\n");
                    pa.UpdateRecord(Int32.Parse(Regex.Split(enteredValue, ",")[1]), Regex.Split(enteredValue, ",")[2], Regex.Split(enteredValue, ",")[3]);
                }
                else
                {
                    Console.Write("Insert Operation in Progress...\n");
                    pa.InsertRecord(Regex.Split(enteredValue, ",")[0], Regex.Split(enteredValue, ",")[1]);
                }                                
            }
            catch (Exception ex)
            {
                message = ex.ToString();
            }
            Console.Write(message);            
            Console.ReadLine();                        
        }
    }
}