EF Core 7: "The database operation was expected to affect 1 row(s), but actually affected 0 row(s); "

Question

I've read quite a few posts that refer to this exception, but the use cases differ from mine

I have a new entity (Bonus) that I am trying to save. It has two child collections. If I save it WITHOUT the two child collections (Currencies and Tiers), an INSERT statement is generated and the entity is saved correctly.

However, when I add the two collections, I get the above exception and the SQL that is generated is actually trying to UPDATE the tables corresponding to those collections when it should be generating an INSERT.

Here are the queries generate with and without the child collections:

Without child collections:

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Bonus] ([Allocation], [BonusExpiryTerm], [BonusFreeSpinsConvention], [BonusTermsUrl], [BonusValueType], [CasinoId], [DateAdded], [DateEnd], [DateStart], [DateUpdated], [EligibilityDescription], [FreeBetConvention], [Guid], [IsActive], [IsBonusPagePresence], [IsBonusTermsLink], [IsClaimInstructions], [IsExclusive], [IsHighRollerBonus], [IsHomePagePresence], [IsNew], [IsPrimary], [IsPromotionPagePresence], [IsSecondChanceBonus], [IsSpecial], [IsSplitBonusAccountInCashier], [LongDescription], [PrimaryBonusType], [QualityAndEaseOfUse], [SecondaryBonusType], [ShortDescription], [SportsbookBonusTrigger], [SportsbookExcludedBettingTypesAndMarkets], [SportsbookExcludedPaymentMethods], [SportsbookMinimumOddsAllowed], [SportsbookPermittedBettingTypesAndMarkets], [SportsbookPermittedPaymentMethods], [WageringRequirementsDescription], [WageringRequirementsExcludedGamesCsv], [WageringRequirementsIsTrackingReporting], [WageringRequirementsPlayThrough], [WageringRequirementsRequirementsUrl], [WageringRequirementsSignificantTerms], [WageringRequirementsTermsCopy], [WageringRequirementsVipWageringMultiple], [WageringRequirementsVipWageringValue], [WithdrawalRestrictionsAdditionalPenaltyNotes], [WithdrawalRestrictionsAllowLowRiskWagering], [WithdrawalRestrictionsAllowOversizedBets], [WithdrawalRestrictionsBonusAbuseNotes], [WithdrawalRestrictionsDescription], [WithdrawalRestrictionsDisputeResolutionProcess], [WithdrawalRestrictionsDisputeResolutionProcessDescription], [WithdrawalRestrictionsEarlyWithdrawalPenalty], [WithdrawalRestrictionsGamePenalty], [WithdrawalRestrictionsGroupCrossoverPenalty], [WithdrawalRestrictionsGroupCrossoverRestriction], [WithdrawalRestrictionsManagerOverrideClause], [WithdrawalRestrictionsPlayingPatternReview], [WithdrawalRestrictionsReversalOption], [WithdrawalRestrictionsSingleBetLimit], [WithdrawalRestrictionsSlotsDoubleUp], [WithdrawalRestrictionsType], [WithdrawalRestrictionsVideoPokerDoubleUp])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47,@p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63);
SELECT [Id]
FROM [Bonus]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

',N'@p0 int,@p1 int,@p2 int,@p3 nvarchar(4000),@p4 int,@p5 int,@p6 datetime2(7),@p7 datetime2(7),@p8 datetime2(7),@p9 datetime2(7),@p10 nvarchar(4000),@p11 int,@p12 uniqueidentifier,@p13 bit,@p14 bit,@p15 bit,@p16 bit,@p17 bit,@p18 bit,@p19 bit,@p20 bit,@p21 bit,@p22 bit,@p23 bit,@p24 bit,@p25 bit,@p26 nvarchar(4000),@p27 int,@p28 int,@p29 int,@p30 nvarchar(4000),@p31 int,@p32 nvarchar(4000),@p33 nvarchar(4000),@p34 nvarchar(4000),@p35 nvarchar(4000),@p36 nvarchar(4000),@p37 nvarchar(4000),@p38 nvarchar(4000),@p39 bit,@p40 int,@p41 nvarchar(4000),@p42 nvarchar(4000),@p43 nvarchar(4000),@p44 int,@p45 int,@p46 nvarchar(4000),@p47 int,@p48 int,@p49 nvarchar(4000),@p50 nvarchar(4000),@p51 int,@p52 nvarchar(4000),@p53 int,@p54 int,@p55 int,@p56 int,@p57 int,@p58 int,@p59 int,@p60 int,@p61 int,@p62 int,@p63 int',@p0=0,@p1=NULL,@p2=0,@p3=NULL,@p4=0,@p5=11,@p6='2023-08-15 10:32:27.5650372',@p7=NULL,@p8=NULL,@p9=NULL,@p10=NULL,@p11=NULL,@p12='18EC1B53-FE52-4B6F-BF60-8FB2D0723F79',@p13=0,@p14=0,@p15=0,@p16=0,@p17=0,@p18=0,@p19=0,@p20=0,@p21=0,@p22=0,@p23=0,@p24=0,@p25=0,@p26=NULL,@p27=1,@p28=0,@p29=0,@p30=NULL,@p31=NULL,@p32=NULL,@p33=NULL,@p34=NULL,@p35=NULL,@p36=NULL,@p37=NULL,@p38=NULL,@p39=0,@p40=0,@p41=NULL,@p42=NULL,@p43=NULL,@p44=NULL,@p45=NULL,@p46=NULL,@p47=NULL,@p48=NULL,@p49=NULL,@p50=NULL,@p51=NULL,@p52=NULL,@p53=NULL,@p54=NULL,@p55=NULL,@p56=NULL,@p57=NULL,@p58=NULL,@p59=NULL,@p60=NULL,@p61=NULL,@p62=NULL,@p63=NULL

With child collections:

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Bonus] ([Allocation], [BonusExpiryTerm], [BonusFreeSpinsConvention], [BonusTermsUrl], [BonusValueType], [CasinoId], [DateAdded], [DateEnd], [DateStart], [DateUpdated], [EligibilityDescription], [FreeBetConvention], [Guid], [IsActive], [IsBonusPagePresence], [IsBonusTermsLink], [IsClaimInstructions], [IsExclusive], [IsHighRollerBonus], [IsHomePagePresence], [IsNew], [IsPrimary], [IsPromotionPagePresence], [IsSecondChanceBonus], [IsSpecial], [IsSplitBonusAccountInCashier], [LongDescription], [PrimaryBonusType], [QualityAndEaseOfUse], [SecondaryBonusType], [ShortDescription], [SportsbookBonusTrigger], [SportsbookExcludedBettingTypesAndMarkets], [SportsbookExcludedPaymentMethods], [SportsbookMinimumOddsAllowed], [SportsbookPermittedBettingTypesAndMarkets], [SportsbookPermittedPaymentMethods], [WageringRequirementsDescription], [WageringRequirementsExcludedGamesCsv], [WageringRequirementsIsTrackingReporting], [WageringRequirementsPlayThrough], [WageringRequirementsRequirementsUrl], [WageringRequirementsSignificantTerms], [WageringRequirementsTermsCopy], [WageringRequirementsVipWageringMultiple], [WageringRequirementsVipWageringValue], [WithdrawalRestrictionsAdditionalPenaltyNotes], [WithdrawalRestrictionsAllowLowRiskWagering], [WithdrawalRestrictionsAllowOversizedBets], [WithdrawalRestrictionsBonusAbuseNotes], [WithdrawalRestrictionsDescription], [WithdrawalRestrictionsDisputeResolutionProcess], [WithdrawalRestrictionsDisputeResolutionProcessDescription], [WithdrawalRestrictionsEarlyWithdrawalPenalty], [WithdrawalRestrictionsGamePenalty], [WithdrawalRestrictionsGroupCrossoverPenalty], [WithdrawalRestrictionsGroupCrossoverRestriction], [WithdrawalRestrictionsManagerOverrideClause], [WithdrawalRestrictionsPlayingPatternReview], [WithdrawalRestrictionsReversalOption], [WithdrawalRestrictionsSingleBetLimit], [WithdrawalRestrictionsSlotsDoubleUp], [WithdrawalRestrictionsType], [WithdrawalRestrictionsVideoPokerDoubleUp])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47,@p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63);
SELECT [Id]
FROM [Bonus]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

',N'@p0 int,@p1 int,@p2 int,@p3 nvarchar(4000),@p4 int,@p5 int,@p6 datetime2(7),@p7 datetime2(7),@p8 datetime2(7),@p9 datetime2(7),@p10 nvarchar(4000),@p11 int,@p12 uniqueidentifier,@p13 bit,@p14 bit,@p15 bit,@p16 bit,@p17 bit,@p18 bit,@p19 bit,@p20 bit,@p21 bit,@p22 bit,@p23 bit,@p24 bit,@p25 bit,@p26 nvarchar(4000),@p27 int,@p28 int,@p29 int,@p30 nvarchar(4000),@p31 int,@p32 nvarchar(4000),@p33 nvarchar(4000),@p34 nvarchar(4000),@p35 nvarchar(4000),@p36 nvarchar(4000),@p37 nvarchar(4000),@p38 nvarchar(4000),@p39 bit,@p40 int,@p41 nvarchar(4000),@p42 nvarchar(4000),@p43 nvarchar(4000),@p44 int,@p45 int,@p46 nvarchar(4000),@p47 int,@p48 int,@p49 nvarchar(4000),@p50 nvarchar(4000),@p51 int,@p52 nvarchar(4000),@p53 int,@p54 int,@p55 int,@p56 int,@p57 int,@p58 int,@p59 int,@p60 int,@p61 int,@p62 int,@p63 int',@p0=0,@p1=NULL,@p2=0,@p3=NULL,@p4=0,@p5=11,@p6='2023-08-15 10:32:27.5650372',@p7=NULL,@p8=NULL,@p9=NULL,@p10=NULL,@p11=NULL,@p12='18EC1B53-FE52-4B6F-BF60-8FB2D0723F79',@p13=0,@p14=0,@p15=0,@p16=0,@p17=0,@p18=0,@p19=0,@p20=0,@p21=0,@p22=0,@p23=0,@p24=0,@p25=0,@p26=NULL,@p27=1,@p28=0,@p29=0,@p30=NULL,@p31=NULL,@p32=NULL,@p33=NULL,@p34=NULL,@p35=NULL,@p36=NULL,@p37=NULL,@p38=NULL,@p39=0,@p40=0,@p41=NULL,@p42=NULL,@p43=NULL,@p44=NULL,@p45=NULL,@p46=NULL,@p47=NULL,@p48=NULL,@p49=NULL,@p50=NULL,@p51=NULL,@p52=NULL,@p53=NULL,@p54=NULL,@p55=NULL,@p56=NULL,@p57=NULL,@p58=NULL,@p59=NULL,@p60=NULL,@p61=NULL,@p62=NULL,@p63=NULL
go
exec sp_executesql N'SET NOCOUNT ON;
UPDATE [BonusCasinoCurrency] SET [BonusId] = @p64
OUTPUT 1
WHERE [Id] = @p65;
UPDATE [BonusMatchTier] SET [BonusId] = @p66
OUTPUT 1
WHERE [Id] = @p67;
',N'@p65 int,@p64 int,@p67 int,@p66 int',@p65=0,@p64=9435,@p67=0,@p66=9435
go

In this case, @p66=9435 is the correct Id for the next Bonus. I assume the "actually affected 0 row(s);" refers to the UPDATE because it updates no rows

The second query block appears to be rolled back and does not insert the bonus.

Here is the EF code: The BonusService and CasinoService are where the EF context is accessible.

[Test]
public void Create()
{
    BonusMatch bonusMatch = new BonusMatch();

    using (BonusService bonusService = new BonusService())
    using (CasinoService casinoService = new CasinoService())
    {
        Casino casino = casinoService.Find(11, true);
        bonusMatch.Casino = casino;
        bonusMatch.Tiers.Add(new BonusMatchTier(0, 1, 1, 1, 1, "test", "test"));
        bonusMatch.BonusCasinoCurrencies.Add(new BonusCasinoCurrency(casino.AvailableCurrencies.First(), true));

        bonusService.Add(bonusMatch);
    }
    // Assert.Pass();
}

You'll notice the AutoMapper Map code, This works fine. This is inside BonusService

public Business.Bonus Add(Business.Bonus dtoBonus)
{
    Domain.Bonus domainBonus = _context.Map<Domain.Bonus>(dtoBonus);

    _context.Add(domainBonus);
    _context.SaveChanges();

    dtoBonus.SetId(domainBonus.Id);

    return dtoBonus;
}

EF Model config:

    public class CasinoConfiguration : IEntityTypeConfiguration<Casino>
    {
        public void Configure(EntityTypeBuilder<Casino> modelBuilder)
        {
            modelBuilder.ToTable(tb => tb.HasTrigger("TR_upd_Casino"));

            modelBuilder.HasMany(c => c.Bonuses)
                .WithOne(b => b.Casino)
                .OnDelete(DeleteBehavior.Cascade);
        }
    }

    public class BonusConfiguration : IEntityTypeConfiguration<Bonus>
    {
        public void Configure(EntityTypeBuilder<Bonus> modelBuilder)
        {


            #region Bonus Mapping

            modelBuilder.HasDiscriminator(d => d.SecondaryBonusType)
                .HasValue(typeof(BonusMatch), BonusTypes.Match)
                .HasValue(typeof(BonusNoDeposit), BonusTypes.NoDeposit)
                .HasValue(typeof(BonusFreeplay), BonusTypes.Freeplay)
                .HasValue(typeof(BonusPostWager), BonusTypes.PostWager)
                .HasValue(typeof(BonusCashback), BonusTypes.Cashback)
            ;

            modelBuilder
                .HasOne(b => b.Casino)
                .WithMany(c => c.Bonuses)
                .OnDelete(DeleteBehavior.Cascade);

             modelBuilder.HasMany(c => c.BonusCasinoCurrencies)
                .WithOne(c => c.Bonus)
                .OnDelete(DeleteBehavior.Cascade);
        }
    }

In the main model config class

            modelBuilder.Entity<BonusMatch>()
                 .OwnsMany(b => b.Tiers,
                     t =>
                     {
                         t.ToTable("BonusMatchTier")
                         .WithOwner(b=> b.Bonus)
                         .HasForeignKey("BonusId");
                     }

                 )
             .Ignore(b => b.HasTiers);

My understanding is that EF would know it must INSERT those child collections because they belong to a new entity that, itself, is correctly being INSERTED.

So why does EF think that these two collections exist and should be UPDATED instead of being implicitly new because they exist on a new entity?

UPDATE As requested, here are the class definitions

    public abstract class Bonus : EntityBase
    {
        private IList<BonusCasinoCurrency> _bonusCasinoCurrencies;
        public virtual IList<BonusCasinoCurrency> BonusCasinoCurrencies
        {
            get { return _bonusCasinoCurrencies; }
            set { _bonusCasinoCurrencies = value; }
        }
        public Bonus() : base()
        {
            PrimaryBonusType = PrimaryBonusType.SignUp;
        }



    }



    public class BonusMatch : Bonus
    {
        private List<BonusMatchTier> _tiers;
        public virtual List<BonusMatchTier> Tiers
        {
            get { return _tiers; }
            protected set { _tiers = value; }
        }
        public BonusMatch() : base ()
        {
            _tiers = new List<BonusMatchTier>();
        }

    }
    public class BonusMatchTier 
    {
        public virtual string BonusCode { get; protected set; }
        public virtual decimal MinDepositAmount { get; protected set; }
        public int? FreeSpinsWagering { get; protected set; }
        protected BonusMatchTier() { }

    }

A class model + mappings would help. It's not even clear why the code would generate an `INSERT INTO [Bonus]` statement. Also, we should not have to guess what these services do, esp. whether they share one context instance. Same for the constructors. — Gert Arnold, Aug 15 '23 at 19:18

Adam Hey · Answer 1 · 2023-08-17T13:45:41.070

Well, I got it to work but I'm not convinced it's the best way to do it - shouldn't EF natively handle the insertion of a new object graph and know how to generate the appropriate SQL for the entire graph? Adding a new object graph with child collections should generate inserts for all entities involved.

Without the two foreach loops in my service, which set the child entities' states to Added, it seems EF doesn't know they are new and should be inserted rather than updated.

What worked for me was updates in two places - the EF model and in my DataService.

Model change. Note the Tiers are commented out. in my opinion, BonusMatchTiers are owned by a Bonus because they cannot exist unless as children of a Bonus

modelBuilder.Entity<BonusMatch>()
//      .OwnsMany(b => b.Tiers,
//          t =>
//          {
//              t.ToTable("BonusMatchTier")
//              .WithOwner(b => b.Bonus)
//              .HasForeignKey("BonusId");
//          }
//      )
    .Ignore(b => b.HasTiers);

// ADDED THIS
modelBuilder.Entity<BonusMatchTier>()
    .ToTable("BonusMatchTier")
    .HasOne(bcc => bcc.Bonus)
    .WithMany(cc => cc.Tiers)
    .HasForeignKey("BonusId")
    .HasConstraintName("FK_BonusMatchTier_BonusMatch")
    .OnDelete(DeleteBehavior.Cascade);

Then, in the BonusService I added the following to inform EF that the child collections were added. I really don't believe this should be necessary. The Bonus has many other collections that must be added. (Not to mention the other entities in my model with many child collections!)

public Business.Bonus Add(Business.Bonus dtoBonus)
{
    Domain.Bonus domainBonus = _context.Map<Domain.Bonus>(dtoBonus);
    _context.Entry(domainBonus).State = EntityState.Added;

    foreach (Domain.BonusCasinoCurrency bcc in domainBonus.BonusCasinoCurrencies)
    {
        _context.BonusCasinoCurrencies.Attach(bcc);
        _context.Entry(bcc).State = EntityState.Added;
    }

    foreach (Domain.BonusMatchTier bmt in ((Domain.BonusMatch)domainBonus).Tiers)
    {
        _context.BonusMatchTiers.Attach(bmt);
        _context.Entry(bmt).State = EntityState.Added;
    }


    _context.Add(domainBonus);
    _context.SaveChanges();
    dtoBonus.SetId(domainBonus.Id);
    return dtoBonus;
}

So this does work and the correct INSERT statements are generated, but there has to be* a better way

EF Core 7: "The database operation was expected to affect 1 row(s), but actually affected 0 row(s); "

1 Answers1