C# and EF cannot insert NULL to Id column

Question

I have entity in C# that has Id column. This is Primary key. But I don't to have it as Identity column. I want to assign value to Id column in my code in C#. Entity:

    public class MasterReview
        {
            public int Id { get; set; }

In database I have column Id, Identity Specification is set as no. In C# when I add new entity I set value to Id:

    public void Add(int reviewId, string title, string language, string assignee)
            {
                using (var uow = unitOfWorkFactory.CreateUnitOfWork())
                {
                    uow.MasterReviewRepository.Add(new Models.MasterReview()
                    {
                        Id = reviewId,
                        Title = title,
                        Language = language,
                        Assignee = assignee
                    });

                    uow.Save();
                }
            }

And when I call uow.Save I get error:

Cannot insert the value NULL into column 'Id', table 'MyApp.dbo.MasterReview'; column does not allow nulls. INSERT fails.

Value is set, is not empty. It's 3.

If you hard code something like `Id = 3` does it save as expected with the expected value? Want to try and find the point of failure. — Keith, Jan 20 '20 at 15:08
Did you try to turn SQL Profiler to see what is generated as insert query? — Nemanja Todorovic, Jan 20 '20 at 15:10
visit this link https://stackoverflow.com/questions/13086006/how-can-i-force-entity-framework-to-insert-identity-columns — Marwen Jaffel, Jan 20 '20 at 15:10
I'd prefer using a string for Id and then creating a GUID with salt. Check in your table what data type Id is. — LinkedListT, Jan 20 '20 at 15:12
I've added a screenshot. As you can see Id is set to 4 but error still occurs — Robert, Jan 20 '20 at 21:04

score 4 · Accepted Answer · answered Jan 20 '20 at 15:13

Try to add DatabaseGeneratedOption.None annotation

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

or if you use FluentAPI

modelBuilder.Entity<MasterReview>().Property(t => t.Id)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

DatabaseGeneratedOption.None option specifies that the value of a property will not be generated by the underlying database. So, use it if you want to provide your own values to id properties instead of database generated values

But note: EF will throw an exception if you do not provide unique values each time when Id is a primary key property.

Note: if using EF Core, the FluentAPI is `.ValueGeneratedNever()` — Manuel Fabbri, Jan 20 '20 at 15:57

C# and EF cannot insert NULL to Id column

1 Answers1