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 :
- A person has many addresses.
- A place has several persons, and also several addresses.
- Places, Persons and Addresses have statuses.
- All entities have an Id, Name, Created date and Modified date (these fields are all defined in an abstract
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.
