Need help with UpdateAsync and Child object #5825

mmukkara created 5 years ago

Here is the scenario...

Entities

public partial class **BenefitEnrolmentRule** : FullAuditedEntity, IMustHaveTenant
{
    public BenefitEnrolmentRule()
    {
        DependentEnrolmentRules = new HashSet&lt;DependentEnrolmentRule&gt;();
    }

    [Key]
    [Column(Order = 1)]
    public int TenantId { get; set; }

    [Key]
    [Column("BenefitEnrolmentRuleId", Order = 2)]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public override int Id { get; set; }

    public bool? TrapLate { get; set; }

    [Required]
    [Column(TypeName = "char(3)")]
    public string GracePeriod { get; set; }

    public byte[] RecordVersion { get; set; }

    [InverseProperty("BenefitEnrolmentRule")]
    public ICollection&lt;DependentEnrolmentRule&gt; DependentEnrolmentRules { get; set; }

}

}

public partial class **DependentEnrolmentRule** : Entity&lt;short&gt;, IMustHaveTenant
{
    [Key]
    [Column(Order = 1)]
    public int TenantId { get; set; }

    [Key]
    [Column("BenefitEnrolmentRuleId", Order = 2)]
    public int BenefitEnrolmentRuleId { get; set; }

    [Key]
    [Column("Dependent", Order = 3)]
    public override short Id { get; set; }

    [Column(TypeName = "char(3)")]
    public string WaitingPeriod { get; set; }
    public short StartDateCalc { get; set; }
    public int TerminationAge { get; set; }
    public short TerminationDateCalc { get; set; }

    [ForeignKey("TenantId,BenefitEnrolmentRuleId")]
    [InverseProperty("DependentEnrolmentRules")]
    public BenefitEnrolmentRule BenefitEnrolmentRule { get; set; }
}

}

Dto....

  public class **BenefitEnrolmentRuleDto**
 {
    BenefitEnrolmentRuleDto()
    {
    }

    public int TenantId { get; set; }
    public int Id { get; set; }

    public int BenefitOptionId { get; set; }

    public bool? TrapLate { get; set; }

    [Required]
    public string GracePeriod { get; set; }

    public List&lt;DependentEnrolmentRuleCreateInputDto&gt; DependentEnrolmentRules { get; set; }
}

public class **DependentEnrolmentRuleCreateInputDto**
{

    DependentEnrolmentRuleCreateInputDto()
    {
    }

    public int TenantId { get; set; }
    public int BenefitEnrolmentRuleId { get; set; }

    public short Dependent { get; set; }
    public string WaitingPeriod { get; set; }
    public short StartDateCalc { get; set; }
    public int TerminationAge { get; set; }
    public short TerminationDateCalc { get; set; }

    public bool isEligible { get; set; }
}

Update Method

public async Task UpdateBenefitEnrolmentRule(BenefitEnrolmentRuleDto input)
    {

        var benefitEnrolmentRule = await _benefitEnrolmentRepository.GetAll()
                    .Include(e => e.DependentEnrolmentRules)
                    .Where(e => e.Id == input.Id)
                    .SingleOrDefaultAsync();

        ObjectMapper.Map(input, benefitEnrolmentRule);
        await _benefitEnrolmentRepository.UpdateAsync(benefitEnrolmentRule);
    }

Data from Angualar client (Dto) BenefitEnrolmentRules={
"tenantId":2, "id":32, "benefitOptionId":0, "trapLate":true, "gracePeriod":"04D", "dependentEnrolmentRules":[
{
"tenantId":2, "benefitEnrolmentRuleId":32, "dependent":1, "waitingPeriod":"02D", "startDateCalc":1, "terminationAge":3, "terminationDateCalc":1, "isEligible":true } ] }

After this statement in the update method var benefitEnrolmentRule = await _benefitEnrolmentRepository.GetAll() .Include(e => e.DependentEnrolmentRules) .Where(e => e.Id == input.Id) .SingleOrDefaultAsync();

benefitEnrolmentRule data

After this statement in the update method ObjectMapper.Map(input, benefitEnrolmentRule);

After last statement in update method await _benefitEnrolmentRepository.UpdateAsync(benefitEnrolmentRule);

Checking the profiler,

SQL profiler Statements exec sp_executesql N'SET NOCOUNT ON; UPDATE [BenefitEnrolmentRule] SET [CreationTime] = @p0, [CreatorUserId] = @p1, [DeleterUserId] = @p2, [DeletionTime] = @p3, [GracePeriod] = @p4, [IsDeleted] = @p5, [LastModificationTime] = @p6, [LastModifierUserId] = @p7, [TrapLate] = @p8 WHERE [TenantId] = @p9 AND [BenefitEnrolmentRuleId] = @p10 AND [RecordVersion] = @p11; SELECT [RecordVersion] FROM [BenefitEnrolmentRule] WHERE @@ROWCOUNT = 1 AND [TenantId] = @p9 AND [BenefitEnrolmentRuleId] = @p10; ',N'@p9 int,@p10 int,@p0 datetime2(7),@p1 bigint,@p2 bigint,@p3 datetime2(7),@p4 varchar(3),@p5 bit,@p6 datetime2(7),@p7 bigint,@p11 varbinary(8),@p8 bit',@p9=2,@p10=32,@p0='2018-10-23 15:49:20.5956672',@p1=3,@p2=NULL,@p3=NULL,@p4='04D',@p5=0,@p6='2018-10-23 16:52:05.2990144',@p7=3,@p11=0x000000000000568D,@p8=1

exec sp_executesql N'SET NOCOUNT ON; UPDATE [DependentEnrolmentRule] SET [TerminationAge] = @p0, [WaitingPeriod] = @p1 WHERE [TenantId] = @p2 AND [BenefitEnrolmentRuleId] = @p3 AND [Dependent] = @p4; SELECT @@ROWCOUNT; ',N'@p2 int,@p3 int,@p4 smallint,@p0 int,@p1 varchar(3)',@p2=2,@p3=32,@p4=1,@p0=3,@p1='02D'

exec sp_executesql N'SET NOCOUNT ON; **DELETE FROM [DependentEnrolmentRule] WHERE [TenantId] = @p0 AND [BenefitEnrolmentRuleId] = @p1 AND [Dependent] = @p2; SELECT @@ROWCOUNT; ',N'@p0 int,@p1 int,@p2 smallint',@p0=2,@p1=32,@p2=1 ** Problem: In UpdateAsync method, it is updating the DependentEnrolmentRule and next step is, it is deleting the same record (see sql profile statements). Not sure why.

One reason I was thinking is, In GetAll, BenefitEnrolmentRule object in the DependentEnrolmentRule (which is associated by FK) has data and after mapping with input object,BenefitEnrolmentRule in the DependentEnrolmentRule is null.

How to avoid getting BenefitEnrolmentRule in the DependentEnrolmentRule (as this is already a parent object, why do I need it again for child), why it is retrieved in child object too.
How can I fix this issue without writing ForEach loop for DependentEnrolmentRule and update.
Am I doing something wrong.

Please let me know how to fix this issue. I have posted all the code here. Really appreciate your help.

Thanks

5 Answer(s)

0
aaron created 5 years ago
Support Team
How to avoid getting BenefitEnrolmentRule in the DependentEnrolmentRule (as this is already a parent object, why do I need it again for child), why it is retrieved in child object too.

From https://docs.microsoft.com/en-us/ef/core/querying/related-data#eager-loading:

Entity Framework Core will automatically fix-up navigation properties to any other entities that were previously loaded into the context instance. So even if you don't explicitly include the data for a navigation property, the property may still be populated if some or all of the related entities were previously loaded.

Related: https://github.com/aspnet/EntityFrameworkCore/issues/11564

How can I fix this issue without writing ForEach loop for DependentEnrolmentRule and update.

Try these:

i. Don't query BenefitEnrolmentRule unnecessarily:

public async Task UpdateBenefitEnrolmentRule(BenefitEnrolmentRuleDto input) { var benefitEnrolmentRule = ObjectMapper.Map<BenefitEnrolmentRule>(input); await _benefitEnrolmentRepository.UpdateAsync(benefitEnrolmentRule); }

ii. Don't include DependentEnrolmentRules unnecessarily:

public async Task UpdateBenefitEnrolmentRule(BenefitEnrolmentRuleDto input) { var benefitEnrolmentRule = await _benefitEnrolmentRepository.GetAll() .Where(e => e.Id == input.Id) .SingleOrDefaultAsync(); ObjectMapper.Map(input, benefitEnrolmentRule); await _benefitEnrolmentRepository.UpdateAsync(benefitEnrolmentRule); }

iii. Specify .AsNoTracking():

public async Task UpdateBenefitEnrolmentRule(BenefitEnrolmentRuleDto input) { var benefitEnrolmentRule = await _benefitEnrolmentRepository.GetAll() .Include(e => e.DependentEnrolmentRules) .Where(e => e.Id == input.Id) .AsNoTracking() .SingleOrDefaultAsync(); ObjectMapper.Map(input, benefitEnrolmentRule); await _benefitEnrolmentRepository.UpdateAsync(benefitEnrolmentRule); }

Am I doing something wrong.

For starters:

Always specify whether you are using ASP.NET Core (EF Core) or MVC 5 (EF6).

Format code properly.

mmukkara created 5 years ago

Hi Aaron,

Thanks for the quick response. **For the first suggestion I get following error **

_Abp.Domain.Uow.AbpDbConcurrencyException: 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.

In SQL profile, I see only update for BenefitEnrolmentRule. no update statements for DependentEnrolmentRules .

For the second suggestion, I get the following error

An error occurred while updating the entries. See the inner exception for details.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_DEPENDENTENROLMENTRULE'. Cannot insert duplicate key in object 'dbo.DependentEnrolmentRule'. The duplicate key value is (2, 32, 1).

In the SQL profiler... I see Update for BenefitEnrolmentRule and insert for DependentEnrolmentRules instead of update for DependentEnrolmentRules.

For the third suggestion, no error. But In SQL profile, I see only update for BenefitEnrolmentRule. no update statements for DependentEnrolmentRules .

Thanks

0

aaron created 5 years ago
Support Team

Specify Id instead of Dependent in your DTO.
0

mmukkara created 5 years ago

Hi aaron,

I have custom mapping for id and dependent

configuration.CreateMap<DependentEnrolmentRuleCreateInputDto, DependentEnrolmentRule>() .ForMember(d => d.Id, options => options.MapFrom(s => s.Dependent)) .ForMember(u => u.BenefitEnrolmentRule, options => options.Ignore());

[edited]
0

ismcagdas created 5 years ago
Support Team

Hi @mmukkara

Have you solved the problem ?

Have an answer to this question? Log in and write your answer.