0

I'm seeing a strange behavior from Entity Framework. I'm using code-first approach to define a One-To-Many relationship between two entities:

public class IncomingCheck : AuditedEntityBase
{
    [Key]
    public int Id { get; set; }

    [Required]
    public virtual CheckType Type { get; set; }

    [Required]
    public virtual Bank Bank { get; set; }
    public string Branch { get; set; }

    public virtual IList<IncomingCheckHistory> History { get; set; }
}

public class IncomingCheckHistory
{
    [Key]
    public int Id { get; set; }
    public string LongDescription { get; set; }
}

And here's I I'm trying to add an item to the History:

using (var db = new CheckDataContext())
{
    foreach (var check in SelectedItems)
    {
        var dbCheck = await db.IncomingChecks.FindAsync(check.Id);

        var history = new IncomingCheckHistory()
        {
            LongDescription = "something",
        };

        dbCheck.History.Add(history);
        await db.SaveChangesAsync(); //throws the exception
    }
}

But it throws an exception saying that "Cannot insert the value NULL into column 'Id'". However I've always done it like this. The database is supposed to fill the Id column itself with a unique number.

What am I missing?

Update:

Using SSProfiler, I got the query that runs on the database. It's as follows:

exec sp_executesql N'INSERT [dbo].[IncomingCheckHistories]([LongDescription], [IncomingCheck_Id])
VALUES (@0, @1)
SELECT [Id]
FROM [dbo].[IncomingCheckHistories]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()',N'@0 nvarchar(max) ,@1 int',@0=N'Something',@1=1

Note that scope_identity() should be getting the Id itself, correct?

Community
  • 1
  • 1
Alireza Noori
  • 14,961
  • 30
  • 95
  • 179
  • check the table design if ID is identity seeded, if it is then you will need to exclude it from the insert. – RoMEoMusTDiE Jan 05 '18 at 00:52
  • @maSTAShuFu It is. I'm using EF, how am I supposed to exclude it? Also since I've always added entities like this, why doesn't this one work?! – Alireza Noori Jan 05 '18 at 00:54
  • You can add incoming check and a corresponding forgien key prop to the history class and set the key with out getting the check. This will save you a db call and may fix the issue. – rahicks Jan 05 '18 at 01:23
  • @rahicks Tried that too, no luck :( – Alireza Noori Jan 05 '18 at 05:30
  • Possible duplicate of [cannot insert the value null into column ... when the value is not null](https://stackoverflow.com/questions/33995393/cannot-insert-the-value-null-into-column-when-the-value-is-not-null) – DevilSuichiro Jan 05 '18 at 06:51

2 Answers2

0

Shouldn't the Id be set as the Identity? Like this:

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

    [Key]
    public string LongDescription { get; set; }
}

Hope that this is the solution to your problem!

flxwu
  • 11
  • 3
  • Thanks. Doesn't adding the `Key` attribute set it as Identity though? Also why did you set `LongDescription` as `Key`? It's not a key for me. In any case I tried your solution. Doesn't fix it :( – Alireza Noori Jan 05 '18 at 01:02
  • No, the `Key` attribute doesn't set it as identity. Does it still throw the same error? – flxwu Jan 05 '18 at 01:05
  • Yep. Exactly the same error. Actually I'm pretty sure it does because adding the attribute didn't change the database (meaning it didn't throw an exception saying that the model has been changed). – Alireza Noori Jan 05 '18 at 01:07
  • Have you taken a look at https://stackoverflow.com/questions/8855100/why-is-ef-trying-to-insert-null-in-id-column ? – flxwu Jan 05 '18 at 01:17
  • This is handled by [key]. Are there any other props? Seems like something is missing – rahicks Jan 05 '18 at 01:20
  • @rahicks no. I created another migration and copy/pasted the code directly from my solution. – Alireza Noori Jan 05 '18 at 01:24
  • @Pl4gue why nullable? – Alireza Noori Jan 05 '18 at 02:23
0

Well for some strange reason the exact code worked after completely deleting the database and migrations and starting from scratch. I'm not sure why it wouldn't work without migrations. Also I had tried deleting the database before but at the time I was trying a little different code.

In any case the posted code/query should work. Also I checked the database and the columns to see if anything is different and it wasn't. I would appreciate if someone could shed some light on why it wasn't working and it is now.

Alireza Noori
  • 14,961
  • 30
  • 95
  • 179
  • 1
    previously your database column was not set to autoincrement/identity. EF would not pass any id value because it suspects it to be given by DBMS, but your database schema wasn't matching EF's model. – DevilSuichiro Jan 05 '18 at 06:49
  • @DevilSuichiro Before changing the DB, I checked the column. It was set as Identity, it had identity seeded and it was set as `PK, int, not null`. – Alireza Noori Jan 05 '18 at 15:38