EF 0..1 to Many Relationship updates

Question

I can't get my head round this, threads all over SO state i'm doing all the right things but clearly I must have missed something ...

Given these two object defs ...

public class Invoice
{
    [Key]
    public int Id { get; set; }

    [ForeignKey("Block")]
    public int? BlockingCodeId { get; set; }

    public virtual BlockingCode Block { get; set; }

    ...

}

public class BlockingCode
{
    [Key]
    public int Id { get; set; }
    public virtual ICollection<Invoice> Invoices { get; set; }
    ...
}

And then context with the appropriate relationship configuration ...

public class FaureciaContext : EFDataContext
{
    public virtual DbSet<Invoice> Invoices { get; set; }
    public virtual DbSet<BlockingCode> BlockingCodes { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Invoice>()
            .HasOptional(e => e.Block)
            .WithMany(e => e.Invoices);
    }
}

Why when I do this ...

// assume this invoice has a BlockingCode relationship
var invoice = db.Invoices.First();
invoice.BlockingCodeId = null;
db.Savechanges();

Do I get this exception ...

The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

EDIT:

I thought I would add, as the answer here doesn't actually point out the real answer to the problem I had ...

It turned out that the reference in question was not the problem I was in fact updating another child property of the invoice class further up the code by something like this ....

invoice.Lines = MergLines(newVersion, dbVersion);

My merging code worked fine but as any keen EF user would know you can't simply "replace a child collection" like this, you have to remove the old and add the new as appropriate.

Answer 1

So, reverse engineering this from what I assume is the basic SQL table structure you are working with...

Block Table:

Invoice Table:

And with a foreign key relationship defined between Invoice.BlockingCodeId and Block.Id...

I get the following code-first entity classes and context when letting EF create them from the physical DB:

[Table("Block")]
public partial class Block
{
    public Block()
    {
        Invoices = new HashSet<Invoice>();
    }

    public int Id { get; set; }

    [Required]
    [StringLength(50)]
    public string Description { get; set; }

    public virtual ICollection<Invoice> Invoices { get; set; }
}

[Table("Invoice")]
public partial class Invoice
{
    public int Id { get; set; }

    public int? BlockingCodeId { get; set; }

    [Required]
    [StringLength(50)]
    public string Description { get; set; }

    public virtual Block Block { get; set; }
}

public partial class TestContext : DbContext
{
    public TestContext()
        : base("name=TestContext")
    {
    }

    public virtual DbSet<Block> Blocks { get; set; }
    public virtual DbSet<Invoice> Invoices { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Block>()
            .HasMany(e => e.Invoices)
            .WithOptional(e => e.Block)
            .HasForeignKey(e => e.BlockingCodeId);
    }
}

When the entities and context are configured as above, the following code executes without issue and I see what I would expect in the SQL database:

var context = new TestContext();
var block = new Block { Description = "Block 1" };
var invoices = new List<Invoice>
                    {
                        new Invoice { Description = "Invoice 1" },
                        new Invoice { Description = "Invoice 2" }
                    };

invoices.ForEach(i => block.Invoices.Add(i));
context.Blocks.Add(block);
context.SaveChanges();

block = null;
var invoice = context.Invoices.First();
invoice.Block = null;
context.SaveChanges();

After execution, the resulting data state is...

Block Table:

Invoice Table: