Entity framework with SQL CE don't allow zero values?

Question

I have the following pretty simple code:

using System;
using System.Data.Entity;

namespace TestCSharp
{
    public class Testee
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public Testee()
        {
        }

        public Testee(long id, string name)
        {
            Id = id;
            Name = name;
        }
    }

    public class DatabaseContext : DbContext
    {
        public DatabaseContext()
            : base("name=MyContext")
        {
        }

        public DbSet<Testee> Testees { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var dbContext = new DatabaseContext();
            var testData = new Testee(0, "name");
            dbContext.Testees.Add(testData);
            dbContext.SaveChanges();
            Console.WriteLine("Id: {0}", testData.Id);
        }
    }
}

And I assume that it should print 0 but in fact it prints 1. If I used any other value except 0 then I would get that value. And it adds a row with 1 not 0 as I ask it. What is the problem with zero value? I'm absolutely new to both EF and SQL CE but that looks like a bug, no?

I use the latest available Nuget packages for EF(6.x) and SQL CE(4.x) on desktop Windows 8.1.

UPD: Create table script:

CREATE TABLE [Testees] (
  [Id] bigint IDENTITY (1,1) NOT NULL
, [Name] nvarchar(4000) NULL
);
GO
ALTER TABLE [Testees] ADD CONSTRAINT [PK_dbo.Testees] PRIMARY KEY ([Id]);
GO

Could you script the CREATE TABLE script for your Testee table. — ErikEJ, Apr 30 '15 at 10:39
@ErikEJ, updated(seems like with your tool I did the generation - thanks for the tool!). It seems like the problem is with `IDENTITY (1,1)`, right? Shouldn't I've explicitly asked EF to add this IDENTITY instead of it doing it itself without me asking for it? — ixSci, Apr 30 '15 at 10:51

score 1 · Accepted Answer · edited May 23 '17 at 11:58

If you name a field "Id" or (using your entity as an example) "TesteeId" and you don't specify another field as the primary key using data attributes or the fluent api, then Entity Framework assumes that field is the primary key.

If it is an int or a long, then it will assume that you want the database to generate the key, and it will put an IDENTITY field in the table.

What happens then is that when you add an entity and call SaveChanges(), Entity Framework inserts the data, retrieves the key generated by the database and updates the entity. The first one you add has an Id of 1, the next one would be 2 etc.

If you don't want the key to be generated by the database, then you can add a data attribute:

[DatabaseGenerated(DatabaseGeneratedOption.None)]
public long Id { get; set; }

or use the fluent api:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   modelBuilder.Entity<Testee>()
      .Property(e => e.Id)
      .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
}

Note: if you change the Id field in this way after the table has been generated, then EF will not remove the IDENTITY from the field. This is a non-trivial operation because foreign keys may have to be regenerated for existing data. You have to force a drop and recreate of the table, or fix it up yourself.

Reference:

https://stackoverflow.com/a/18917348/150342

Why does it happen with `0` only? If I set `Id` to `2`, for example, then my `testData` object will have `Id` set to `2` after `SaveChanges` completed not `3`. — ixSci, Apr 30 '15 at 14:07
EF does not send the value to the database, the database generates it. An IDENTITY field is auto incremented http://www.w3schools.com/sql/sql_autoincrement.asp . When you set Id to 2 and testData returned 2 that was because the last inserted value was 1. Coincidence. — Colin, Apr 30 '15 at 14:47

Entity framework with SQL CE don't allow zero values?

1 Answers1