Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.StockItemProductList' with unique index 'IX_StockItemProductList_1'. The duplicate key value is (1, 1, 1)

Radzen Studio Blazor Server
1

Ok, so new to Radzen, relatively new to C#, previously created an app with VS Lightswitch which I am trying to refactor.

I am getting the error;
Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.StockItemProductList' with unique index 'IX_StockItemProductList_1'. The duplicate key value is (1, 1, 1)
when deleting or creating an entry on the different but related table, StockPartSuppliers.

image
image733×652 11.7 KB

I'm working on the assumption that the two Product foreign keys in StockItemProductList may be causing some internal confusion(Radzen does have to be poked into correctly displaying data when approaching StockItemProductList from the StockPart2 Connection, incorrectly specifies i => Product instead of i => StockPart2)
But why is it trying to update that table anyway?
the problem only arises when the data for StockitemProductLists is Invoked as shown

image
image1274×692 41.3 KB

when not loaded there's no issue

This is for a small company, the Products table contains items for sale as well as stock items, a Product may be its own StockItem or may comprise of several different StockItems via the StockItemProductList.
There used to be a separate table called StockParts but the information in it was redundant to Products and so the information was folded into the Products table and the StockParts table detached, however the old records still remain and we are unable to update our current front end to cope with their removal :slightly_frowning_face:

Sorry for the long first post but I figured too much information is better than not enough
Any help will be much appreciated :slight_smile:

Ian

2

Here are some articles on how EF handles CRUD with related tables:

3

thank you for your prompt response, after writing my post and having some time to mull things over I have found my answer, however I think I've uncovered a flaw in how Radzen handles this scenario.
(I'm using a Master/Detail DataGrid for clarity. My project uses a Master/Detail Hierarchy, which has some bearing on my error but not the underlying issue)

To recap above the StockItemsProductList table has two foreign keys to the Product table, correctly identified here in this excerpt from the DBContext.cs file

        builder.Entity<EnergyCentre2.Models.EnergyOracle.StockItemProductList>()
              .HasOne(i => i.Product1)
              .WithMany(i => i.StockItemProductLists)
              .HasForeignKey(i => i.Product)
              .HasPrincipalKey(i => i.Id);
        builder.Entity<EnergyCentre2.Models.EnergyOracle.StockItemProductList>()
              .HasOne(i => i.Product2)
              .WithMany(i => i.StockItemProductLists1)
              .HasForeignKey(i => i.StockPart2)
              .HasPrincipalKey(i => i.Id);

which exposes two separate collections of StockItemProductList from the Product table as shown in this excerpt from Product.cs

    public ICollection<StockItemProductList> StockItemProductLists { get; set; }
    public ICollection<StockItemProductList> StockItemProductLists1 { get; set; }

so far so good, however when building a master detail page from this data like this (notice the child collections 'StockItemProductLists' and 'StockItemProductLists1')

image
image501×510 28 KB

the resultant screen is broken as shown (Edited for brevity)
image
image873×610 27.8 KB

the reason for this is that while the first table is constructed correctly, the second uses the same query and assignment as shown.

        protected async System.Threading.Tasks.Task Grid0RowSelect(EnergyCentre2.Models.EnergyOracle.Product args)
        {
            master = args;

            if (args == null) {
                StockItemProductLists = null;
            }

            if (args != null)
            {
                var energyOracleGetStockItemProductListsResult = await EnergyOracle.GetStockItemProductLists($"i => i.Product == {args.Id}", int.Parse($"{}"), int.Parse($"{}"));
                StockItemProductLists = energyOracleGetStockItemProductListsResult;
            }

            if (args != null)
            {
                var energyOracleGetStockItemProductListsResult0 = await EnergyOracle.GetStockItemProductLists($"i => *i.Product* == {args.Id}", int.Parse($"{}"), int.Parse($"{}"));
                *StockItemProductLists* = energyOracleGetStockItemProductListsResult0;
            }
        }

when it should look like this

        protected async System.Threading.Tasks.Task Grid0RowSelect(EnergyCentre2.Models.EnergyOracle.Product args)
        {
            master = args;

            if (args == null) {
                StockItemProductLists = null;
            }

            if (args != null)
            {
                var energyOracleGetStockItemProductListsResult = await EnergyOracle.GetStockItemProductLists(new Query() { Filter = $@"i => i.Product == {args.Id}" });
                StockItemProductLists = energyOracleGetStockItemProductListsResult;
            }

            if (args != null)
            {
                var energyOracleGetStockItemProductListsResult0 = await EnergyOracle.GetStockItemProductLists(new Query() { Filter = $@"i => *i.StockPart2* == {args.Id}" });
                *StockItemProductLists1* = energyOracleGetStockItemProductListsResult0;
            }
        }

The differences have been are shown between asterisks.

Whether or not StockItemProductLists is loaded, the collection StockItemProductLists1 always uses the connection to StockItemProductLists even though the underlying code seems to know the difference.

My original issue was caused by an incomplete fix where I corrected the query to fetch the correct data but didn't fix the assignment so I had, from the application point of view, changed the StockItemProductLists collection(I was using a Hierarchy view so {event}.StockItemProductLists recieved the data for {event}.StockItemProductLists1) hence the error.

So now I know what I was doing wrong, is it possible to fix Radzen to correctly identify the correct collection? (Maybe there's not enough information exposed in the database .cs files to adequately infer the correct connection?)

thank you again, and sorry for the long post

Ian

4

You should try Radzen Blazor Studio instead - there are tons of improvements compared to Radzen IDE.