CODEWITHSUNDEEP
entity-frameworkmigrationseedidentity-insert
Morteza
Morteza

Reputation: 2428

How to Insert Identity by AddOrUpdate methode in Seed Entityframework version 6.x.x

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.

Upvotes: 4

Views: 3736

Answers (3)

Asiri Dissanayaka
Asiri Dissanayaka

Reputation: 510

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

Upvotes: -1

Ronin
Ronin

Reputation: 172

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.

Upvotes: 0

Steve Greene
Steve Greene

Reputation: 12324

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 ");

Upvotes: 4

Related Questions