条件映射 [英] EF 5 Conditional Mapping
问题描述
我试图摆脱每次查询我的数据库时必须检查Deleted == false的要求。执行此操作的非常简单的方法是在edmx文件中使用条件映射，其中EF始终返回未被删除的数据。这很好。
但是，执行此条件映射的问题是，当我想允许用户从他们的通讯录中删除一些例如Address的记录时，我会在条件映射中使用它时，不能访问EF中的删除字段，因此我必须查找另一个允许用户删除记录的选项。
我想的方式是创建一个处理删除查询的存储过程，并在我要删除记录时调用它。
有更好的方法吗？
我有一个可行的解决方案， a href =https://stackoverflow.com/a/18985828/150342>软删除在实体框架代码第一可能有帮助。
关键是您可以为每个要软删除的模型添加一个鉴别器。在代码中，这样做完成：
modelBuilder.Entity< Foo>（）。Map（m => m.Requires IsDeleted）。HasValue（false））;
这使它不可见的上下文，因此你必须使用sql进行删除。
如果这相当于Database First中的条件映射，那么修改sql的一种方法是重写SaveChanges并从中运行sql：
public override int SaveChanges（）
{
foreach（ChangeTracker.Entries（）中的var entry
。其中（p => p.State == EntityState.Deleted
&& p.Entity是ModelBase））//我有一个基类，具有单个
//ID 属性 - 我所有的实体都是从这里导出的，
//但是你可以在这里使用ISoftDelete
SoftDelete（entry）;
return base.SaveChanges（）;
}
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;
}
这是我曾经这样做的方式。可能与您在EF5中的数据库第一种方法无关，但我现在已经转移到存储过程中。 EF6代码首先在迁移文件中生成 CreateStoredProcedure 调用。我替换为 this.CreateDeleteProcedure（dbo.Foo_Delete，[dbo]。[Foos]）; - 这是一个调用我自己的扩展方法： / p>
public static class MigrationExtensions
{
内部静态字符串DeleteSqlFormat
{
/ /我也很难删除在同一个表中删除一天以上的任何内容
get {returnDELETE FROM {0} WHERE IsDeleted = 1 AND DATEADD（DAY，1，DeletedAt）&GET; GETUTCDATE（）; UPDATE { 0} SET IsDeleted = 1，DeletedAt = GETUTCDATE（）WHERE ID = @ID;;
内部静态void CreateDeleteProcedure（此DbMigration迁移，字符串procName，字符串tableName）
{
migration.CreateStoredProcedure（
procName，
p => new
{
ID = p.Int（），
}，
body：
string.Format（MigrationExtensions.DeleteSqlFormat ，tableName）
）;
}
}
I'm using EF 5 Database first approach in my MVC application. all of my tables uses a Field called Deleted which is a boolean field to mark a record is deleted.
I'm trying to get rid of the requirement of having to check Deleted == false every time I query my database. The very straightforward way of doing this is to use a conditional mapping in the edmx file where EF always return data that are not deleted. That's all good.
But the problem of doing this condition mapping is that, when I want to allow the user to delete some record for e.g Address from their address book I don't have access to Delete field from EF as I used it in the conditional mapping and therefore I have to look for another option to allow user to delete a record.
The way I thought is to create a stored proc that handle the delete query and call it when I want to delete the record.
Is there a better way of doing this? Is it possible to make the Delete field accessible even it is used in the conditional mapping?
I have a working solution for Soft Delete in Entity Framework Code First that may help.
The key is that you add a discriminator to every model that you want to be able to soft delete. In code first that is done like this:
modelBuilder.Entity<Foo>().Map(m => m.Requires("IsDeleted").HasValue(false));
This makes it invisible to the context and therefore you have to do the deletes using sql.
If this is the equivalent of your "conditional mapping" in Database First then one way to modify the sql is to override SaveChanges and run sql from there:
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();
}
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;
}
Method used to Get Table Name explained here
That is the way I used to do it. Probably irrelevant to your Database First approach in EF5, but I have now moved to doing it in stored procedures. EF6 Code First generates CreateStoredProcedure calls in Migration files. I replace these with this.CreateDeleteProcedure("dbo.Foo_Delete", "[dbo].[Foos]"); - which is a call to my own extension method:
public static class MigrationExtensions
{
internal static string DeleteSqlFormat
{
//I also hard delete anything deleted more than a day ago in the same table
get { return "DELETE FROM {0} WHERE IsDeleted = 1 AND DATEADD(DAY, 1, DeletedAt) < GETUTCDATE(); UPDATE {0} SET IsDeleted = 1, DeletedAt = GETUTCDATE() WHERE ID = @ID;"; }
}
internal static void CreateDeleteProcedure(this DbMigration migration, string procName, string tableName)
{
migration.CreateStoredProcedure(
procName,
p => new
{
ID = p.Int(),
},
body:
string.Format(MigrationExtensions.DeleteSqlFormat, tableName)
);
}
}
这篇关于条件映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
