Can't insert the value NULL into column in EF 6.1.3

Question

I created a table in SQL Server Management Studio with two columns, FacilityId is the primary key and not null. After the design, I manually added the first row.

However when I insert the new record into the table programmatically in C# code I get this error:

Cannot insert the value NULL into column 'FacilityId', table 'xxxxxxxx'; column does not allow nulls.

My table in code:

public class MyURL
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Column("FacilityId")]
    public int FacilityId { get; set; }
    [Column("Url")]
    public string Url { get; set; }
}

To add new record by the below code:

public void AddNewUrl(int id, string url)
{
        using (MyUrlContext context = new MyUrlContext())
        {
            context.Database.Connection.ConnectionString = GetConnectionString(context);
            var data = GetNewUrl(id, url);
            SaveContextAfterAddingNewRecord(context, data);
        }
}

public virtual void SaveContextAfterAddingNewRecord(MyUrlContext context, MyURL data)
{
        context.MyEndpoints.Add(data);
        context.SaveChanges();
}

And

public class MyUrlContext : DbContext
{
    public MyUrlContext():base()
    {
        Database.SetInitializer<MyUrlContext>(null);
    }

    public DbSet<MyURL> MyEndpoints { get; set; }
}

So what is wrong?

EDIT:

The db/table structure.

Also, I do have the value provided.

See the image:

I am sure that the identity property is off.

EDIT2:

The SS profiler indicated

exec sp_executesql N'INSERT [dbo].[TableName]([Url])
VALUES (@0)
SELECT [FacilityID]
FROM [dbo].[TableName]
WHERE @@ROWCOUNT > 0 AND [FacilityID] = scope_identity()',N'@0 nvarchar(max) ',@0=N'sdfsdf'

@GertArnold. I am not sure how to generate the SQL statement. I use [this method](http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework). The result is something like `SELECT [Extent1].[FacilityId] AS [FacilityId], [Extent1].[Url] AS [Url] FROM [dbo].[TableName] AS [Extent1] WHERE 163 = [Extent1].[FacilityId]` — , Feb 21 '16 at 02:30
The INSERT statement of course. Just look in the SS profiler. — Gert Arnold, Feb 21 '16 at 08:38
@GertArnold, please see my updated thread about SS profiler. — , Feb 22 '16 at 13:31
@GertArnold, so what is wrong? I couldn't figure it out. Should I redesign the database just let auto identity on and forget the `FacilityId` as the primary key? — , Feb 22 '16 at 14:11
You should figure out why EF doesn't pickup the `[DatabaseGenerated(DatabaseGeneratedOption.None)]` attribute. Is there any additional fluent mapping involved? — Gert Arnold, Feb 22 '16 at 14:13
@GertArnold, no. It is not code first EF, I designed the db at Sql Ser Management Studio. So I don't have additional fluent mapping. I only have the POCO class. — , Feb 22 '16 at 14:20

marc_s · Answer 1 · 2016-02-22T15:03:12.483

0

What's the exact definition of FacilityID in your database table?

Is it defined as an IDENTITY column (to make SQL Server assign values automatically)?
If so, then you need to use [DatabaseGenerated(DatabaseGeneratedOption.Identity)] in your EF model.

If it's not defined as an identity column, then you must provide a value when you insert a new row.

Update: if that column is not an identity column (which seems to be the case), I'd recommend to entirely remove that [DatabaseGenerated(DatabaseGeneratedOption.None)] attribute from your model class:

public class MyURL
{
    [Key]
    [Column("FacilityId")]
    public int FacilityId { get; set; }
    [Column("Url")]
    public string Url { get; set; }
}

edited Feb 22 '16 at 15:03

answered Feb 20 '16 at 08:10

marc_s

732,580
175
1,330
1,459

It is not an identity column and I did provided a value. See the edited body. – Feb 20 '16 at 13:04
@Love: as the SQL Server Profiler log shows - there's no value for `FacilityID` being provided. Since this is *not* an identity column, I would suggest you remove that `[DatabaseGenerated(DatabaseGeneratedOption.None)]` attribute completely from your model class – marc_s Feb 22 '16 at 15:02
I removed it, but nothing changed. The profiler is still same. – Feb 22 '16 at 15:29
By the way, this is a dll. I can insert the new record in the unit test of dll project even with `[DatabaseGenerated(DatabaseGeneratedOption.None)]`. But it failed in an asp.net mvc project when I add the dll to the controller. Not sure whether it is the issue. – Feb 22 '16 at 15:43

score 0 · Accepted Answer · 2016-02-22T21:08:04.987

Finally reslove it under the help of somebody in the team. The code itself is nothing wrong. The problem is that I developed the project as a class library and add it to an asp.net MVC application. The web application is a DNX 4.5.1 project. Because my original dll had some defect I believe, although I updated it, the DNX 4.5.1 framework couldn't find it. It always looked the original dll in the cache.

The right action is to push the dll to the Nuget server, then add to the web application. So the dll version can be incremented automatically.

So whatever I played the attribute, it just is not working. Hopefully in the next release of Visual Studio 2015 release, the issue can be resloved.

Can't insert the value NULL into column in EF 6.1.3

2 Answers2