EF Core Many-To-Many Relationship Sets Both Keys to Parent

Question

I working on building out a model that would represent a typical product that could be created in an e-commerce platform written using EF Core 2.0. See the model structure below

public class GSProduct : BaseEntity
{
    [Required]
    public string Name { get; set; }

    public ICollection<GSProduct> BaseProducts { get; set; }

    public ICollection<GSRelatedProduct> ParentProducts { get; set; } 
    public ICollection<GSRelatedProduct> ChildProducts { get; set;  } 

    public ICollection<GSVendorProductInfo> VendorProductInfo { get; } = new List<GSVendorProductInfo>();

}

public class GSRelatedProduct
{

    public virtual GSProduct ParentProduct { get; set; }
    public Guid ParentProductId { get; set; }

    public virtual GSProduct ChildProduct { get; set; }
    public Guid ChildProductId { get; set; }

}

public class GSVendorProductInfo : BaseEntity
{
    public GSContact Vendor { get; set; }
    public Guid VendorId { get; set; }

    public GSProduct Product { get; set; }
    public Guid ProductId { get; set; }

    public string VendorPartNumber { get; set; }
    public int BaseUnits { get; set; }
    public double Cost { get; set; }
    public int MinOrderQty { get; set; }
    public int OrderedInMultiples { get; set; }

}

This is what I have set up for the Fluent API.

           modelBuilder.Entity<GSVendorProductInfo>()
            .HasOne(pt => pt.Product)
            .WithMany(p => p.VendorProductInfo)
            .HasForeignKey(pt => pt.ProductId);

        modelBuilder.Entity<GSVendorProductInfo>()
            .HasOne(pt => pt.Vendor)
            .WithMany(t => t.VendorProductInfo)
            .HasForeignKey(pt => pt.VendorId);

        modelBuilder.Entity<GSRelatedProduct>().HasKey(x => new { x.ParentProductId, x.ChildProductId });

        modelBuilder.Entity<GSRelatedProduct>()
            .HasOne(pt => pt.ParentProduct)
            .WithMany(t => t.ParentProducts)
            .HasForeignKey(pt => pt.ParentProductId);

        modelBuilder.Entity<GSRelatedProduct>()
            .HasOne(pt => pt.ChildProduct)
            .WithMany(t => t.ChildProducts)
            .HasForeignKey(pt => pt.ChildProductId);

Also including the migration

migrationBuilder.CreateTable(
            name: "GSRelatedProducts",
            columns: table => new
            {
                ParentProductId = table.Column<Guid>(nullable: false),
                ChildProductId = table.Column<Guid>(nullable: false),
                Optional = table.Column<bool>(nullable: false),
                Quantity = table.Column<int>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_GSRelatedProducts", x => new { x.ParentProductId, x.ChildProductId });
                table.ForeignKey(
                    name: "FK_GSRelatedProducts_GSProducts_ChildProductId",
                    column: x => x.ChildProductId,
                    principalTable: "GSProducts",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.NoAction);
                table.ForeignKey(
                    name: "FK_GSRelatedProducts_GSProducts_ParentProductId",
                    column: x => x.ParentProductId,
                    principalTable: "GSProducts",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.NoAction);
            });

The scaffolding / migration is working fine and I can actually create products without a problem that include all of the relationships. The issue arises when I try to add a 'RelatedProduct' to the Product model.

I set the ParentProductId and the ChildProductId accordingly and when I create or update the entity it sets both the ParentProductId and the ChildProductId value to the ParentProductId.

I've followed the code through my debugger and it is correct up until the point where I call _context.Update(entity). After that both of the Ids in the RelatedProduct model are set to the same value.

I've got no idea why this is happening any suggestions would be very helpful.

How is your mapping of class property to table column name set? Could it be that you set parent id to both columns? Could you provide some code for it? — Huske, Feb 18 '18 at 22:32
Are you referring to the migrations that were created for the table? — James Parker, Feb 18 '18 at 22:39
that could also help. Usually there is a mapping class that says something like: Property(p => ParentId).HasColumnName("ParentId") — Huske, Feb 18 '18 at 22:47
@Brad The Union of parent and child products would be the list of all related products. — James Parker, Feb 18 '18 at 22:50
You may want to check the EF Core project on Github to see if something similar has been reported as an issue. It could be a bug. In the meantime I suggest you bypass the `_context.Update(entity)` method and just set the relationship Id's manually. — Brad, Feb 18 '18 at 23:05
@HuseinRoncevic I updated the question to include the migration code. — James Parker, Feb 18 '18 at 23:54

score 0 · Answer 1 · answered Feb 19 '18 at 00:15

0

I think there is a problem with the way your migration is generated. From the above code I am not sure it is clear what is the purpose of the child property in GSRelatedProduct. If you look at the migration you will see that the same constraint is set for both parent and child:

table.ForeignKey(
    name: "FK_GSRelatedProducts_GSProducts_ChildProductId",
    column: x => x.ChildProductId,
    principalTable: "GSProducts",
    principalColumn: "Id",
    onDelete: ReferentialAction.NoAction);
table.ForeignKey(
    name: "FK_GSRelatedProducts_GSProducts_ParentProductId",
    column: x => x.ParentProductId,
    principalTable: "GSProducts",
    principalColumn: "Id",
    onDelete: ReferentialAction.NoAction);

Both Parent and Child have the same principal table and principal column. They will both get the same value from GSProducts table. What your logic or business process is I cannot figure out, but you are actually getting the desired effect. Is the child supposed to point to something else? In your code you are probably assigning different values to product and child, but this seems to be somehow overwritten. Basically, your code first is thinking that both and parent should have the same value. In other words, Child seems to be redundant.

Is your GSProduct table a self-referencing table where you keep products and subproducts together? If so, then you need an additional column for this purpose like ParentId that points to the Id in order to get the desired relationship.

answered Feb 19 '18 at 00:15

Huske

9,186
2
36
53

The goal is to be able to create a list of products and attach them to a parent product. These items are all GSProduct models. So to me it seems that the table model is correct. Both ChidProductId and ParentProductId would be referencing the ID column in GSProducts because they are both referencing GSProduct models. – James Parker Feb 19 '18 at 00:18
But then you will get the actual result. My understanding is that your child product id should reference some sort of a subcategory within GSProduct. – Huske Feb 19 '18 at 00:20
Try to manually insert some value into `GSRelatedProduct` using `INSERT INTO` SQL statement and see if that works. – Huske Feb 19 '18 at 00:22
If I manually run INSERT INTO GSRelatedProducts (ParentProductId, ChildProductId, Optional, Quantity) VALUES ('B3F755BF-9186-4423-EB41-08D577193FE9', 'EEF699E3-9E1B-4E4C-EB40-08D577193FE9', 0, 1) It inserts the correct values. – James Parker Feb 19 '18 at 00:28
According to this question what I'm doing should technically be working. https://stackoverflow.com/questions/39728016/self-referencing-many-to-many-relations. Not sure where I'm going wrong. – James Parker Feb 19 '18 at 00:45
The only difference between your code and the one in your post is that the one in your post shows a class with an explicitly defined ID property, but you don't have it on your side in GSProduct. Here is another answer and you will notice that there is also an explicitly defined Id property on the main table. https://stackoverflow.com/questions/5559043/entity-framework-code-first-two-foreign-keys-from-same-table – Huske Feb 19 '18 at 00:58
It's not shown in the code, but ID is in the BaseEntity class. – James Parker Feb 19 '18 at 01:27

EF Core Many-To-Many Relationship Sets Both Keys to Parent

1 Answers1