由于EF不支持DELETE SET NULL，我可以在EF外运行一个SQL命令吗？ [英] Since EF doesn't support DELETE SET NULL, Can I run an SQL command outside EF to do it?

问题描述

我知道如果您希望FK可以为空，我们可以在您的实体中设置一个可空的键：

I know that you set a nullable key in your entity if you want that FK to be nullable:

class ChildEntity
{
   // Other properties not shown for brevity
   public int? ParentId { get; set; }
   public virtual ParentEntity Parent; { get; set; }
}

这将导致一个可空的FK。有人建议这里，我们也应该在Fluent中设置可选关系：

This will result in a nullable FK. It was suggested here that we should also set the optional relationship in Fluent:

modelBuilder.Entity<ChildEntity>()
            .HasOptional(c => c.Parent)
            .WithMany()
            .HasForeignKey(c => c.ParentId);

但这仍然没有设置delete set null。 FK ParentId仍然删除设置为No Action。

but this still doesn't set delete set null. The FK ParentId still has delete set to No Action.

在文章的后面，有人建议我们应该在Configuration类的Seed方法中运行SQL命令？我不知道这是否是一个问题，但是我经常运行更新数据库，我会来回更改这个设置。

Later in the article, it was suggested that we should run the SQL command in the Seed method of the Configuration class? I'm not sure if it's a problem, but I run Update-Database quite often, and I'd be changing this setting back and forth.

那么，是吗那么安全，那么后退EF，并在SQL Management Studio（或其他应用程序）中将删除规则更改为SET NULL？由于我们以简单的SQL语言在种子方法中使用SqlCommand，我想说是的，我们可以继续手动更改删除规则，但我不确定。在这一点上我无法实验，所以我很乐意为此做出回答。

So, is it safe, then, to "go behind EF's back" and change the delete rule to SET NULL in SQL Management Studio (or other app)? Since we're using SqlCommand in the seed method in plain SQL language, I want to say yes, we can go ahead and manually change the delete rule, but I'm not sure. I can't afford to experiment at this point, so I would appreciate an answer for this.

推荐答案

该示例将sql放在 Seed 方法中，它每次调用 Update-Database 时运行。您可以通过在迁移中使用 Sql 方法对数据库进行修改来避免这种情况。这样它只运行一次。

That example puts the sql in the Seed method and that means that it runs every time you call Update-Database. You avoid that by making the modification to the database using the Sql method in a migration. That way it only runs once.

public void Up()
{
   Sql(@"ALTER TABLE Products DROP CONSTRAINT Product_Category");

   Sql(@"ALTER TABLE Products ADD CONSTRAINT Product_Category 
        FOREIGN KEY (CategoryId) REFERENCES Categories (CategoryId) 
        ON UPDATE CASCADE ON DELETE SET NULL");" 
}

这篇关于由于EF不支持DELETE SET NULL，我可以在EF外运行一个SQL命令吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！