Unable to update foreign key value using Entity Framework

Viewed 168

When I update an existing record, Entity Framework up sends the original value to database, but the rest of fields works fine.

I'm using Entity Framework 6 code-first and I want to update several columns of an existing record, including foreign key fields. The update script generated has everything right, except one foreign key value. It keeps using the old value instead of the new one I assign.

I've checked similar questions and tried several solution but still couldn't make it to work. I guess my model might have set up wrong.

There are three tables involved: Contract, BillingCategory, Biller.
1. Contract has many BillingCategories
2. Contract has many Billers
3. BillingCategory has many Billers
4. Biller belongs to one Contract and one BillingCategory


[TrackChanges]
public class Biller
{
    [Key]
    public int BillerId { get; set; }

    public int ContractId { get; set; }

    [ForeignKey("ContractId")]
    public virtual Contract Contract { get; set; }

    public int BillingCategoryId { get; set; }

    [ForeignKey("BillingCategoryId")]
    public virtual BillingCategory BillingCategory { get; set; }
}

[TrackChanges]
public class Contract
{
    public Contract()
    {
        BillingCategories = new HashSet<BillingCategory>();
    }

    [Key]
    public int ContractId { get; set; }

    public virtual ICollection<BillingCategory> BillingCategories { get; set; }
}

public class BillingCategory
{
    public BillingCategory()
    {
        Billers = new HashSet<Biller>();
    }

    [Key]
    public int BillingCategoryId { get; set; }

    public int ContractId { get; set; }

    [ForeignKey("ContractId")]
    public virtual Contract Contract { get; set; }

    public virtual ICollection<Biller> Billers { get; set; }
}

I've tried several ways to modify both Id, but when I call SaveChanges(), I can see in the generated script, the BillerCategoryId is its original value rather than the new value I assigned.

  1. Modify Id
    List<Biller> modifiedBillers = _context.Billers.Where(m => m.ContractId == modified.ContractId).ToList();
    foreach (Biller b in modifiedBillers)
    {
        if (...)
        {
            b.ContractId = 23589;
            b.BillingCategoryId = 119662;
        }
    }

  1. Modify both Id and navigation property
b.ContractId = 23589;
b.Contract = _context.Contracts.Where(m => m.ContractId == 23589).FirstOrDefault();

b.BillingCategoryId = 119662;
b.BillingCategory = _context.BillingCategories.Where(m => m.BillingCategoryId == 119662).FirstOrDefault();
  1. Modify Id and load reference
b.ContractId = 23589;
_context.Entry(b).Reference(p => p.Contract).Load();

b.BillingCategoryId = 119662;
_context.Entry(b).Reference(p => p.BillingCategory).Load();

In SQL Profiler, I can see the generated script has wrong BillCategoryId, but correct ContractId, and the rest of fields are also correct. I suspect that the relationship between BillingCategory and Biller is not declared properly.

exec sp_executesql N'UPDATE [dbo].[Billers] SET [ModificationStatus] = @0, [ContractId] = @1, [BillingCategoryId] = @2 WHERE ([BillerId] = @3)',N'@0 int,@1 int,@2 int,@3 int',@0=0,@1=23589,@2=119679,@3=250128

The SaveChanges overrides the DBContext SaveChanges to do some audit trail using DBChangeTracker. Taking out audit logging seems to be helpful in this case, but I don't see how does it affect.

However, even in the changeTracker entries, the BillingAllocationId in both OriginalValues and CurrentValues are 119679. The change on BillingAllocationId somehow not tracked? But it is in the update script?

I'm pretty lost here. Any help would be much appreciated.

The following is the SaveChanges() with audit logging.

    public override int SaveChanges()
    {
        Auditer?.AddEntityChangesForAuditing(EntityChanges, ChangeTracker);
        try
        {
            return base.SaveChanges();
        }      
        catch (Exception e)
        {
          ...
        }
    }

public class ContextChangeAuditer
{
    public void AddEntityChangesForAuditing(IDbSet<EntityChange> entityChanges, DbChangeTracker changeTracker)
    {
        string user = GetUserFromContext();
        var auditableEntities = GetAuditableEntities(changeTracker);
        foreach (var auditableEntity in auditableEntities)
        {
            var entityChange = CreateEntityChange(user, auditableEntity);
            entityChanges.Add(entityChange);
        }
    }

    private IEnumerable<DbEntityEntry> GetAuditableEntities(DbChangeTracker changeTracker)
    {
        var auditableEntities = changeTracker.Entries().Where(x => (x.State == EntityState.Added ||
        x.State == EntityState.Deleted ||
        x.State == EntityState.Modified) &&
        Attribute.GetCustomAttribute(x.Entity.GetType(),
        typeof(TrackChangesAttribute)) != null);

        return auditableEntities;
    }

    private EntityChange CreateEntityChange(string user, DbEntityEntry auditableEntity)
    {
        var entityChange = new EntityChange()
        {
            Date = DateTime.Now,
            User = user,
            Operation = auditableEntity.State,
            Entity = auditableEntity.Entity.GetType().Name,
            AccessType = DataAccessType.EntityFramework,
            RequestCorrelation = RequestTracingService.GetTransactionCorrelation(),
        };

        switch (auditableEntity.State)
        {
            case EntityState.Added:
                entityChange.NewValue = CreateWithValues(auditableEntity.CurrentValues);
                break;

            case EntityState.Deleted:
                entityChange.OldValue = CreateWithValues(auditableEntity.OriginalValues);
                break;

            case EntityState.Modified:
                entityChange.OldValue = CreateWithValues(auditableEntity.OriginalValues);
                entityChange.NewValue = CreateWithValues(auditableEntity.CurrentValues);
                break;
            default:

                throw new ArgumentOutOfRangeException();
        }

        return entityChange;
    }

    private string CreateWithValues(DbPropertyValues values)
    {
        var json = new JObject();
        foreach (var propertyName in values.PropertyNames)
        {
            json.Add(new JProperty(propertyName, values.GetValue < object(propertyName)));
        }
        return json.ToString();
    }
}

Update
Settings in changeTracker._internalContext

AutoDetectChangesEnabled = true  
LazyLoadingEnabled = true  
ProxyCreationEnabled = true  
ValidateOnSaveEnabled = true  
0 Answers
Related