EF 0..1 for many relationship updates

Question

I can't figure it out, threads are in every way SO, I am doing everything right, but obviously I must have missed something ...

Given these two defs objects ...

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 the 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(); 

I get this exception ...

The operation failed: the relationship cannot be changed because one or more properties of the foreign key are not NULL. When a change is made to a relation, set the property of the foreign key associated with it to zero. If the foreign key does not support null values, a new relation must be defined, the property of the foreign key must be assigned a different nonzero value, or an object not associated with it must be deleted.

EDIT:

I thought I would add, since the answer here does not actually indicate a real answer to the problem I had ...

It turned out that this link was not a problem on which I actually updated another child property of the invoice class beyond the code, for example, in this way.

 invoice.Lines = MergLines(newVersion, dbVersion); 

My merge code worked fine, but since any favorite EF user would know that you can't just “replace the child collection” in this way, you need to delete the old one and add a new one if necessary.

Answer 1

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

Block table:

Billing table:

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

I get the following classes and contexts of the first class of code, allowing EF to create them from a physical database:

 [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 objects and context are configured as described above, the following code runs without problems, and I see what I would expect in an 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 ...

Block table:

Billing table: