Issue when inserting new entity with relationships to existing entities (Entity Framework Core 1.1.0 )

Question

Issue when inserting new entity with relationships to existing entities (Entity Framework Core 1.1.0 )

c# entity-framework-core

Question

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.

kall2sollies

View more on Stack Overflow

kcwu · Accepted Answer · 1/2/2017 5:37:41 AM

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.

Issue when inserting new entity with relationships to existing entities (Entity Framework Core 1.1.0 )

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related