Efcore: Adding New related entity tries to insert primary key value causing exception

[SimonOrdo]

I'm create a few related entities and attempting to save them. The entities are new, but for some reason EF is attempting to insert the primary key column causing a duplicate primary key exception (attempting to insert an entity with Key '0', which already exists in the DB.

Offending code:

     // grab discount code from DB
     var lecode = _DBContext
                             .DiscountCodes
                             .Include(x => x.DiscountCodesPortalMap)
                             .Include(x => x.DicountCodesPackageMap)
                             .Where(x => x.CodeText == "testy")
                             .SingleOrDefault();

                 // Create order record and add to context
                PaymentsOrders o = new IaymentsOrders();
                _DBContext.Add(o);

                // Get customer object
                var lebuyer = await _AppUserManager.GetUserAsync(User);

                // Make some changes to the order record
                o.TimeStamp = DateTime.UtcNow;
                o.TimeStampCst = DateTime.UtcNow.ToCSTTime();
                o.UserId = lebuyer.Id;
                o.PortalId = WebUtil.GetCurrentPortal(HttpContext).ID;
                o.MerchantAccountId = WebUtil.GetCurrentPortal(HttpContext).MerchantAccountId.Value;
                o.ResultCode = 100;

                 // Create order item records for everything in the shopping cart
                foreach (var leitem in ShoppingCart.Get(HttpContext).CartItems)
                {
                    PaymentsOrderItem oitem = new PaymentsOrderItem();                    
                    oitem.ItemId = leitem.PackageId;
                    oitem.ItemType = PaymentOrderType.PortalPurchase;
                    oitem.Quantity = leitem.QuantityInCart;
                    oitem.UnitPrice = (decimal)leitem.UnitPrice;
                    oitem.Order = o;

                    // add the item to the order
                    _DBContext.Add(oitem);               

                   // if user has a coupon code, create a coupon code use record and add it to the context
                    if (lecode != null)
                    {
                        // we have a code, so we need to add a code use record... one code use for each
                       // item in the cart Qty
                        for (int i = 0; i < leitem.QuantityInCart; i++)
                        {
                            DiscountCodesUses useRecord = new DiscountCodesUses()
                            {
                                ConsumedOn = DateTime.UtcNow,
                                ConsumerId = lebuyer.Id,
                                DiscountCodeId = lecode.Id,
                                OrderItem = oitem // set order item reference
                            };
                             // Add it to the context
                            _DBContext.DiscountCodesUses.Add(useRecord);
                        }
                    }
                }

                // Save change causes error
                _DBContext.SaveChanges();

Order Item Entity:

    public partial class PaymentsOrderItem
    {
        public PaymentsOrderItem()
        {
            DiscountCodes = new HashSet<DiscountCodes>();
            DiscountCodesUses = new HashSet<DiscountCodesUses>();
        }

        public long Id { get; set; }
        public long ItemId { get; set; }
        public long OrderId { get; set; }
        public decimal UnitPrice { get; set; }
        public PaymentOrderType ItemType { get; set; }
        public int Quantity { get; set; }

        public virtual ICollection<DiscountCodes>DiscountCodes { get; set; }
        public virtual ICollection<DiscountCodesUses>DiscountCodesUses { get; set; }
        public virtual Package_Config Item { get; set; }
        public virtual PaymentsOrders Order { get; set; }
    }
}

        modelBuilder.Entity<PaymentsOrderItem>(entity =>
            {
                entity.ToTable("Payments_OrderItem");

                entity.HasIndex(e => e.ItemType)
                    .HasName("IX_Payments_OrderItem");

                entity.HasIndex(e => e.OrderId)
                    .HasName("IX_Payments_OrderItem_1");

                entity.Property(e => e.Id).HasColumnName("ID");

                entity.Property(e => e.UnitPrice).HasColumnType("decimal");

                entity.HasOne(d => d.Item)
                    .WithMany(p => p.PaymentsOrderItem)
                    .HasForeignKey(d => d.ItemId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_Payments_OrderItem_Package_Config");

                entity.HasOne(d => d.Order)
                    .WithMany(p => p.PaymentsOrderItem)
                    .HasForeignKey(d => d.OrderId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_Payments_OrderItem_Payments_Orders");
            });

Code use entity:

 public partial class DiscountCodesUses
    {
        public long Id { get; set; }
        public long DiscountCodeId { get; set; }
        public string ConsumerId { get; set; }
        public DateTime ConsumedOn { get; set; }
        public long OrderItemId { get; set; }

        public virtual ApplicationUser Consumer { get; set; }
        public virtual DiscountCodes DiscountCode { get; set; }
        public virtual PaymentsOrderItem OrderItem { get; set; }
    }

 modelBuilder.Entity<DiscountCodesUses>(entity =>
            {
                entity.ToTable("DiscountCodes_Uses");

                entity.Property(e => e.Id)
                    .HasColumnName("ID")
                    .ValueGeneratedNever();

                entity.Property(e => e.ConsumedOn).HasColumnType("datetime");

                entity.Property(e => e.ConsumerId)
                    .IsRequired()
                    .HasMaxLength(450);

                entity.HasOne(d => d.Consumer)
                    .WithMany(p => p.DiscountCodesUses)
                    .HasForeignKey(d => d.ConsumerId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FKDiscountCodes_Uses_AspNetUsers");

                entity.HasOne(d => d.DiscountCode)
                    .WithMany(p => p.DiscountCodesUses)
                    .HasForeignKey(d => d.DiscountCodeId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_DiscountCodes_Uses_DiscountCodes");

                entity.HasOne(d => d.OrderItem)
                    .WithMany(p => p.DiscountCodesUses)
                    .HasForeignKey(d => d.OrderItemId)
                    .OnDelete(DeleteBehavior.Restrict)
                    .HasConstraintName("FK_DiscountCodes_Uses_Payments_Orders");
            });

The error I get is `{"Violation of PRIMARY KEY constraint 'PK_DiscountCodes_Uses'. Cannot insert duplicate key in object 'dbo_DiscountCodes_Uses'. The duplicate key value is (0).\r\nThe statement has been terminated."}'

The generate SQL is as follows:

PortalWeb.exe Information: 0 : 2017-05-17 11:48:55.946 -05:00 [Information] Executed DbCommand (36ms) [Parameters=[@p0='?' (Size = 450), @p1='?' (Size = 450), @p2='?', @p3='?' (Size = 450), @p4='?', @p5='?' (Size = 450), @p6='?', @p7='?', @p8='?', @p9='?' (Size = 450), @p10='?' (Size = 4000), @p11='?', @p12='?', @p13='?', @p14='?', @p15='?', @p16='?', @p17='?', @p18='?', @p19='?', @p20='?' (Size = 450), @p21='?' (Size = 450)], CommandType='Text', CommandTimeout='120']
SET NOCOUNT ON;
INSERT INTO [Payments_Orders] ([AuthorizationNumber], [AuthorizedBy], [BillingInfoId], [CaptureReconcileId], [CreditCardId], [InternalAuthorizationNumber], [LegacyFailAuthId], [LegacyOrderId], [MerchantAccountId], [MerchantOrderID], [Message], [PortalId], [ResultCode], [SettlementOrderId], [Shipping], [SubTotal], [Tax], [TimeStamp], [TimeStampCST], [Total], [TransactionNumber], [UserId])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21);
SELECT [ID]
FROM [Payments_Orders]
WHERE @@ROWCOUNT = 1 AND [ID] = scope_identity();
PortalWeb.exe Information: 0 : 2017-05-17 11:48:55.989 -05:00 [Information] Executed DbCommand (40ms) [Parameters=[@p22='?', @p23='?', @p24='?', @p25='?', @p26='?'], CommandType='Text', CommandTimeout='120']
SET NOCOUNT ON;
INSERT INTO [Payments_OrderItem] ([ItemId], [ItemType], [OrderId], [Quantity], [UnitPrice])
VALUES (@p22, @p23, @p24, @p25, @p26);
SELECT [ID]
FROM [Payments_OrderItem]
WHERE @@ROWCOUNT = 1 AND [ID] = scope_identity();
PortalWeb.exe Information: 0 : 2017-05-17 11:48:56.025 -05:00 [Information] Executed DbCommand (33ms) [Parameters=[@p27='?', @p28='?', @p29='?' (Size = 450), @p30='?', @p31='?'], CommandType='Text', CommandTimeout='120']
SET NOCOUNT ON;
INSERT INTO [DiscountCodes_Uses] ([ID], [ConsumedOn], [ConsumerId], [DiscountCodeId], [OrderItemId])
VALUES (@p27, @p28, @p29, @p30, @p31);

As you can see, EF attempts to erroneously INSERT the [ID] column.

As an aside, everything works fine if I don't attempt to add a DiscountCodesUses record. I.e. all the other tracking/id relationships seems to work.

Is this an EF issue or am I doing it wrong?

Thanks in advance!

[smitpatel]

In your code

entity.Property(e => e.Id)
.HasColumnName("ID")
.ValueGeneratedNever();

You have configured not to generate values for ID column in DiscountCodes_Uses table. When you add DiscountCodesUses entity to context, EF will just send value as is to database (since there is no value generation configured). In your case, since you have not set the value of Id it takes the default value of int which is 0. You need to provide a value for Id property or set value generation for it.

[SimonOrdo]

Thanks, @smitpatel

Two things:

1. That code was generated by EF scaffolding (just fyi, for what it's worth).
2. I removed the .ValueGeneratedNever() line... and now

Exception is:

InnerException = {"Cannot insert the value NULL into column 'ID', table 'dbo.DiscountCodes_Uses'; column does not allow nulls. INSERT fails.\r\nThe statement has been terminated."}

[smitpatel]

@SimonOrdo - What is the definition in database for table DiscountCodes_Uses

[SimonOrdo]

@smitpatel

CREATE TABLE [dbo].[DiscountCodes_Uses](
    [ID] [bigint] NOT NULL,
    [DiscountCodeId] [bigint] NOT NULL,
    [ConsumerId] [nvarchar](450) NOT NULL,
    [ConsumedOn] [datetime] NOT NULL,
    [OrderItemId] [bigint] NOT NULL,
 CONSTRAINT [PK_DiscountCodes_Uses] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

ALTER TABLE [dbo].[DiscountCodes_Uses]  WITH CHECK ADD  CONSTRAINT [FK_DiscountCodes_Uses_AspNetUsers] FOREIGN KEY([ConsumerId])
REFERENCES [dbo].[AspNetUsers] ([Id])
GO

ALTER TABLE [dbo].[DiscountCodes_Uses] CHECK CONSTRAINT [FK_DiscountCodes_Uses_AspNetUsers]
GO

ALTER TABLE [dbo].[DiscountCodes_Uses]  WITH CHECK ADD  CONSTRAINT [FK_DiscountCodes_Uses_DiscountCodes] FOREIGN KEY([DiscountCodeId])
REFERENCES [dbo].[DiscountCodes] ([ID])
GO

ALTER TABLE [dbo].[DiscountCodes_Uses] CHECK CONSTRAINT [FK_DiscountCodes_Uses_DiscountCodes]
GO

ALTER TABLE [dbo].[DiscountCodes_Uses]  WITH CHECK ADD  CONSTRAINT [FK_DiscountCodes_Uses_Payments_Orders] FOREIGN KEY([OrderItemId])
REFERENCES [dbo].[Payments_OrderItem] ([ID])
GO

ALTER TABLE [dbo].[DiscountCodes_Uses] CHECK CONSTRAINT [FK_DiscountCodes_Uses_Payments_Orders]
GO

[smitpatel]

Answer to 1:
Your ID column does not have identity (or default value etc) set hence Reverse engineer added ValueGeneratedNever to reflect what is in the database i.e. never generate values through any mechanism.

Answer to 2:
Since your database is not configured to generate any values, your database needs you to provide value of the column every time you are trying to insert something. When ValueGeneratedNever call was made, EF concluded that we don't have to generate values hence we will send whatever value you provided while adding the entity which is 0 and we sent it. (hence first exception)
When you remove call to ValueGeneratedNever in the runtime model, you made the property as ValueGenerated (by convention it would Identity). Since the value set is 0 which is default(long), we take it as no value set and the value should be generated by database. This sends Insert statement without including the column ID. Hence the 2nd exception.

Given that your database is not configured to use any value generation, whenever you are adding new entity, you need to explicitly specify the value of Id column.

[SimonOrdo]

Oh geez...!

I've spent hours looking at this!

Sorry for wasting your time and thanks for your help!