4

I have an entity that has an identity column. As part of the data-seed I want to use specific identifier values for the "standard data" in my system. I dont want disable identity. only i want to set IDENTITY_INSERT ON in migration seed.

My code is:

protected override void Seed(DelphyWCFTestService.Model.DataContext context)
{
    context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Cities] On ");
    context.Cities.AddOrUpdate(
        p => p.CityId,
        new City { CityId = 1, Title = "Paris" }
    );
}

but my CityId not insert and identity automaticaly inserted

My Entityframework version is 6.1.3

Update:

I change My code to:

context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Cities] On ");
var cities = new List<City>
{
    new City { CityId = 1, Title = "Paris" }
};
context.Cities.AddRange(cities);
context.SaveChanges();
context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Cities] Off ");

and the problem is not resolved.

Morteza
  • 2,378
  • 5
  • 26
  • 37

3 Answers3

4

AddOrUpdate isn't as straightforward as standard LINQ (see here).

I would just use standard LINQ:

if (!context.Cities.Any())
{
  using (var transaction = context.Database.BeginTransaction())
  {        var cities = new List<City> 
    {
       new City { CityId = 1, Title = "Paris" },
       new City { CityId = 2, Title = "London" },
       ...
       new City { CityId = 99, Title = "Rome" }
    }
    context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Cities] On ");
    context.Cities.AddRange(cities);
    context.SaveChanges();
    context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Cities] Off ");

    transaction.Commit();
  }
}

If you are adding to existing cities, you could just test one by one:

if (!context.Cities.Any(c => c.CityId == 1))
{
    context.Cities.Add(new City { CityId = 1, Title = "Paris" });
}
... repeat for all cities 
context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Cities] On ");
context.SaveChanges();
context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Cities] Off ");
Steve Greene
  • 12,029
  • 1
  • 33
  • 54
  • I change AddOrUpdate to Add and AddRange , but dont resolve my problem. – Morteza Jun 14 '16 at 15:16
  • OK, problem may be the batch fashion that Seeding works - each of those commands is separately executed. See my edit to wrap a transaction or you could try this: http://stackoverflow.com/questions/7714812/why-doesnt-set-identity-insert-off-work-for-me-in-ef-4-1 – Steve Greene Jun 14 '16 at 15:39
0

You can find a good explaination here:
http://blog.robertobonini.com/2014/10/09/entity-framework-with-identity-insert/

You can solve the problem by using two encapsulated scopes.

Ronin
  • 179
  • 11
-1
context.Database.ExecuteSqlCommand($"DBCC CHECKIDENT('Table',RESEED, {context.Tables.Count(x => x.RelatedEntityKey == null)})");
context.Set<Table>().AddOrUpdate(Data);
context.SaveChanges();

This works for me

Asiri Dissanayaka
  • 474
  • 1
  • 7
  • 18