I have encountered an issue when inserting (Add
method of EF API) or updating (Update
method of EF API) entities holding reference properties to existing entities (I call existing entity an entity that already exists in the database, and has its PK properly set).
The model consists in Place
, Person
, Address
, and Status
:
BaseEntity
)If I create a whole graph for a "Place", with new Persons and new Addresses, and save it in one step, everything is fine.
If I create a Place with Addreses then save it, it is still ok. But at last when I add an existing person and resave the Place, I have an exception: EF actually tries to insert the existing person, and SQL Server throws an error because EF tried to insert a row with a provided Id (PK are set to be generated by SQL Server).
That means that by default, EF Core 1.1.0 looks like being unable to properly traverse relationships and discover which enitites should be added, and which one should be ignored or updated. It tries to insert an entity which already has its PK set to a positive value.
After doing some research, I discovered the new DbContext.ChangeTracker.Track()
method of the EF Core 1.1.0 API, and it allows one to execute a callback method on all the entities discovered by traversing the relationships of the root entity.
Thanks to this, I have set up the appropriate State, according to the value of the primary key.
Without this code (in DbRepository.ApplyStates()
), none of my insert would work, as long as they would refer a relation to an existing entity.
Note that with EF7 and the DNX CLI, this scenario would work, even without the DbRepository.ApplyStates() thing.
Source to reproduce
everything is in there: models, DbContext, Repository and test code.
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using Microsoft.EntityFrameworkCore;
namespace EF110CoreTest
{
public class Program
{
public static void Main(string[] args)
{
Seed();
}
private static void Seed()
{
// Repo
var statusRepo = new DbRepository<Status>();
var personRepo = new DbRepository<Person>();
var addressRepo = new DbRepository<Address>();
var placeRepo = new DbRepository<Place>();
// Status
if (!statusRepo.GetAll().Any())
{
statusRepo.InsertOrUpdate(new Status() { Name = "Active" });
statusRepo.InsertOrUpdate(new Status() { Name = "Archive" });
statusRepo.SaveChanges();
}
var statusActive = statusRepo.GetSingle(1);
var statusArchive = statusRepo.GetSingle(2);
// Delete the non static data
foreach(var address in addressRepo.GetAll()) addressRepo.Delete(address);
addressRepo.SaveChanges();
foreach (var place in placeRepo.GetAll()) placeRepo.Delete(place);
placeRepo.SaveChanges();
foreach (var person in personRepo.GetAll()) personRepo.Delete(person);
personRepo.SaveChanges();
Console.WriteLine("Cleared any existing data");
/***********************************************************************/
// Step 1 : a person with status and addresses is saved
var personWithAddresses = new Person()
{
Name = "Jon SNOW",
Status = statusActive,
AddressCollection = new List<Address>()
{
new Address() { City = "Castleblack", Status = statusActive },
new Address() { City = "Winterfel", Status = statusArchive }
}
};
personRepo.InsertOrUpdate(personWithAddresses);
personRepo.SaveChanges();
Console.WriteLine("Step 1 ok");
System.Threading.Thread.Sleep(1000);
/***********************************************************************/
// Step 2 : Create a place with addresses
var placeWithAddress = new Place()
{
Name = "Castleblack",
Status = statusActive
};
placeWithAddress.AddressCollection.Add(new Address() { City = "Castleblack", Status = statusActive });
placeRepo.InsertOrUpdate(placeWithAddress);
placeRepo.SaveChanges();
Console.WriteLine("Step 2 ok");
System.Threading.Thread.Sleep(1000);
/***********************************************************************/
// Step 3 : add person to this place
placeWithAddress.PersonCollection.Add(personWithAddresses);
placeRepo.InsertOrUpdate(placeWithAddress);
placeRepo.SaveChanges();
Console.WriteLine("Step 3 ok");
System.Threading.Thread.Sleep(1000);
}
}
public class DbRepository<T> where T : BaseEntity
{
protected readonly MyContext _context;
public DbRepository() { _context = new MyContext(); }
public T GetSingle(int id) => _context.Set<T>().FirstOrDefault(e => e.Id == id);
public IEnumerable<T> GetAll() => _context.Set<T>().AsEnumerable();
public void Insert(T entity)
{
ApplyStates(entity);
_context.Add(entity);
}
public void Update(T entity)
{
ApplyStates(entity);
_context.Update(entity);
}
public void Delete(T entity)
{
_context.Remove(entity);
}
private void ApplyStates(T entity)
{
_context.ChangeTracker.TrackGraph(entity, node =>
{
var entry = node.Entry;
var childEntity = (BaseEntity)entry.Entity;
entry.State = childEntity.IsNew ? EntityState.Added : EntityState.Modified;
});
}
public void InsertOrUpdate(T entity)
{
if (entity.IsNew) Insert(entity); else Update(entity);
}
public void SaveChanges()
{
var pendingChanges = _context.ChangeTracker.Entries<T>()
.Where(entry => entry.State == EntityState.Added || entry.State == EntityState.Modified)
.Select(e => e.Entity)
.ToList();
foreach (var entity in pendingChanges)
{
entity.Modified = DateTime.Now;
if (entity.Created == null) entity.Created = DateTime.Now;
}
_context.SaveChanges();
}
}
#region Models
public abstract class BaseEntity
{
public int Id { get; set; }
public string Name { get; set; }
public DateTime? Created { get; set; }
public DateTime? Modified { get; set; }
[NotMapped]
public bool IsNew => Id <= 0;
}
public class Person : BaseEntity
{
public int? StatusId { get; set; }
public Status Status { get; set; }
public List<Address> AddressCollection { get; set; } = new List<Address>();
}
public class Address : BaseEntity
{
public string Zip { get; set; }
public string City { get; set; }
public int? StatusId { get; set; }
public Status Status { get; set; }
public int? PersonId { get; set; }
public Person Person { get; set; }
public int? PlaceId { get; set; }
public Place Place { get; set; }
}
public class Place : BaseEntity
{
public int? StatusId { get; set; }
public Status Status { get; set; }
public List<Person> PersonCollection { get; set; } = new List<Person>();
public List<Address> AddressCollection { get; set; } = new List<Address>();
}
public class Status : BaseEntity { }
#endregion
#region Context
public class MyContext : DbContext
{
public DbSet<Status> StatusCollection { get; set; }
public DbSet<Person> PersonCollection { get; set; }
public DbSet<Address> AddressCollection { get; set; }
public DbSet<Place> PlaceCollection { get; set; }
protected override void OnModelCreating(ModelBuilder builder)
{
// Basic event fire of model creation
base.OnModelCreating(builder);
// Status
builder.Entity<Status>().ToTable("Status", "Shared");
// Person
builder.Entity<Person>().ToTable("Person", "Shared");
builder.Entity<Person>()
.HasMany(p => p.AddressCollection)
.WithOne(a => a.Person);
// Address
builder.Entity<Address>().ToTable("Address", "Shared");
builder.Entity<Address>()
.HasOne(p => p.Person)
.WithMany(a => a.AddressCollection);
// Place
builder.Entity<Place>().ToTable("Place", "Shared");
builder.Entity<Place>()
.HasMany(p => p.AddressCollection)
.WithOne(p => p.Place);
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EF110CoreTest;Trusted_Connection=True;");
}
}
#endregion
}
Project.json file
{ "version": "1.0.0-*", "buildOptions": { "emitEntryPoint": true },
"dependencies": {
"Microsoft.EntityFrameworkCore": "1.1.0",
"Microsoft.EntityFrameworkCore.SqlServer": "1.1.0",
"Microsoft.EntityFrameworkCore.Tools": "1.1.0-preview4-final"
},
"frameworks": {
"net461": {}
},
"tools": {
"Microsoft.EntityFrameworkCore.Tools.DotNet": "1.0.0-preview3-final"
}
}
Exception details
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'Person' when IDENTITY_INSERT is set to OFF.
I modified some code, please review it.
In class DbRepository
, added another constructor, to make sure there is the same DbContext
in different DbRepository
.
public DbRepository(MyContext myContext)
{
_context = myContext;
}
In class Person
added 2 properties, to ensure the relation between Person
and Place
.
public int? PlaceId { get; set; }
public Place Place { get; set; }
In function Seed
, modified some code with above modifications.
Firstly, in the part of initialize repository.
// Repo
var myContext = new MyContext();
var statusRepo = new DbRepository<Status>(myContext);
var personRepo = new DbRepository<Person>(myContext);
var addressRepo = new DbRepository<Address>(myContext);
var placeRepo = new DbRepository<Place>(myContext);
This will make all repository use same database connection.
Secondly, due to those changes, the clear process should change the orders, too.
// Delete the non static data
foreach (var address in addressRepo.GetAll()) addressRepo.Delete(address);
addressRepo.SaveChanges();
foreach (var person in personRepo.GetAll()) personRepo.Delete(person);
personRepo.SaveChanges();
foreach (var place in placeRepo.GetAll()) placeRepo.Delete(place);
placeRepo.SaveChanges();
In your Step 1
, I extract the address with CatsleBlack
, because I guess the one in Person
and the other one in Place
should be the same.
So, when you initialize a new Person
, it will be
var castleBlack = new Address {City = "Castleblack", Status = statusActive};
var personWithAddresses = new Person()
{
Name = "Jon SNOW",
Status = statusActive,
AddressCollection = new List<Address>()
{
castleBlack,
new Address() { City = "Winterfel",
Status = statusArchive }
}
};
Initialize the Place
var placeWithAddress = new Place()
{
Name = "Castleblack",
Status = statusActive
};
placeWithAddress.AddressCollection.Add(castleBlack);
Those are what I have done, can save successfully. The Person
record in db also has its PlaceId
.