How do I set Identity seed on an ID column using Entity Framework 4 code first with SQL Compact 4?

Question

I am having some problem setting the Identity Seed on the Id column in SQL Compact 4 using the code first approach.

I have tried this

context.Database.ExecuteSqlCommand("DBCC CHECKIDENT ('Members', RESEED, 100001");

but this is not working in Sql Compact.

MyDbContext:

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {



        SetupMemberEntity(modelBuilder);


    }

    private static void SetupMemberEntity(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Member>().Property(m => m.Id);
            //.Property(m => m.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        modelBuilder.Entity<Member>().Property(m => m.FirstName).IsRequired();
        modelBuilder.Entity<Member>().Property(m => m.LastName).IsRequired();
        modelBuilder.Entity<Member>().Property(m => m.PinCode).IsRequired();
        modelBuilder.Entity<Member>().Property(m => m.Email);
        //modelBuilder.Entity<Member>().Property(m => m.DateCreated).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);
        //modelBuilder.Entity<Member>().Property(m => m.DateModified).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);


    }

I have added one more property MemberId to the Member entity and have tried to use

  context.Database.ExecuteSqlCommand("ALTER TABLE Members ADD MemberId INT IDENTITY(10000,1) PRIMARY KEY");

but I get error message that a table can only contain one identity, but I havent set any IDENTITY so, is the Id column auto IDENTITY ?

Petaliferous

Answer 1

I found the answer here:

context.Database
    .ExecuteSqlCommand("ALTER TABLE Members ALTER COLUMN Id IDENTITY (10000,1)");

Petaliferous

Answer 2

To set the first seed value for an Identity column using Entity Framework:

builder.Property(prop => prop.Id)
        .UseIdentityColumn(10000000, 1);

from here.

Myrmidon