entity-framework-corexaf

The column name is specified more than once inserting into entities with 1 to 1 relationship


I have two tables that join in a 1 to 1 relationship

using System.ComponentModel;

public class Product 
{
    public Product()
    {
        MaterialProperties = new MoreProductInfo
        {
            Product = this
        };
    }

    [Key] 
    public int ItemId { get; set; }

    [ForeignKey("ItemId")] 
    public virtual MoreProductInfo MaterialProperties { get; set; }
}

public class MoreProductInfo : IObjectSpaceLink, IElipseLookUp
{
    [Key] 
    public int ItemID { get; set; }

    [ForeignKey("ItemId")]
    public virtual Product Product { get; set; }
}

The relationship is set up in FluentAPI like this:

modelBuilder.Entity<Product>()
    .HasOne<MoreProductInfo>(x => x.MaterialProperties)
    .WithOne(x => x.Product)
    .HasForeignKey<MoreProductInfo>(m => m.ItemID);

When I try to save a new product I get

Microsoft.Data.SqlClient.SqlException
HResult=0x80131904

The column name 'ItemID' is specified more than once in the SET clause or column list of an INSERT. A column cannot be assigned more than one value in the same clause. Modify the clause to make sure that a column is updated only once. If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code.

Source=Core Microsoft SqlClient Data Provider

StackTrace:
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

I am using XAF 21.2.8 on .NET 6 and Entity Framework Core 5.0.14

I tried the following;

modelBuilder.Entity<Product>()
    .HasOne(x => x.MaterialProperties)
    .WithOne(x => x.Product)
    .HasForeignKey<MoreProductInfo>(m => m.ItemID)
    .HasForeignKey<Product>(x => x.ItemId)
    .HasPrincipalKey<Product>(x => x.ItemId)
    .HasPrincipalKey<MoreProductInfo>(x = >x.ItemID);

But this gives the error

System.InvalidOperationException
  HResult=0x80131509
  Message=The principal and dependent ends of the relationship cannot be inverted once foreign key or principal key properties have been specified.
  Source=Microsoft.EntityFrameworkCore
  StackTrace:
   at Microsoft.EntityFrameworkCore.Metadata.Builders.InvertibleRelationshipBuilderBase..ctor(InternalForeignKeyBuilder builder, InvertibleRelationshipBuilderBase oldBuilder, Boolean inverted, Boolean foreignKeySet, Boolean principalKeySet, Boolean requiredSet)

Solution

  • I got it working by adjusting the attributes to use a different column name in one of the entities as follows

    public class MoreProductInfo : IObjectSpaceLink, IElipseLookUp
    {
        [Column("ItemID")]
        [Key] public int ExtItemID { get; set; }
    
        [ForeignKey("ExtItemID")]
        public virtual Product Product { get; set; }
    
       // etc
    

    and correcting the Fluent api

    modelBuilder.Entity<Product>().HasOne(x => x.MaterialProperties).WithOne(x => x.Product)
                .HasForeignKey<MoreProductInfo>(m => m.ExtItemID).HasPrincipalKey<Product>(x => x.ItemId);