CODEWITHSUNDEEP
asp.net-mvcentity-frameworkapi
courtea
courtea

Reputation: 23

Entity framework trying to insert existing parent record when trying to add child record

I am newer to using EF (used Dapper mostly before) and am having issues in an API app that I have taken over mostly built.

I have a method (previous developer built) that is creating or updating a userTerminal. It had a FK to Terminal the current record I am adding to userTerminal is tied to a terminal that already exists.

This is the method

    public void AddTerminalUser(UserTerminal userTerminal)
    {
        using (var context = new GateManagementEntities(connectionString))
        {
            //Modified existing
            if (context.UserTerminals.Count(z => z.TerminalCode == userTerminal.TerminalCode && z.UserId == userTerminal.UserId) > 0)
            {
                UserTerminal ut = new UserTerminal() { Id = userTerminal.Id, IsDeleted = true, LastUpdated = DateTime.Now };
                var entry = context.Entry(ut);
                entry.Property(z => z.LastUpdated).IsModified = true;
                entry.Property(z => z.IsDeleted).IsModified = true;
            }
            else
            {
                //New Entity 
                userTerminal.CreatedDate = DateTime.Now;
                userTerminal.LastUpdated = DateTime.Now;
                context.UserTerminals.Add(userTerminal);
            }
            context.SaveChanges();
        }
    }

and this is the class being passed in

    public partial class UserTerminal
    {
        public int Id { get; set; }
        public string UserId { get; set; }
        public Nullable<int> TerminalCode { get; set; }
        public Nullable<bool> IsDeleted { get; set; }
        public Nullable<System.DateTime> LastUpdated { get; set; }
        public Nullable<System.DateTime> CreatedDate { get; set; }
        public string UpdatedBy { get; set; }

        public virtual Terminal Terminal { get; set; }
        public virtual UserPreference UserPreference { get; set; }
    }

    public partial class Terminal
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Terminal()
        {
            this.GateAssignments = new HashSet<GateAssignment>();
            this.GateAssignments1 = new HashSet<GateAssignment>();
            this.GateDefinitions = new HashSet<GateDefinition>();
            this.UserTerminals = new HashSet<UserTerminal>();
        }

        public int TerminalCode { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public Nullable<System.DateTime> LastUpdated { get; set; }
        public Nullable<System.DateTime> CreatedDate { get; set; }
        public string UpdatedBy { get; set; }
        public Nullable<int> GateTypeId { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<GateAssignment> GateAssignments { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<GateAssignment> GateAssignments1 { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<GateDefinition> GateDefinitions { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<UserTerminal> UserTerminals { get; set; }
        public virtual GateType GateType { get; set; }
    }

TerminalCode is what links Terminal to TerminalUser I checked the UserTerminal object passed in and the UserTerminal's TerminalCode and it is populated with the terminal code I expect (I know that terminal code is in the terminal table) and the Terminal object is fully populated as well with the TerminalCode I expect. It is trying to insert the already existing terminal record into the database causing a PK duplication error.

How do I get it to only all terminal if it doesn't already exist? It seems to follow the same pattern that other methods that are similar are doing, so I haven't been able to figure out why this is different.

Upvotes: 1

Views: 131

Answers (1)

Steve Py
Steve Py

Reputation: 34683

Passing entities between context instances is troublesome. The issue when your context doesn't know about UserTerminal while that new UserTerminal references a real, existing Terminal record is that the new Context instance doesn't know about that Terminal either.

If you load the Terminal (and any other related entities) into the context prior to adding the UserTerminal, it should work, however to be safe it's generally better to associate the new UserTerminal with references to the terminal that the context knows.

For example:

   public void AddTerminalUser(UserTerminal userTerminal)
    {
        using (var context = new GateManagementEntities(connectionString))
        {
            //Modified existing
            if (context.UserTerminals.Count(z => z.TerminalCode == userTerminal.TerminalCode && z.UserId == userTerminal.UserId) > 0)
            {
                UserTerminal ut = new UserTerminal() { Id = userTerminal.Id, IsDeleted = true, LastUpdated = DateTime.Now };
                var entry = context.Entry(ut);
                entry.Property(z => z.LastUpdated).IsModified = true;
                entry.Property(z => z.IsDeleted).IsModified = true;
            }
            else
            {
                //New Entity 
                userTerminal.CreatedDate = DateTime.Now;
                userTerminal.LastUpdated = DateTime.Now;
                var terminal = context.Terminals.Find(userTerminal.TerminalCode); 
                userTerminal.Terminal = terminal;
                // Repeat above for all other references.
                context.UserTerminals.Add(userTerminal);
            }
            context.SaveChanges();
        }
    }

I don't advise passing entities around outside of the scope of the DbContext that loaded them. ViewModels / DTOs should serve this purpose and avoid surprises when you expect FK references to resolve automatically. There are cases where contexts will resolve references seemingly sometimes because the related entity is already loaded, but then fail with duplicate PKs in scenarios where the entity hasn't been previously loaded into the context.

Upvotes: 1

Related Questions