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);
    //innerContext.Entry(innerBalance1File).State = EntityState.Added;
}
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 correspoding 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

I believe that you might be setting the ID when you don't need to: it's an autonum field so it should be generated automatically...

As for updating a field if it exits I would check this answer.

Also, I would take a look at your await innerContext.AddAsync(innerBalance1File); statement and check if it's in an async method. E.g. You can make it async by changing static void MyMethod()...to static async Task MyMethod()...

Answer 2

is there any specific reason why you are using DatabaseGeneratedAttribute(DatabaseGeneratedOption.None ?

Because this is the reason why it's generating an id of 0

If not you could just take it off or you could put this instead [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

You can read here the documentation about this here :

https://www.learnentityframeworkcore.com/configuration/data-annotation-attributes/databasegenerated-attribute

And in the end change innerContext.SaveChangesAsync().Wait() to await innerContext.SaveChangesAsync() so it doesn't deadlock.

That should solve the problem