How to populate a table correctly using Entity framework

Question

I've been given this code:

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;
}
innerContext.SaveChangesAsync().Wait();

I've been told that a record exists in Balance1 and the correstpoding records exist in Balance1Part, rerunning this code causes Balance 1 to get overwritten. If an attempt is made to add a new record to Balance1 a duplicate record error appears as Balance1 always creates an Id of 0.

I think this needs to be changed as follows to resolve the Balance1 issue:

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

if (innerBalance1File == null)
{
    innerBalance1File = new Balance1();
}

Balance1Class balance1Class = new Balance1Class();

innerBalance1File = balance1Class.Balance1Data(innerBalance1File, values);
innerContext.Update(innerBalance1File);
innerContext.SaveChangesAsync().Wait();

Is this correct that update will add a new record if none exists and update if it does and also allow non 0 Id's?

I added the relevant classes for clarity:

public class Balance1 : IAnalyticsSection
{
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)]
    [Key]
    public int Id { get; set; }
    public Guid DataFileId { get; set; }

    public string Side { get; set; }
    public decimal AverageHeadSway { get; set; }
    public decimal AverageSwaySpeed { get; set; }
    public decimal AverageLHandSway { get; set; }
    public decimal AverageRHandSway { get; set; }
    public decimal PercentAverageInLeftSphere { get; set; }
    public decimal PercentAverageInRightSphere { 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 HeadSway { get; set; }
    public decimal SwaySpeeds { get; set; }
    public decimal LeftHandSway { get; set; }
    public decimal RightHandSway { get; set; }
    public decimal PercentInLeftSphere { get; set; }
    public decimal PercentInRightSphere { get; set; }
    public decimal TotalSway { get; set; }

    public virtual Balance1 Balance1 { get; set; }
}


public class DataFile
{
    public Guid Id { get; set; }

    public Guid SessionId { get; set; }

    public string Type { get; set; }

    public string ContentType { get; set; }

    public DateTimeOffset CreatedAt { get; set; }
    public DateTimeOffset ModifiedAt { get; set; }

    public DateTimeOffset UploadedAt { get; set; }

    public string DeviceLocalPath { get; set; }

    public long? Length { get; set; }

    public string DataLocation { get; set; }
    public DateTimeOffset? AnalysisAt { get; set; }
    public DataFileAnalysisStatus AnalysisStatus { get; set; } = DataFileAnalysisStatus.None;


    public virtual Session Session { get; set; }
}


public class Balance1Class
{
    public Balance1 Balance1Data(Balance1 balance1, Dictionary<string, object> values)
    {

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

        balance1.AverageHeadSway = decimal.Parse(values["AverageHeadSway"].ToString());
        balance1.AverageLHandSway = decimal.Parse(values["AverageLHandSway"].ToString());
        balance1.AverageRHandSway = decimal.Parse(values["AverageRHandSway"].ToString());
        balance1.AverageSwaySpeed = decimal.Parse(values["AverageSwaySpeed"].ToString());
        balance1.AverageTotalSway = decimal.Parse(values["AverageTotalSway"].ToString());
        balance1.PercentAverageInLeftSphere = decimal.Parse(values["%AverageInLeftSphere"].ToString());
        balance1.PercentAverageInRightSphere = decimal.Parse(values["%AverageInRightSphere"].ToString());

        for (int i = 1; i < 6; i++)
        {
            Balance1Part balance1Part = new Balance1Part
            {
                HeadSway = decimal.Parse(values["HeadSway" + i].ToString()),
                SwaySpeeds = decimal.Parse(values["SwaySpeeds" + i].ToString()),
                LeftHandSway = decimal.Parse(values["LeftHandSway" + i].ToString()),
                RightHandSway = decimal.Parse(values["RightHandSway" + i].ToString()),
                PercentInLeftSphere = decimal.Parse(values["%InLeftSphere" + i].ToString()),
                PercentInRightSphere = decimal.Parse(values["%InRightSphere" + i].ToString()),
                TotalSway = decimal.Parse(values["TotalSway" + i].ToString()),
                ConvexHullArea = decimal.Parse(values["ConvexHullArea" + i].ToString()),
                Order = i,
            };
            balance1.Parts.Add(balance1Part);
        }

        return balance1;
    }
}

Answer 1

Seems that you need to set the Id because of [DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)] as per this page and if not this would appear to be an issue with your code.

Is there a reason for no auto-incrementing primary key? Which would be configured like this

namespace NorthEntityLibrary.Contexts
{
    public class CustomersConfiguration : IEntityTypeConfiguration<Customers>
    {
        public void Configure(EntityTypeBuilder<Customers> entity)
        {
            entity.HasKey(e => e.CustomerIdentifier)
                .HasName("PK_Customers_1");


        }
    }
}

Answer 2

@Paul

If the ID of an EF Entity = 0, then EF is going to add or insert a new record in the database table. and then assign the ID to the EF Entity that's still in memory. If the ID > 0, then EF is going to update the database table record with the EF Entity data.