C# 如何使用实体框架自动过滤掉软删除的实体？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12698793/
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
How can I automatically filter out soft deleted entities with Entity Framework?
提问by Jordan
I am using Entity Framework Code First. I override SaveChangesin DbContextto allow me to do a "soft delete":
我首先使用实体框架代码。我重写SaveChanges的DbContext，让我做一个“软删除”：
if (item.State == EntityState.Deleted && typeof(ISoftDelete).IsAssignableFrom(type))
{
item.State = EntityState.Modified;
item.Entity.GetType().GetMethod("Delete")
.Invoke(item.Entity, null);
continue;
}
Which is great, so the object knows how to mark itself as a soft delete (In this case it just sets IsDeletedto true).
这很好，所以对象知道如何将自己标记为软删除（在这种情况下它只是设置IsDeleted为true）。
My question is how can I make it such that when I retrieve the object it ignores any with IsDeleted? So if I said _db.Users.FirstOrDefault(UserId == id)if that user had IsDeleted == trueit would ignore it. Essentially I want to filter?
我的问题是我怎样才能使它在我检索对象时忽略任何与IsDeleted？因此，如果我说_db.Users.FirstOrDefault(UserId == id)该用户是否拥有IsDeleted == true它，则会忽略它。基本上我想过滤？
Note: I do not want to just put && IsDeleted == trueThat's why I am marking the classes with an interface so the remove knows how to "Just Work" and I'd like to somehow modify the retrieval to know how to "Just Work" also based on that interface being present.
注意：我不想只放置&& IsDeleted == true这就是为什么我用接口标记类的原因，以便删除知道如何“Just Work”，我想以某种方式修改检索以了解如何“Just Work”也基于该界面存在。
回答by Ricky G
One option would be to encapsulate the !IsDeletedinto an extension method. Something like below is just an example. Beware its just to give you an idea of an extension method, the below won't compile.
一种选择是将 封装!IsDeleted到扩展方法中。像下面这样的东西只是一个例子。请注意，它只是为了让您了解扩展方法，下面的将无法编译。
public static class EnumerableExtensions
{
public static T FirstOrDefaultExcludingDeletes<T>(this IEnumerable<T> source, Func<T, bool> predicate)
{
return source.Where(args => args != IsDeleted).FirstOrDefault(predicate);
}
}
Usage:
用法：
_db.Users.FirstOrDefaultExcludingDeletes(UserId == id)
回答by majkinetor
Great question.
很好的问题。
You would need to intercept SQL query before it gets executed somehow, then add additional where clause to remove 'deleted' items from selection. Unfortunately, Entity doesn't have GetCommand that can be used to change the query.
您需要在 SQL 查询以某种方式执行之前拦截它，然后添加额外的 where 子句以从选择中删除“已删除”的项目。不幸的是，Entity 没有可用于更改查询的 GetCommand。
Perhaps EF Provider Wrapper which sits in the right place could be modified to allow for query change.
也许可以修改位于正确位置的 EF Provider Wrapper 以允许查询更改。
Or, u can utilize QueryInterceptor but each query would have to use InterceptWith(visitor)to change the expressions...
或者，您可以使用 QueryInterceptor 但每个查询都必须 InterceptWith(visitor)用于更改表达式...
So, I would concentrate on this approach as there is AFAIK no other option then intercepting the query and fixing it (if you want to keep code that queries unchanged).
所以，我会专注于这种方法，因为 AFAIK 没有其他选择，然后拦截查询并修复它（如果你想保持查询代码不变）。
Anyway, if you figure out something useful, let us know.
无论如何，如果您发现有用的东西，请告诉我们。
回答by Colin
I've got soft delete working for all my entities and soft deleted items are not retrieved via the context using a technique suggested by this answer. That includes when you access the entity via navigation properties.
我已经为我的所有实体进行了软删除，并且不会使用此答案建议的技术通过上下文检索软删除项目。这包括您何时通过导航属性访问实体。
Add an IsDeleted discriminator to every entity that can be soft deleted. Unfortunately I haven't worked out how to do this bit based on the entity deriving from an abstract class or an interface (EF mapping doesn't currently support interfaces as an entity):
为每个可以软删除的实体添加一个 IsDeleted 鉴别器。不幸的是，我还没有想出如何根据从抽象类或接口派生的实体（EF 映射当前不支持将接口作为实体）来实现这一点：
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Foo>().Map(m => m.Requires("IsDeleted").HasValue(false));
modelBuilder.Entity<Bar>().Map(m => m.Requires("IsDeleted").HasValue(false));
//It's more complicated if you have derived entities.
//Here 'Block' derives from 'Property'
modelBuilder.Entity<Property>()
.Map<Property>(m =>
{
m.Requires("Discriminator").HasValue("Property");
m.Requires("IsDeleted").HasValue(false);
})
.Map<Block>(m =>
{
m.Requires("Discriminator").HasValue("Block");
m.Requires("IsDeleted").HasValue(false);
});
}
Override SaveChanges and find all the entries to be deleted:
覆盖 SaveChanges 并找到所有要删除的条目：
EditAnother way to override the delete sqlis to change the stored procedures generated by EF6
编辑另一种覆盖删除sql的方法是更改EF6生成的存储过程
public override int SaveChanges()
{
foreach (var entry in ChangeTracker.Entries()
.Where(p => p.State == EntityState.Deleted
&& p.Entity is ModelBase))//I do have a base class for entities with a single
//"ID" property - all my entities derive from this,
//but you could use ISoftDelete here
SoftDelete(entry);
return base.SaveChanges();
}
The SoftDelete method runs sql directly on the database because discriminator columns cannot be included in entities:
SoftDelete 方法直接在数据库上运行 sql，因为实体中不能包含鉴别器列：
private void SoftDelete(DbEntityEntry entry)
{
var e = entry.Entity as ModelBase;
string tableName = GetTableName(e.GetType());
Database.ExecuteSqlCommand(
String.Format("UPDATE {0} SET IsDeleted = 1 WHERE ID = @id", tableName)
, new SqlParameter("id", e.ID));
//Marking it Unchanged prevents the hard delete
//entry.State = EntityState.Unchanged;
//So does setting it to Detached:
//And that is what EF does when it deletes an item
//http://msdn.microsoft.com/en-us/data/jj592676.aspx
entry.State = EntityState.Detached;
}
GetTableName returns the table to be updated for an entity. It handles the case where the table is linked to the BaseType rather than a derived type. I suspect I should be checking the whole inheritance hierarchy.... But there are plans to improve the Metadata APIand if I have to will look into EF Code First Mapping Between Types & Tables
GetTableName 返回要为实体更新的表。它处理表链接到 BaseType 而不是派生类型的情况。我怀疑我应该检查整个继承层次结构......但是有改进元数据 API 的计划，如果必须的话，我会研究EF Code First Mapping between Types & Tables
private readonly static Dictionary<Type, EntitySetBase> _mappingCache
= new Dictionary<Type, EntitySetBase>();
private ObjectContext _ObjectContext
{
get { return (this as IObjectContextAdapter).ObjectContext; }
}
private EntitySetBase GetEntitySet(Type type)
{
type = GetObjectType(type);
if (_mappingCache.ContainsKey(type))
return _mappingCache[type];
string baseTypeName = type.BaseType.Name;
string typeName = type.Name;
ObjectContext octx = _ObjectContext;
var es = octx.MetadataWorkspace
.GetItemCollection(DataSpace.SSpace)
.GetItems<EntityContainer>()
.SelectMany(c => c.BaseEntitySets
.Where(e => e.Name == typeName
|| e.Name == baseTypeName))
.FirstOrDefault();
if (es == null)
throw new ArgumentException("Entity type not found in GetEntitySet", typeName);
_mappingCache.Add(type, es);
return es;
}
internal String GetTableName(Type type)
{
EntitySetBase es = GetEntitySet(type);
//if you are using EF6
return String.Format("[{0}].[{1}]", es.Schema, es.Table);
//if you have a version prior to EF6
//return string.Format( "[{0}].[{1}]",
// es.MetadataProperties["Schema"].Value,
// es.MetadataProperties["Table"].Value );
}
I had previously created indexes on natural keys in a migration with code that looked like this:
我之前在迁移中使用如下代码在自然键上创建了索引：
public override void Up()
{
CreateIndex("dbo.Organisations", "Name", unique: true, name: "IX_NaturalKey");
}
But that means that you can't create a new Organisation with the same name as a deleted Organisation. In order to allow this I changed the code to create the indexes to this:
但这意味着您不能创建与已删除组织同名的新组织。为了实现这一点，我更改了代码以创建索引：
public override void Up()
{
Sql(String.Format("CREATE UNIQUE INDEX {0} ON dbo.Organisations(Name) WHERE IsDeleted = 0", "IX_NaturalKey"));
}
And that excludes deleted items from the index
这从索引中排除已删除的项目
NoteWhile navigation properties are not populated if the related item is soft deleted, the foreign key is. For example:
注意如果相关项被软删除，则不会填充导航属性，但外键是。例如：
if(foo.BarID != null) //trying to avoid a database call
string name = foo.Bar.Name; //will fail because BarID is not null but Bar is
//but this works
if(foo.Bar != null) //a database call because there is a foreign key
string name = foo.Bar.Name;
P.S.Vote for global filtering here https://entityframework.codeplex.com/workitem/945?FocusElement=CommentTextBox#and filtered includes here
PS在此处为全局过滤投票https://entityframework.codeplex.com/workitem/945?FocusElement=CommentTextBox#并过滤包括此处
回答by John
Use EntityFramework.DynamicFilters. It allows you to create global filters that will be applied automatically (including against navigation properties) when queries are executed.
使用EntityFramework.DynamicFilters。它允许您创建将在执行查询时自动应用（包括针对导航属性）的全局过滤器。
There is an example "IsDeleted" filter on the project page that looks like this:
项目页面上有一个示例“IsDeleted”过滤器，如下所示：
modelBuilder.Filter("IsDeleted", (ISoftDelete d) => d.IsDeleted, false);
That filter will automatically inject a where clause on any query against an entity that is ISoftDelete. Filters are defined in your DbContext.OnModelCreating().
该过滤器将自动在针对 ISoftDelete 实体的任何查询中注入 where 子句。过滤器在您的 DbContext.OnModelCreating() 中定义。
Disclaimer: I'm the author.
免责声明：我是作者。
回答by hkutluay
You can use Global Query Filterson Entity Framework Core 2.0.
您可以在 Entity Framework Core 2.0 上使用全局查询过滤器。
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Blog>().Property<string>("TenantId").HasField("_tenantId");
// Configure entity filters
modelBuilder.Entity<Blog>().HasQueryFilter(b => EF.Property<string>(b, "TenantId") == _tenantId);
modelBuilder.Entity<Post>().HasQueryFilter(p => !p.IsDeleted);
}
