7

So i'm trying to do a simple add an entry to my db on azure but for some reason the db is not generating my PK for the entry.
Below is all the code used to create the db and do the entry.
This is on EF 6.1 on a console app

Context

public BlizzardDbContext() : base("AzureSqlDb")
{
}

public DbSet<Maintenance> Maintenances { get; set; }

Model

public class Maintenance
{
    public Maintenance()
    {}

    public Maintenance(DateTime start, DateTime end, string info)
    {
        Start = start;
        End = end;
        Info = info;
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int MaintenanceId { get; set; }

    public DateTime? Start { get; set; }

    public DateTime? End { get; set; }

    public string Info { get; set; }
}

Test that failed on save changes

var context = new BlizzardDbContext();
context.Maintenances.Add(new Maintenance() {Start = DateTime.Now, End = DateTime.Now, Info = ""});
context.SaveChanges();

I know it sounds so simple, i've used EF a few times before but cannot figure out what is going wrong this time and here's the error

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

enter image description here

Update: Ended up fixing this by deleting the db and recreating it, I think there was something weird going on with EF, it wasn't updating the db with the migration properly since after recreating it the column was then set to be Identity

Community
  • 1
  • 1
Toxicable
  • 1,639
  • 2
  • 21
  • 29
  • 1
    The error message pretty much says *exactly what the problem is*. Either you aren't passing the value because you told EF the database is suppose to supply it, or you are passing the value because the column isn't an identity column. In this case, the column isn't an identity column (or it's been turned off). – Erik Philips Mar 30 '16 at 05:03
  • @ErikPhilips Yes I understand that but as you can see from my model, the data annotation (I also tried with fluent API) tells the db that it is the PK collum. this is the SQL generated from accessing the col in SQL Server Object Explorer `ALTER TABLE [dbo].[Maintenances] ADD CONSTRAINT [PK_dbo.Maintenances] PRIMARY KEY CLUSTERED ([MaintenanceId] ASC);` – Toxicable Mar 30 '16 at 05:05
  • 1
    That is a SQL Server error. The **SQL Server** is telling you it's not an identity column regardless of how you've configured EF. – Erik Philips Mar 30 '16 at 05:07

2 Answers2

2

Just verify whether the MaintenanceId is identity key or not in DB. If it is not or you are not sure you can try below option

change 

 [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

to

[DatabaseGenerated(DatabaseGeneratedOption.None)]

.None - That means "The database does not generate values."

Shrutika Kalra
  • 121
  • 4
  • Oh wow, ok so setting it to None made it work, I am so confused, if i dont incluide it I will get errors but when I got to null it works? as MaintenanceId as the PK still – Toxicable Mar 30 '16 at 05:27
  • 1
    You can find the answer here :) http://stackoverflow.com/questions/15567886/why-does-the-databasegeneratedoption-none-exist – Shrutika Kalra Mar 30 '16 at 06:09
  • 1
    I needed to do it the other way around, as my PK was not auto generated. When passing in my Entity model with the KEY populated, the error message was that the property was NULL.. when it was NOT null. So I had to use the `[DatabaseGenerated(DatabaseGeneratedOption.None)]` and it worked . – Piotr Kula Sep 28 '17 at 14:38
0

Log on to your database and verify that the MaintenanceId is indeed auto-generating the key. EF supports this, and I suspect that something fun happened during migration, if you used that, or during the construction of the table.

Also, make sure that you have not disabled tracking of objects in your DbContext class. It is default on, so unless you explicitly disabled it, you do not have to worry about that one :)

Pedro G. Dias
  • 3,162
  • 1
  • 18
  • 30
  • I cant log onto the db as far as I know since it's a Azure cloud one, however I can access it through VS's SQL Object Explorer which does confirm that it is (PK, int, not null) with the code that created it in the above comment – Toxicable Mar 30 '16 at 05:08
  • You can log on to the cloud DB as long as you have access to the azure portal, you can use SQL Server Management studio if you want. Just point to the url of the server, and use the username/password. Maybe you should try removing the second attribute on your key (DatabaseGenerated) and see if that helps you? I've never used that myself, and still get the adds. – Pedro G. Dias Mar 30 '16 at 05:15
  • Ok so my mistake about before, I thought setting `DatabaseGeneratedOption.None` fixed it but I forgot that `int`'s default value is 0 which let it insert into the db, so it's not. So after doing what you said I checked the DB and that col does not appear to be a Identity col, it is the PK just not the Identity, any idea how to fix this? prefer code first – Toxicable Mar 30 '16 at 07:32
  • 1
    If you have the MaintenanceId tagged as Key, then that should be enough for migration to work it's magic. If you want to quickly fix it in the database, you can just edit the table in SQL Object explorer. – Pedro G. Dias Mar 30 '16 at 07:37
  • Ok I got it this time, I think something weird was going on with EF, such that it wasn't applying the migration properly. So I deleted the DB and recreated it with the same code and it's actually and Identity col now – Toxicable Mar 30 '16 at 07:48
  • Fantastic. Glad to have helped :) – Pedro G. Dias Mar 30 '16 at 07:49