C# LINQ To SQL：使用一个查询删除实体（按 ID）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/339041/
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
LINQ To SQL: Delete entity (by ID) with one query
提问by
I've been working with LINQ To SQL for a little while now and when it comes to removing an entity from the DB, I've always called the table's .DeleteOnSubmit and passed in the entity. Sometimes I've found myself writing something like:
我已经使用 LINQ To SQL 一段时间了，在从数据库中删除实体时，我总是调用表的 .DeleteOnSubmit 并传入实体。有时我发现自己在写这样的东西：
db.Users.DeleteOnSubmit(db.Users.Where(c => c.ID == xyz).Select(c => c).Single());
This of course causes two queries. One to get the entity matching the criteria and then another to delete it. Often I have the ID of the record I need removing and I am wondering if there is a more direct way to remove a row from a table via the ID only?
这当然会导致两个查询。一个获取与条件匹配的实体，然后另一个删除它。我经常有需要删除的记录的 ID，我想知道是否有更直接的方法可以仅通过 ID 从表中删除行？
回答by Adam Lassek
I don't believe Linq to Sql can do this natively, although writing a stored procedure would give you what you want, with syntax like:
我不相信 Linq to Sql 可以在本机上做到这一点，尽管编写存储过程会给你你想要的，语法如下：
db.spDeleteUserById(id);
This would requre writing a stored procedure for each table, but it's a pretty trivial amount of SQL.
这将需要为每个表编写一个存储过程，但这是一个非常简单的 SQL。
Check out this blog post, in which the author has created an extension method that generates its own delete statement. You might be able to take this concept and run with it to create a delete routine that doesn't require a select. The downside is, you might end up with an extension method that is specific to your own schema.
查看这篇博文，其中作者创建了一个生成自己的删除语句的扩展方法。您也许能够接受这个概念并使用它来创建一个不需要选择的删除例程。缺点是，您最终可能会得到一个特定于您自己的架构的扩展方法。
I understand that you want to keep your logic out of the database layer, but in this case I think it would be far simpler to go the stored proc route. It wouldn't be subject to the problems with tightly coupled data access logic, since deleting a row by id isn't likely to ever need refactoring.
我知道您希望将逻辑保留在数据库层之外，但在这种情况下，我认为使用存储过程路由会简单得多。它不会受到紧耦合数据访问逻辑的问题的影响，因为按 id 删除一行不太可能需要重构。
回答by Christopher Edwards
Here is a solution...
这是一个解决方案...
public static void DeleteByPK<TSource, TPK>(TPK pk, DataContext dc)
where TSource : class
{
Table<TSource> table = dc.GetTable<TSource>();
TableDef tableDef = GetTableDef<TSource>();
dc.ExecuteCommand("DELETE FROM [" + tableDef.TableName
+ "] WHERE [" = tableDef.PKFieldName + "] = {0}", pk);
}
It's not my code! See for explaination - http://msmvps.com/blogs/omar/archive/2008/10/30/linq-to-sql-delete-an-entity-using-primary-key-only.aspx
这不是我的代码！请参阅解释 - http://msmvps.com/blogs/omar/archive/2008/10/30/linq-to-sql-delete-an-entity-using-primary-key-only.aspx
Hope this helps.
希望这可以帮助。
回答by Dragos
I am using the following extension methods, the usage being:
我正在使用以下扩展方法，用法是：
context.Customers.DeleteEntity(c => c.CustomerId, 12);
public static class EntityExtensions
{
public static EntityKey CreateEntityKey<T, TId>(this ObjectSet<T> entitySet, Expression<Func<T, TId>> entityKey, TId id)
where T : class
{
var qEntitySet = entitySet.Context.DefaultContainerName + "." + entitySet.EntitySet.Name;
var keyName = LinqHelper.PropertyName(entityKey);
return new EntityKey(qEntitySet, keyName, id);
}
public static void DeleteEntity<T, TId>(this ObjectSet<T> entitySet, Expression<Func<T, TId>> entityKey, TId id)
where T : EntityObject, new()
{
var key = CreateEntityKey(entitySet, entityKey, id);
var entityInstance = new T {EntityKey = key};
var propertyName = LinqHelper.PropertyName(entityKey);
var property = typeof (T).GetProperty(propertyName);
if (property == null)
throw new Exception("Property name " + propertyName + " does not exist on " + typeof(T).Name);
property.SetValue(entityInstance, id, null);
entitySet.Attach(entityInstance);
entitySet.DeleteObject(entityInstance);
}
回答by joelmdev
I know this question is old but here's a newer and (IMO) better solution.
I was trying to accomplish the same thing when I ran across PLINQO. Very nice set of extensions to LINQ. This pagedescribes how to perform deletes with PLINQO's extension methods that require only 1 roundtrip to the db server.
The downside is that it requires Codesmithto run, which is a little pricey.
我知道这个问题很老，但这里有一个更新和（IMO）更好的解决方案。
当我遇到PLINQO时，我试图完成同样的事情。非常好的 LINQ 扩展集。本页描述了如何使用 PLINQO 的扩展方法执行删除，这些方法只需要到数据库服务器的 1 次往返。
缺点是它需要Codesmith运行，这有点贵。
回答by Noam Ben-Ami
Do this without hand tooling SQL by attaching a partial object and deleting it:
通过附加部分对象并删除它，无需手动工具 SQL 即可执行此操作：
var myEntity = new MyEntityType { MyEntityId = xxx };
Context.MyEntityTypeTable.Attach(myEntity, false);
Context.MyEntityTypeTable.DeleteOnSubmit(myEntity);
Context.SubmitChanges();
Easy. You can even write it as an extension method and use a dynamic object type to represent the key, and use reflection to figure out key properties, which I will leave as a fun exercise for the reader. E.g. implement Context.Delete(new { MyEntityId = xxx });
简单。您甚至可以将其编写为扩展方法并使用动态对象类型来表示键，并使用反射来计算键属性，我将其作为一个有趣的练习留给读者。例如实现 Context.Delete(new { MyEntityId = xxx });
