Entity Framework 6 inserts null as primary key while inserting nested identifying relationships

Question

I have four entities defined using EF6 Code First:

public class Item1
{
    [Key]
    [StringLength(50)]
    public string Name { get; set; }
}

public partial class Item2
{
    [Key]
    [Column(Order = 0)]
    [StringLength(50)]
    public string Item1Name { get; set; }

    [Key]
    [Column(Order = 1)]
    [StringLength(50)]
    public string Name { get; set; }
}

public partial class Item3
{
    [Key]
    [Column(Order = 0)]
    [StringLength(50)]
    public string Item1Name { get; set; }

    [Key]
    [Column(Order = 1)]
    [StringLength(50)]
    public string Item2Name{ get; set; }

    [Key]
    [Column(Order = 2)]
    [StringLength(50)]
    public string Name { get; set; }
}

public partial class Item4
{
    [Key]
    [Column(Order = 0)]
    [StringLength(50)]
    public string Item1Name { get; set; }

    [Key]
    [Column(Order = 1)]
    [StringLength(50)]
    public string Item2Name{ get; set; }

    [Key]
    [Column(Order = 2)]
    [StringLength(50)]
    public string Item3Name{ get; set; }

    [Key]
    [Column(Order = 3)]
    [StringLength(50)]
    public string Name{ get; set; }
}

(I omitted the navigation properties, but they are there.)

With the relationships defined like this:

modelBuilder.Entity<Item1>()
            .HasMany(e => e.Item2s)
            .WithRequired(e => e.Item1)
            .HasForeignKey(e => new { e.Item1Name})
            .WillCascadeOnDelete(false);

modelBuilder.Entity<Item2>()
            .HasMany(e => e.Item3s)
            .WithRequired(e => e.Item2)
            .HasForeignKey(e => new { e.Item1Name, e.Item2Name })
            .WillCascadeOnDelete(false);

modelBuilder.Entity<Item3>()
            .HasMany(e => e.Item4s)
            .WithRequired(e => e.Item3)
            .HasForeignKey(e => new { e.Item1Name, e.Item2Name, e.Name})
            .WillCascadeOnDelete(false);

Now when I try to insert an Item3 into existingItem2.Item3s like this:

Item3 newItem3 = new Item3();
newItem3.Item4s.Add(new Item4());
existingItem2.Item3s.Add(newItem3);
db.SaveChanges();

I get the following error:

Cannot insert the value NULL into column 'Item1Name', table 'Database.dbo.Item4s'; column does not allow nulls. INSERT fails.\r\nThe statement has been terminated.

The weird thing is, after I insert one Item3 into existingItem2.Item3s without the nested Item4, I can insert the nested items using above procedure without any errors. I've tried explicitly designating all the keys with [DatabaseGenerated(DatabaseGeneratedOption.None)], but it makes no difference. What could be at play here?

Edit

I narrowed down the bug and why it sometimes happens and sometimes not. In my program, in addition to what I described, I was also setting existingItem2.someUnrelatedNonKeyIntegerValue = someValue;. Turns out, that if that would actually change the value (it got set always) EF would generate a failing query. Seems to be a bug in EF, right? I worked around it like this:

if (existingItem2.someUnrelatedNonKeyIntegerValue != someValue)
{
   existingItem2.someUnrelatedNonKeyIntegerValue = someValue;
   db.SaveChanges();
}
Item3 newItem3 = new Item3();
newItem3.Item4s.Add(new Item4());
existingItem2.Item3s.Add(newItem3);
db.SaveChanges();

(By the way, the integer doesn't contain ID in the name, and really isn't marked as a key in the database.)

Are you required to use the data model as-is? It's really pretty badly designed which could be the cause of some of your pain. Each table should have a surrogate key that becomes the foreign key for the next table down in the hierarchy. Fixing the design of the database would probably fix your issue and give you a better structure for the long run. — Craig W., Jun 11 '16 at 14:01
I agree with Craig W. The fact that this simple model is already giving you problems. And we are not even talking about performance. I doubt it is a bug in EF, it is more likely that it is not designed to be used like that. With the best intentions I suggest you to download a tutorial with EF6 and take some time to learn from it instead of finding workarounds. Here is one: https://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/creating-an-entity-framework-data-model-for-an-asp-net-mvc-application Notice that the key is a single property of type int and name Id. — , Jun 11 '16 at 14:16
@RuardvanElburg Thanks, but I'm already familiar with that. In fact, that was how I did it, before I refactored it to the current design. Identifying relationships in database design is not a construct I came up with myself and EF supports it in multiple ways. For example, DBSet.Remove() automatically deletes an entity if the relationship with the parent is identifying (http://stackoverflow.com/a/17726414/6452047). — Onne van Dijk, Jun 12 '16 at 10:52
@CraigW. Not required, but it made sense because I primarily make a lot of queries to retrieve Item3 objects based on the names of the parent item1 and item2 that the user sends via a Web API call. — Onne van Dijk, Jun 12 '16 at 10:55

Andrew McCallum · Answer 1 · 2016-06-11T00:35:59.253

Without knowing anything about the database table...

If the column that maps to Item1Name is of the same datatype as the property in the model, you can rememdy this by altering the table column to allow nulls or initialising the models properties within the constructor.

public partial class Item3
{
    public Item3()
    {
     Item1Name = string.empty;
     Item2Name = string.empty;
     Name = string.empty;
    }

    [Key]
    [Column(Order = 0)]
    [StringLength(50)]
    public string Item1Name { get; set; }

    [Key]
    [Column(Order = 1)]
    [StringLength(50)]
    public string Item2Name{ get; set; }

    [Key]
    [Column(Order = 2)]
    [StringLength(50)]
    public string Name { get; set; }
}

Thanks for the help. The database table is generated by EF itself. This unfortunately didn't work because Item1Name does have to be set to the corresponding item1 principal (maybe the existence of an item1 was unclear in my question). I narrowed down the bug though, it's in the edited question. — Onne van Dijk, Jun 11 '16 at 13:17

Entity Framework 6 inserts null as primary key while inserting nested identifying relationships

Edit

1 Answers1