Efcore: Insertions are taken as updates on a seeder

[Dongata]

Description

In short, whenever we try to update the company to add the star ratings EFcore interprets it as an update on Star Rating instead of an insert.

We're having a structure like this:

We're using a ddd approach in which company is our aggregation root. So, this is our only reference of access on the db context.

Note that we're only trying to add some new definitions onto the company.

We're using 3.1.9 on everything
Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Relational

public static class StarRatingSeeder
{
    //scoped provider
    public static void Seed(IServiceProvider provider)
    {
        var context = provider.GetService<ITransactDbContext>();

        // Edenorte
        var edenorte = context.Companies
            .Include(a => a.StarRatings)
            .FirstOrDefault(a => a.Id == SeedersIdentifiers.COMPANY_ID_EDENORTE);

        if (edenorte != null && !edenorte.StarRatings.Any())
        {
            FillWithRatingReasons(edenorte);
        }
    }

     private static void FillWithRatingReasons(Company company)
    {
        company.AddStarRatingRange(GetEmptyStarRatings());
    }

    private static List<StarRating> GetEmptyStarRatings()
    {
        var ratingConfig1Star = new StarRating(1, true, null);
        var ratingConfig2Star = new StarRating(2, true, null);
        var ratingConfig3Star = new StarRating(3, true, null);
        var ratingConfig4Star = new StarRating(4, true, null);
        var ratingConfig5Star = new StarRating(5, true, null);

        var result = new List<StarRating>();
        result.Add(ratingConfig1Star);
        result.Add(ratingConfig2Star);
        result.Add(ratingConfig3Star);
        result.Add(ratingConfig4Star);
        result.Add(ratingConfig5Star);
        return result;
    }
}

Here are the logs:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[@__COMPANY_ID_EDENORTE_0='1488953f-c093-47dc-a03d-0fbee4dc9412'], CommandType='Text', CommandTimeout='30']
      SELECT [t].[Id], [t].[CategoryId], [t].[CreatedBy], [t].[CreatedOn], [t].[DeletedBy], [t].[DeletedOn], [t].[ImageId], [t].[InstanceConfigurationId], [t].[IntegrationConfigurationId], [t].[Name], [t].[RegionId], [t].[UpdatedBy], [t].[UpdatedOn], [s].[Id], [s].[CompanyId], [s].[IsReasonSelectionRequired], [s].[StarValue]
      FROM (
          SELECT TOP(1) [c].[Id], [c].[CategoryId], [c].[CreatedBy], [c].[CreatedOn], [c].[DeletedBy], [c].[DeletedOn], [c].[ImageId], [c].[InstanceConfigurationId], [c].[IntegrationConfigurationId], [c].[Name], [c].[RegionId], [c].[UpdatedBy], [c].[UpdatedOn]
          FROM [Company] AS [c]
          WHERE [c].[DeletedOn] IS NULL AND ([c].[Id] = @__COMPANY_ID_EDENORTE_0)
      ) AS [t]
      LEFT JOIN [StarRating] AS [s] ON [t].[Id] = [s].[CompanyId]
      ORDER BY [t].[Id], [s].[Id]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (9ms) [Parameters=[@p3='028012e8-ada4-4651-87c3-912bb5155da1', @p0='1488953f-c093-47dc-a03d-0fbee4dc9412', @p1='True', @p2='2' (Size = 1), @p7='6e742788-dc71-42e2-924c-a8e5a7187bf9', @p4='1488953f-c093-47dc-a03d-0fbee4dc9412', @p5='True', @p6='3' (Size = 1), @p11='81577e41-c1cd-4ef7-b3c4-beeaa5c5a4eb', @p8='1488953f-c093-47dc-a03d-0fbee4dc9412', @p9='True', @p10='1' (Size = 1), @p15='8f97bda8-8ce0-447c-b128-207647511780', @p12='1488953f-c093-47dc-a03d-0fbee4dc9412', @p13='True', @p14='4' (Size = 1), @p19='c84ed568-0a1e-4479-a694-27d150eac9f5', @p16='1488953f-c093-47dc-a03d-0fbee4dc9412', @p17='True', @p18='5' (Size = 1)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      UPDATE [StarRating] SET [CompanyId] = @p0, [IsReasonSelectionRequired] = @p1, [StarValue] = @p2
      WHERE [Id] = @p3;
      SELECT @@ROWCOUNT;

      UPDATE [StarRating] SET [CompanyId] = @p4, [IsReasonSelectionRequired] = @p5, [StarValue] = @p6
      WHERE [Id] = @p7;
      SELECT @@ROWCOUNT;

      UPDATE [StarRating] SET [CompanyId] = @p8, [IsReasonSelectionRequired] = @p9, [StarValue] = @p10
      WHERE [Id] = @p11;
      SELECT @@ROWCOUNT;

      UPDATE [StarRating] SET [CompanyId] = @p12, [IsReasonSelectionRequired] = @p13, [StarValue] = @p14
      WHERE [Id] = @p15;
      SELECT @@ROWCOUNT;

      UPDATE [StarRating] SET [CompanyId] = @p16, [IsReasonSelectionRequired] = @p17, [StarValue] = @p18
      WHERE [Id] = @p19;
      SELECT @@ROWCOUNT;

Entity Type Configurations

CompanyConfiguration

public class CompanyEntityTypeConfiguration : IEntityTypeConfiguration<Company>
{
    public void Configure(EntityTypeBuilder<Company> builder)
    {
        builder.HasKey(a => a.Id);

        builder.HasMany(a => a.Branches).WithOne(a => a.Company);

        builder.Property(a => a.Name)
           .HasMaxLength(255)
           .IsRequired();

        builder.HasMany(a => a.Tags)
            .WithOne()
            .HasForeignKey("CompanyId")
            .IsRequired()
            .OnDelete(DeleteBehavior.Cascade);

        builder.HasMany(a => a.CompanyDocuments)
            .WithOne()
            .HasForeignKey("CompanyId")
            .IsRequired()
            .OnDelete(DeleteBehavior.Cascade);

        builder.HasMany(a => a.StarRatings)
            .WithOne()
            .HasForeignKey("CompanyId")
            .IsRequired()
            .OnDelete(DeleteBehavior.Cascade);

        builder.HasQueryFilter(a => a.DeletedOn == null);

        builder.ToTable("Company");
    }
}

StarRatingConfiguration

public class StarRatingEntityTypeConfiguration : IEntityTypeConfiguration<StarRating>
{
    public void Configure(EntityTypeBuilder<StarRating> builder)
    {
        builder.HasKey(a => a.Id);

        builder.HasMany(a => a.RatingReasons)
            .WithOne()
            .HasForeignKey("StarRatingId")
            .IsRequired()
            .OnDelete(DeleteBehavior.Cascade);

        builder.Property(a => a.StarValue)
            .IsRequired();

        builder.Property(a => a.IsReasonSelectionRequired)
            .IsRequired();

        builder.ToTable("StarRating");
    }
}

Models

Company

public class Company : TaggedEntity<Company>
{
    #region Fields

    private string _name;

    #endregion

    #region Constructors

    public Company(string name,
        Guid regionId,
        string createdBy,
        List<StarRating> ratingConfigurations = null)
        : base(regionId, createdBy)
    {
        Name = name;
        Branches = new List<Branch>();
        CompanyDocuments = new List<CompanyDocument>();

        StarRatings = new List<StarRating>();
        if (ratingConfigurations != null)
        {
            AddStarRatingRange(ratingConfigurations);
        }
    }

    public Company()
    {
        Branches = new List<Branch>();
        CompanyDocuments = new List<CompanyDocument>();
        StarRatings = new List<StarRating>();
    }

    #endregion

    #region Properties

    /// <summary>
    /// Company's name
    /// </summary>
    public string Name
    {
        get => _name;
        set
        {
            string sanitizedValue = value.Sanitize();
            if (string.IsNullOrEmpty(sanitizedValue))
            {
                throw new ValidationException(Errors.CompanyNameCanNotBeNullOrEmpty);
            }

            if (sanitizedValue.Length < 2)
            {
                throw new ValidationException(Errors.CompanyNameMustHaveTwoCharactersAtLeast);
            }

            _name = value.Trim();
        }
    }

    /// <summary>
    /// Company's image.
    /// </summary>
    public Image Image { get; set; }

    /// <summary>
    /// Company's image identifier.
    /// </summary>
    public Guid? ImageId { get; set; }

    /// <summary>
    /// Company's branches.
    /// </summary>
    public List<Branch> Branches { get; set; }

    /// <summary>
    /// Company's documents.
    /// </summary>
    public List<CompanyDocument> CompanyDocuments { get; set; }

    /// <summary>
    /// Company's category.
    /// </summary>
    public Category Category { get; set; }

    /// <summary>
    /// Company's integration configuration.
    /// </summary>
    public IntegrationConfiguration IntegrationConfiguration { get; set; }

    /// <summary>
    /// Company's integration configuration identifier.
    /// </summary>
    public Guid? IntegrationConfigurationId { get; set; }

    /// <summary>
    /// Company's instance configuration.
    /// </summary>
    public InstanceConfiguration InstanceConfiguration { get; set; }

    /// <summary>
    /// Company's instance configuration identifier.
    /// </summary>
    public Guid? InstanceConfigurationId { get; set; }

    /// <summary>
    /// The rating configurations for this company.
    /// This list should have always 5 elements (1 by each possible rating selection).
    /// </summary>
    public List<StarRating> StarRatings { get; set; }

    #endregion

    #region Public Methods

    /// <summary>
    /// Creates a new branch
    /// </summary>
    /// <param name="code">Branch's code.</param>
    /// <param name="description">Branch's description.</param>
    /// <param name="address">Branch's address.</param>
    /// <param name="latitude">Branch's latitude.</param>
    /// <param name="longitude">Branch's longitude.</param>
    /// <param name="phone">Branch's complete phone number.</param>
    /// <param name="website">Branch's website.</param>
    /// <param name="iANATimeZoneCode">The IANA time zone code corresponding to this branch.</param>
    /// <param name="regionId">Branch's region identifier.</param>
    /// <param name="createdBy">Branch's creator name.</param>
    /// <param name="lastSyncDate">Branch's optional sync date.</param>
    /// <param name="externalId">Branch's optional external identifier.</param>
    /// <exception cref="InvalidParameterException">If description is null or empty</exception>
    /// <exception cref="InvalidParameterException">If address is null or empty</exception>
    /// <returns>The branch created</returns>
    public Branch AddBranch(string code,
        string description,
        string address,
        decimal latitude,
        decimal longitude,
        string phone,
        string website,
        string iANATimeZoneCode,
        Guid regionId,
        string createdBy,
        DateTime? lastSyncDate = null,
        int? externalId = null)
    {
        // Execute.
        var newBranch = new Branch(code,
            description,
            address,
            latitude,
            longitude,
            phone,
            website,
            iANATimeZoneCode,
            this,
            regionId,
            createdBy,
            lastSyncDate,
            externalId);

        ValidateIfExistBranchWithSameDescription(newBranch);
        ValidateIfExistBranchWithSameCode(newBranch);

        Branches.Add(newBranch);

        return newBranch;
    }


    public void ValidateIfExistBranchWithSameDescription(Branch branch)
    {
        bool existBranchWithSameDescription = this.Branches.Any(bra => bra.HasSameDescriptionOf(branch));

        if (existBranchWithSameDescription)
        {
            throw new ValidationException(Errors.BranchDescriptionAlreadyExist);
        }
    }

    public void ValidateIfExistBranchWithSameCode(Branch branch)
    {
        bool existBranchWithSameCode = this.Branches.Any(bra => bra.HasSameCodeOf(branch));

        if (existBranchWithSameCode)
        {
            throw new ValidationException(Errors.BranchCodeAlreadyExistForThisCompany);
        }
    }

    public static Expression<Func<Company, bool>> IsComplete = company =>
            company.InstanceConfiguration != null
            && company.Category != null
            && company.Image != null
            && company.Branches
                .AsQueryable()
                .Count(Branch.IsComplete) > 0;



    public void AddCompanyDocument(Guid documentId, byte priority, string identifier)
    {
        var copmanyDocument = new CompanyDocument(Id, documentId, priority, identifier);

        if (CompanyDocuments.Any(cd => cd.DocumentId == documentId))
        {
            throw new ValidationException(Errors.CompanyAlreadyHasARelationshipWithThisDocument);
        }

        if (CompanyDocuments.Any(cd => cd.Priority == priority))
        {
            throw new ValidationException(Errors.CompanyAlreadyHasARelationshipWithSamePriority);
        }

        CompanyDocuments.Add(copmanyDocument);
    }

    /// <summary>
    /// Adds a range of rating configurations.
    /// </summary>
    /// <returns></returns>
    public void AddStarRatingRange(List<StarRating> ratingConfigurations) =>
        ratingConfigurations.ForEach(sr => AddStarRating(sr));

    /// <summary>
    /// Adds a rating configuration to this company.
    /// </summary>
    /// <returns></returns>
    public void AddStarRating(StarRating ratingConfiguration)
    {
        if (StarRatings.Any(rc => rc.StarValue == ratingConfiguration.StarValue))
        {
            throw new ValidationException(Errors.ThisCompanyAlreadyHasARatingConfigurationWithThisRatingSelection);
        }

        StarRatings.Add(ratingConfiguration);
    }


    #endregion    
}

StarRating

/// <summary>
/// Defines the rating star configuration for each rating reason.
/// </summary>
public class StarRating
{
    #region Fields

    private byte _starValue { get; set; }

    #endregion

    #region Constructor

    public StarRating()
    {
        RatingReasons = new List<RatingReason>();
    }

    public StarRating(Guid id, byte starValue, bool isReasonSelectionRequired, List<RatingReason> ratingReasons)
    {
        Id = id;
        StarValue = starValue;
        IsReasonSelectionRequired = isReasonSelectionRequired;

        RatingReasons = new List<RatingReason>();
        if (ratingReasons != null)
        {
            AddRatingReasonRange(ratingReasons);
        }
    }

    public StarRating(byte starValue, bool isReasonSelectionRequired, List<RatingReason> ratingReasons)
        : this(id: Guid.NewGuid(), starValue: starValue, isReasonSelectionRequired: isReasonSelectionRequired, ratingReasons: ratingReasons)
    {
    }

    #endregion

    #region Properties

    /// <summary>
    /// Identifier of the rating configuration.
    /// </summary>
    public Guid Id { get; set; }

    /// <summary>
    /// The rating selection at which this configuration is referred.
    /// The value must be between 1 and 5.
    /// </summary>
    public byte StarValue
    {
        get => _starValue;
        set
        {
            if (value < 1 || value > 5)
            {
                throw new ValidationException(Errors.RatingSelectionMustBeANumberBetween1And5);
            }

            _starValue = value;
        }
    }

    /// <summary>
    /// Indicates if the user must select a rating reason for this star selection.
    /// </summary>
    public bool IsReasonSelectionRequired { get; set; }

    /// <summary>
    /// The list of possible rating reasons.
    /// This list must be shown to the user in the user's current language.
    /// </summary>
    public List<RatingReason> RatingReasons { get; set; }

    #endregion

    #region Public methods

    public void AddRatingReasonRange(List<RatingReason> ratingReasons) => 
        ratingReasons.ForEach(rr => AddRatingReason(rr));

    public void AddRatingReason(RatingReason ratingReason)
    {
        if (RatingReasons.Any(rr => rr.Id == ratingReason?.Id))
        {
            throw new ValidationException(Errors.ThisRatingConfigurationAlreadyHasThisRatingReason);
        }

        if (RatingReasons.Any(rr => rr.Order == ratingReason.Order))
        {
            throw new ValidationException(Errors.ThisRatingConfigurationAlreadyHasARatingReasonConfigurationWithThisOrder);
        }

        RatingReasons.Add(ratingReason);
    }

    #endregion
}

[Dongata]

To add up, de change tracker status is an update, and i forgot to add the exception that it throws

Unhandled exception. System.AggregateException: One or more errors occurred. (Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.)
 ---> Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ThrowAggregateUpdateConcurrencyException(Int32 commandIndex, Int32 expectedRowsAffected, Int32 rowsAffected)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetWithoutPropagationAsync(Int32 commandIndex, RelationalDataReader reader, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at System.Threading.Tasks.Task.Wait()
   at eflow.transact.api.Data.RatingConfigurationSeeder.Seed(IServiceProvider provider) in C:\repos\eflow-transact\src\eflow.transact.api\Data\RatingConfigurationSeeder.cs:line 70
   at eflow.transact.api.Program.ProcessArgs(String[] args, IHost host) in C:\repos\eflow-transact\src\eflow.transact.api\Program.cs:line 52
   at eflow.transact.api.Program.Main(String[] args) in C:\repos\eflow-transact\src\eflow.transact.api\Program.cs:line 16

[ajcvickers]

@Dongata By default, GUID keys are configured to be auto-generated. This means that any entity already saved to the database will have a non-default GUIDkey value, while entities that have not yet been inserted will have a default GUID value. EF therefore uses the key value to determine whether to insert or update any newly discovered entities.

It looks like in this case you are actually always generating the GUID value, in which case configuring as not-generated in the model will stop EF from assuming that a non-default GUID key represents a new entity. For example:

C# modelBuilder.Entity<StarRating>().Property(e => e.Id).ValueGeneratedNever();

[Dongata]

Thank you @ajcvickers I'll try that

[Dongata]

It worked, the weird thing is that, the others models doesn't have any configuration on value generation, with a identical configuration, and any of this have happened, we move recently from 3.1.2 to 3.1.9, does this had any impact on defaults?

Thank you again. I'm closing the issue