DeleteBehavior.ClientSetNull sets FKs to null on SaveChanges in some cases

[Wain123]

I can't reopen #8633 so I'm posting a new issue.

The relationship is configured like this:

modelBuilder.Entity<Post>().HasOne(t => t.Blog).WithMany("PostBlogCollection").HasForeignKey("Blog_Seqnr").OnDelete(DeleteBehavior.ClientSetNull);

The following code:

var post = ctx.Posts.Find(1);
var blog = ctx.Blogs.Find(2);
// post.Blog == blog
post.Text = "asdf"; // Just to make post's State Modified
ctx.Blogs.Remove(blog);
ctx.SaveChanges();

Sets post.Blog_Seqnr to null and saves this to the DB successfully. I expected it to crash on SaveChanges with "ORA-02292: integrity constraint [...] violated - child record found".
On the other hand, the following code does exactly what I expected. The difference is only that post is now Unchanged instead of Modified:

var post = ctx.Posts.Find(1);
var blog = ctx.Blogs.Find(2);
// post.Blog == blog
ctx.Blogs.Remove(blog);
ctx.SaveChanges();

[ajcvickers]

@Wain123 I am not able to reproduce this. In both cases, the blog is deleted from the database and the FK on the post is set to null. Here's my code:

public class Blog
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    public ICollection<Post> PostBlogCollection { get; set; }
}

public class Post
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    public string Text { get; set; }

    public int? Blog_Seqnr { get; set; }
    public Blog Blog { get; set; }

}

public class Context : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlServer(
            @"Server=(localdb)\mssqllocaldb;Database=Test;Trusted_Connection=True;ConnectRetryCount=0");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>()
            .HasOne(t => t.Blog)
            .WithMany("PostBlogCollection")
            .HasForeignKey("Blog_Seqnr")
            .OnDelete(DeleteBehavior.ClientSetNull);

    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new Context())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(new Post { Id = 1, Blog = new Blog { Id = 2 } });
            context.SaveChanges();
        }

        using (var context = new Context())
        {
            var post = context.Posts.Find(1);
            var blog = context.Blogs.Find(2);

              post.Text = "asdf"; // Just to make post's State Modified

            context.Blogs.Remove(blog);
            context.SaveChanges();
        }

        using (var context = new Context())
        {
            var post = context.Posts.Find(1);
            var blog = context.Blogs.Find(2);
        }
    }
}

[Wain123]

Apologies, I found a mistake in my code - indeed the FK is set to null in both cases.

However, if that is the intended behavior, why is #8633 marked as fixed by ClientSetNull?

[ajcvickers]

@Wain123 ClientSetNull for non-nullable FKs (i.e. required relationships) is essentially the same as Restrict because it is not possible to set null. Therefore, an exception is thrown in SaveChanges instead. Nulls can only be set for nullable properties.