Updating subtables adds records rather than amending existing records

paul carron 1 Reputation point
2021-05-17T16:25:06.14+00:00

I have these two models:

public class Balance1 : IAnalyticsSection
{
    public int Id { get; set; }
    public Guid DataFileId { get; set; }

    public string Side { get; set; }
    public decimal AverageHSway { get; set; }
    public decimal AverageSSpeed { get; set; }
    public decimal AverageLHSway { get; set; }
    public decimal AverageRHSway { get; set; }
    public decimal PercentAverageInLSphere { get; set; }
    public decimal PercentAverageInRSphere { get; set; }
    public decimal AverageTotalSway { get; set; }

    public virtual ICollection<Balance1Part> Parts { get; set; } = new List<Balance1Part>();
    public virtual DataFile DataFile { get; set; }
}


public class Balance1Part
{
    public int Id { get; set; }
    public int Balance1Id { get; set; }

    public int Order { get; set; }
    public decimal ConvexHullArea { get; set; }
    public decimal HSway { get; set; }
    public decimal SSpeeds { get; set; }
    public decimal LHSway { get; set; }
    public decimal RHSway { get; set; }
    public decimal PercentInLSphere { get; set; }
    public decimal PercentInRSphere { get; set; }
    public decimal TotalSway { get; set; }

    public virtual Balance1 Balance1 { get; set; }
}

In my context I have public DbSet<Balance1> Balance1 { get; set; } but nothing for Balance1Part. There will only ever be 5 Balance1Part records for each Balance1. When I enter a new record into Balance1, it successfully creates with each of the 5 parts. If I try to update Balance1 it succeeds but 5 additional part records are created. These are duplicates of the existing records.

This is is the code that enters/updates my Balance1 and Balance1Parts:

var innerBalance1File = await innerContext.Balance1.SingleOrDefaultAsync(x => x.DataFileId == dataFile.Id);

Balance1Class balance1Class = new Balance1Class();

if (innerBalance1File == null)
{
    innerBalance1File = new Balance1();
    innerBalance1File.DataFileId = dataFile.Id;
    innerBalance1File = balance1Class.Balance1Data(innerBalance1File, values);

    await innerContext.AddAsync(innerBalance1File);
}
else
{
    innerBalance1File = balance1Class.Balance1Data(innerBalance1File, values);
    innerContext.Entry(innerBalance1File).State = EntityState.Modified;
}

await innerContext.SaveChangesAsync();

This is my Balance1Calss:

public class Balance1Class
{
    public Balance1 Balance1Data(Balance1 balance1, Dictionary<string, object> values)
    {
        //Balance1 balance1 = new Balance1();
        //Balance1Part balance1Part = new Balance1Part();

        if ((values["DataType"]).Equals("Balance1R"))
        {
            balance1.Side = "R";
        }
        else
        {
            balance1.Side = "L";
        }

        balance1.AverageHSway = decimal.Parse(values["AverageHSway"].ToString());
        balance1.AverageLHSway = decimal.Parse(values["AverageLHSway"].ToString());
        balance1.AverageRHSway = decimal.Parse(values["AverageRHSway"].ToString());
        balance1.AverageSSpeed = decimal.Parse(values["AverageSSpeed"].ToString());
        balance1.AverageTotalSway = decimal.Parse(values["AverageTotalSway"].ToString());
        balance1.PercentAverageInLSphere = decimal.Parse(values["%AverageInLSphere"].ToString());
        balance1.PercentAverageInRSphere = decimal.Parse(values["%AverageInRSphere"].ToString());

        var balance1Parts = balance1.Parts;  // Gets actual balance parts: returns a list

        if (balance1Parts.Count == 0 || balance1Parts == null)
        {
            for (int i = 1; i < 6; i++)
            {
                Balance1Part balance1Part = new Balance1Part
                {
                    HSway = decimal.Parse(values["HSway" + i].ToString()),
                    SSpeeds = decimal.Parse(values["SSpeeds" + i].ToString()),
                    LHSway = decimal.Parse(values["LHSway" + i].ToString()),
                    RHSway = decimal.Parse(values["RHSway" + i].ToString()),
                    PercentInLSphere = decimal.Parse(values["%InLSphere" + i].ToString()),
                    PercentInRSphere = decimal.Parse(values["%InRSphere" + i].ToString()),
                    TotalSway = decimal.Parse(values["TotalSway" + i].ToString()),
                    ConvexHullArea = decimal.Parse(values["ConvexHullArea" + i].ToString()),
                    Order = i,
                };
                balance1.Parts.Add(balance1Part);
            }
        }
        else
        {
            foreach (var balance1Part in balance1Parts)
            {
                balance1Part.HSway = decimal.Parse(values["HSway" + balance1Part.Order].ToString());
                balance1Part.SSpeeds = decimal.Parse(values["SSpeeds" + balance1Part.Order].ToString());
                balance1Part.LHSway = decimal.Parse(values["LHSway" + balance1Part.Order].ToString());
                balance1Part.RHSway = decimal.Parse(values["RHSway" + balance1Part.Order].ToString());
                balance1Part.PercentInLeftSphere = decimal.Parse(values["%InLSphere" + balance1Part.Order].ToString());
                balance1Part.PercentInRSphere = decimal.Parse(values["%InRSphere" + balance1Part.Order].ToString());
                balance1Part.TotalSway = decimal.Parse(values["TotalSway" + balance1Part.Order].ToString());
                balance1Part.ConvexHullArea = decimal.Parse(values["ConvexHullArea" + balance1Part.Order].ToString());
                balance1Part.Order = int.Parse(values["Order"].ToString());
                balance1.Parts.Add(balance1Part);
            }
        return balance1;
    }
}

I think the context isn't getting updated correctly for the parts as the second time I run this I would expect innerBalance1File to contain the parts but when I logged this out it was not there. Can anybody please advise what's wrong?

This is my context:

public class VRHContext : DbContext
    {
        public VRHContext(DbContextOptions<VRHitContext> options)
            : base(options)
        {

        }

        public DbSet<Organisation> Organisations { get; set; }
        public DbSet<ClientDeviceRegistration> ClientDeviceRegistrations { get; set; }

        public DbSet<Session> Sessions { get; set; }
        public DbSet<User> Users { get; set; }

        public DbSet<DataFile> DataFiles { get; set; }

        public DbSet<Symptoms> Symptoms { get; set; }
        public DbSet<Balance1> Balance1 { get; set; }
        public DbSet<Balance1Raw> Balance1Raw { get; set; }
        public DbSet<Balance2> Balance2 { get; set; }
        public DbSet<Balance2Raw> Balance2Raw { get; set; }
        public DbSet<Calibration> Calibration { get; set; }
        public DbSet<CogMotor1> CogMotor1 { get; set; }
        public DbSet<CogMotor1Raw> CogMotor1Raw { get; set; }
        public DbSet<CogMotor2> CogMotor2 { get; set; }
        public DbSet<CogMotor2Raw> CogMotor2Raw { get; set; }

        public DbSet<CogMotor3> CogMotor3 { get; set; }
        public DbSet<CogMotor3Raw> CogMotor3Raw { get; set; }

        public DbSet<Gait> Gait { get; set; }
        public DbSet<GaitRaw> GaitRaw { get; set; }

        public DbSet<Interception1> Interception1 { get; set; }
        public DbSet<Interception2> Interception2 { get; set; }
        public DbSet<InterceptionPlayerRaw> InterceptionPlayerRaw { get; set; }
        public DbSet<ManualDexterity> ManualDexterity { get; set; }
        public DbSet<ManualDexterityRaw> ManualDexterityRaw { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>().HasIndex(u => u.UserIdentifier).IsUnique();

            // Set default decimal precision.
            foreach (var property in modelBuilder.Model.GetEntityTypes()
                .SelectMany(t => t.GetProperties())
                .Where(p => p.ClrType == typeof(decimal) || p.ClrType == typeof(decimal?)))
                        {
                            property.SetColumnType("decimal(38, 10)");
                        }


            // Set default for GUID primary keys.
            foreach (var property in modelBuilder.Model.GetEntityTypes()
                .SelectMany(t => t.GetProperties())
                .Where(p => (p.ClrType == typeof(Guid) || p.ClrType == typeof(Guid?)) && p.IsPrimaryKey()))
            {
                property.SetDefaultValueSql("NEWID()");
            }
        }
    }

Some things I'm curious about. Should public DbSet<Balance1Part> Balance1Part { get; set; } be added to the context and should I have innerContext.Attach(Balance1Part); before innerContext.Entry(innerBalance1File).State = EntityState.Modified;?

Developer technologies | .NET | Entity Framework Core
Developer technologies | C#
{count} votes

1 answer

Sort by: Most helpful
  1. Karen Payne MVP 35,586 Reputation points Volunteer Moderator
    2021-05-17T17:05:43.27+00:00

    I'd recommend turning on logging and inspect the SQL generated by EF as done in the following sample code.

    Seems if records are not being added but updated this would appear to do with a primary key that exists or does not exists.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.