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

Question

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

public class EmployeeRate : BaseEntity
{
    [Key]
    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;
    }
    _context.SaveChanges();

    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;
    _context.SaveChanges();

    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?

EDIT

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

I don't understand the line newRate = _context.EmployeeRates.Add(newRate).Entity;... everything else looks fine. How does BaseEntity looks like? — Marc
@Marc I've edited my post for BaseEntity. Sorry, i tried to simplify it for the sake of the post before but made it more confusing ;) that line of code in question is simply adding the newRate object to the EmployeeRates DbSet and then assigning the returned entity back to new rate so that the generated Id is populated when _context.SaveChanges() is called — big_water
@stephen.vakil, i thought for some reason that the update operation may have been causing SQL issues if it wasn't committed before I ran the Add. I proved this wrong however, because I ran it with the entire update portion commented out and I get the same result — big_water
All, I think it may be an issue with having two of the same "EmployeeId" values in two different rows. If I manually add a few records to the DB with the same EmployeeId as the one I'm adding, it will delete all of them. Which is weird, because the generated SQL is deleting based on "EmployeeRateId" — big_water

big_water big_water · Accepted Answer · 2016-12-09T15:50:04

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 ;)

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

1 Answers