Why is Entity Framework (Core) deleting old records on Add() operation?

asp.net-core c# entity-framework entity-framework-core


I have an entity that needs history tracked on it for employee pay rates. The employeeRate object is:

public class EmployeeRate : BaseEntity
    public int EmployeeRateId { get; set; }

    public int EmployeeId { get; set; }

    public double Rate { get; set; }

public class BaseEntity
    public bool ActiveFlag { get; set; }

    public DateTime CreateStamp { get; set; }

    public DateTime UpdateStamp { get; set; }

    public string LastModifiedBy { get; set; }

So to track history, when I "update" an employees rate, I retrieve all old records with their Id and set the ActiveFlag to false, then add a new entity with the new pay rate and the ActiveFlag set to true. Here is my function for this operation:

private EmployeeRate UpdateRate(EmployeeRate model, string currentUser)
    var existingRates = _context.EmployeeRates.Where(r => r.EmployeeId == model.EmployeeId).ToList();
    foreach(var rate in existingRates)
        rate.ActiveFlag = false;
        rate.UpdateStamp = DateTime.UtcNow;
        rate.LastModifiedBy = currentUser;

    var newRate = new EmployeeRate()
        EmployeeId = model.EmployeeId,
        Rate = model.Rate,
        ActiveFlag = true,
        CreateStamp = DateTime.UtcNow,
        UpdateStamp = DateTime.UtcNow,
        LastModifiedBy = currentUser

    newRate = _context.EmployeeRates.Add(newRate).Entity;

    return newRate;

When I do an Add operation on my DbContext object, the SQL being generated by EntityFramework is deleting all old records for that employee for some reason.
Generated SQL:

DELETE FROM employeerates WHERE EmployeeRateId = @p0;

INSERT INTO employeerates (ActiveFlag, CreateStamp, EmployeeId, LastModifiedBy, Rate, UpdateStamp) VALUES (@p1, @p2, @p3, @p4, @p5, @p6); SELECT EmployeeRateId FROM employeerates WHERE ROW_COUNT() = 1 AND EmployeeRateId=LAST_INSERT_ID();

Why would this be happening? Is there a way to keep EF from deleting these old records?


I am using asp.net core and entity framework core.

12/10/2016 8:46:42 AM

Accepted Answer

Prepare to call me stupid...

Because of how I want to track history on the EmployeeRate object, I was thinking of my relationships between Employee and EmployeeRate incorrectly. Here was my original Employee object (which I should've posted originally):

    public class Employee : BaseEntity
        public int EmployeeId { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

        public string Email { get; set; }

        public string PhoneNumber { get; set; }

        public int HomeLocationId { get; set; }

        //Navigation Properties
        public virtual Location HomeLocation { get; set; }

        public virtual EmployeeRate EmployeeRate { get; set; }

Some of you may see the problem right away here. The EmployeeRate reference actually has to be this:

public virtual List<EmployeeRate> EmployeeRates { get; set; }

because it is actually a one to many relationship. Due to how I was thinking about only having to have one rate per employee, I only had the singular reference before (making it one to one as far as EF is concerned). So when I updated the reference, EF thought it was one to one so it deleted all other records with the same reference. It's actually "One to Many" with only ONE rate "Active" at a time. So the EmployeeRate navigation property needs to be referenced as a List or Collection for EF to work properly and not delete old records. Stupid me ;)

12/9/2016 3:50:04 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow