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=0x80131904The 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)
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);