EF 5 条件映射 [英] EF 5 Conditional Mapping

问题描述

我在我的 MVC 应用程序中使用 EF 5 数据库优先方法.我的所有表都使用一个名为 Deleted 的字段，它是一个布尔字段，用于标记记录已删除.

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.

我试图摆脱每次查询数据库时都必须检查 Deleted == false 的要求.执行此操作的非常直接的方法是在 edmx 文件中使用条件映射，其中 EF 始终返回未删除的数据.一切都很好.

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.

但是做这个条件映射的问题是，当我想允许用户从他们的地址簿中删除一些记录，例如地址时，我无法访问来自 EF 的删除字段，因为我在条件中使用它映射，因此我必须寻找另一个选项来允许用户删除记录.

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().Map(m => m.Requires("IsDeleted").HasValue(false));

这使得它对上下文不可见，因此您必须使用 sql 进行删除.

This makes it invisible to the context and therefore you have to do the deletes using sql.

如果这相当于 Database First 中的条件映射"，那么修改 sql 的一种方法是覆盖 SaveChanges 并从那里运行 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;
}

用于此处解释的获取表名的方法

这就是我以前做的方式.可能与您在 EF5 中的数据库优先方法无关，但我现在已转向在存储过程中执行此操作.EF6 Code First 在迁移文件中生成 CreateStoredProcedure 调用.我将这些替换为 this.CreateDeleteProcedure("dbo.Foo_Delete", "[dbo].[Foos]"); - 这是对我自己的扩展方法的调用:

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)

                    );
    }

}

这篇关于EF 5 条件映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！