实体框架如何自动过滤出软删除的实体？ [英] How can I automatically filter out soft deleted entities with Entity Framework?
问题描述
DbContext 中覆盖 SaveChanges ，以允许我做一个软删除： if（item.State == EntityState.Deleted&& typeof（ISoftDelete）.IsAssignableFrom（type））
{
item .State = EntityState.Modified;
item.Entity.GetType（）。GetMethod（Delete）
.Invoke（item.Entity，null）;
继续;
}
哪个是好的，所以对象知道如何将自己标记为软删除（在这种情况下，只需将 IsDeleted 设置为 true ）。
我的问题是如何让我这样做，以便当我检索对象时，它将忽略 IsDeleted ？所以如果我说 _db.Users.FirstOrDefault（UserId == id），如果该用户有 IsDeleted == true 会忽略它。本质上我想过滤？
注意：我不想只是把&& IsDeleted == true
这就是为什么我用一个接口标记类，所以删除知道如何正常工作，我想以某种方式修改检索以知道如何
我已经对所有的实体进行了软删除，软删除使用此答案建议的技术，不会通过上下文检索项目。这包括当您通过导航属性访问实体时。
向可以软删除的每个实体添加一个IsDele标识符。不幸的是，我还没有根据从抽象类或接口派生的实体来研究如何做这一点（ EF映射目前不支持接口作为实体）：
protected override void OnModelCreating（DbModelBuilder modelBuilder）
{
modelBuilder.Entity< Foo>（） 。m.Requires（ 请将isDeleted）的HasValue（假））;
modelBuilder.Entity< Bar>（）。Map（m => m.Requires（IsDeleted）。HasValue（false））;
//如果您有派生实体，则会更复杂。
//这里'Block'来自'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）;
}）;
}
覆盖SaveChanges并查找要删除的所有条目：
修改
另一种方法来覆盖删除sql 是更改由EF6生成的存储过程
public override int SaveChanges（）
{
foreach（ChangeTracker.Entries（）中的var entry
.Where（p => p.State == EntityState.Deleted
&& p.Entity是ModelBase））//我确实有一个具有单个
//ID属性的实体的基类 - 所有我的实体从此派生，
//但是您可以在这里使用ISoftDelete
SoftDelete（entry）;
return base.SaveChanges（）;
}
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）
，新的SqlParameter（id，e.ID） ）;
//标记它不变防止硬删除
//entry.State = EntityState.Unchanged;
//所以设置为分离：
//这就是EF在删除项目时所做的工作
//http://msdn.microsoft.com/en-us/ data / jj592676.aspx
entry.State = EntityState.Detached;
}
GetTableName返回要为实体更新的表。它处理表链接到BaseType而不是派生类型的情况。我怀疑我应该检查整个继承层次结构....
但是有计划改进元数据API ，如果我必须查看 EF Code First Mapping Between Types&表
private readonly static Dictionary< Type，EntitySetBase> _mappingCache
=新词典< 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
（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;
}
内部字符串GetTableName（类型类型）
{
EntitySetBase es = GetEntitySet（type）;
//如果使用EF6
返回String.Format（[{0}]。[{1}]，es.Schema，es.Table）;
//如果您有EF6之前的版本
//返回string.Format（[{0}]。[{1}]，
// es .MetadataProperties [Schema]。Value，
// es.MetadataProperties [Table]。Value）;
}
我以前在迁移过程中创建了自然键上的索引，代码看起来像这个：
public override void Up（）
{
CreateIndex（dbo.Organisations名称，unique：true，name：IX_NaturalKey）;
}
但这意味着您不能创建一个名称相同的新组织作为被删除的组织。为了允许这个，我更改了代码来创建索引：
public override void Up（）
{
Sql（String.Format（CREATE UNIQUE INDEX {0} ON dbo.Organisations（Name）WHERE IsDeleted = 0，IX_NaturalKey））;
}
而且从索引中排除已删除的项目
注意
如果相关项目被软删除，则不填充导航属性，则外键为。
例如：
if（foo.BarID！= null）//试图避免数据库调用
string name = foo.Bar.Name; //将失败，因为BarID不为空，但Bar为
//但是这样工作
if（foo.Bar！= null）//数据库调用，因为有一个外键
string name = foo.Bar.Name;
PS 为全局过滤投票 https://entityframework.codeplex.com/workitem/945?FocusElement=CommentTextBox# ，过滤后包括 here
I am using Entity Framework Code First. I override SaveChanges in DbContext to allow me to do a "soft delete":
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 IsDeleted to 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 == true it would ignore it. Essentially I want to filter?
Note: I do not want to just put && IsDeleted == true
That'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.
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):
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:
Edit Another way to override the delete sql is to change the stored procedures generated by 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:
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 API and if I have to will look into 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
Note While 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
这篇关于实体框架如何自动过滤出软删除的实体？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
