EF Core Foreign Key: incompatible types

Question

I looked through many similar questions, but I find no applicable solution.

I get the following error message during tests:

System.InvalidOperationException : The relationship from 'Product.FeatureType' to 'FeatureType.Product' with foreign key properties {'Type' : string} cannot target the primary key {'Id' : Guid} because it is not compatible. Configure a principal key or a set of compatible foreign key properties for this relationship.

The foreign key should be FeatureType's Type field.

This only happens, when I set the type of Product.Type as a string and as not as a Guid. But it should be a string, rather than a Guid. I do not understand at all what is the problem here. I do the project in a DB-first approach and the database can be created without a problem with SQL using this logic. I appreciate every help.

Edit: Here is my MSSQL model:

CREATE TABLE [Core].[FeatureType](
    [id] [uniqueidentifier] NOT NULL,
    [int_id] [int] IDENTITY(1,1) NOT NULL,
    [type] [varchar](50) NOT NULL UNIQUE,
    [description] [uniqueidentifier] NULL,
    [SysStartTime] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEndTime] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_FeatureType] PRIMARY KEY NONCLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [Core].[FeatureTypeHistory] )
)
GO

CREATE TABLE [Core].[Product](
        [id] [uniqueidentifier] NOT NULL, 
        [name] [varchar](100) NOT NULL UNIQUE, 
        [type] [varchar](50) NOT NULL FOREIGN KEY REFERENCES [Core].[FeatureType](type),
        [SysStartTime] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
        [SysEndTime] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
        CONSTRAINT [PK_Product] PRIMARY KEY NONCLUSTERED ([id] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
        ON [PRIMARY],
        PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
    ) ON [PRIMARY] WITH (SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [Core].[ProductHistory] ))
GO

In my understanding, this should work because the FeatureType table's Type column is UNIQUE.

I have the following models:

public class Product : IDBModel
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Type { get; set; }

    public DateTime SysStartTime { get; set; }
    public DateTime SysEndTime { get; set; }

    public FeatureType FeatureType { get; }
}

public class FeatureType : IDBModel
{
    public Guid Id { get; set; }
    public string Type { get; set; }
    public Guid? Description { get; set; }

    public DateTime SysStartTime { get; set; }
    public DateTime SysEndTime { get; set; }

    public TxtID TxtIDDescription { get; set; }
    public ICollection<Feature> Feature { get; }

    public ICollection<Product> Product { get; }
 }

And the following related context configuration:

public virtual DbSet<Product> Product { get; set; }
public virtual DbSet<FeatureType> FeatureType { get; set; }
...

modelBuilder.Entity<Product>(entity =>
        {
            entity.ToTable("Product", CoreSchema)
                .HasKey(k => new { k.Id }) 
                .HasName("PK_Product");
            entity.Property(a => a.Id).HasColumnName("id");
            entity.Property(a => a.Name).HasColumnName("name").IsRequired();
            entity.Property(a => a.Type).HasColumnName("type").HasMaxLength(50).IsRequired().IsUnicode(false);
            entity.Property(e => e.SysStartTime).HasColumnName("SysStartTime").ValueGeneratedOnAddOrUpdate();
            entity.Property(e => e.SysEndTime).HasColumnName("SysEndTime").ValueGeneratedOnAddOrUpdate();
            entity.HasOne(p => p.FeatureType)
                .WithMany(d => d.Product)
                .HasForeignKey(p => p.Type)
                .OnDelete(DeleteBehavior.Restrict);
        });

modelBuilder.Entity<FeatureAttributeSet>(entity =>
        {
            entity.ToTable("FeatureAttributeSet", "Core")
                .HasKey(e => new { e.Id })
                .HasName("PK_FeatureAttributeSet");

            entity.Property(e => e.Id).HasColumnName("id").IsRequired();
            entity.Property(e => e.AttributeSetId).HasColumnName("as_id").IsRequired();
            entity.Property(e => e.FeatureId).HasColumnName("feature_id").IsRequired();

            entity.Property(e => e.SysStartTime).ValueGeneratedOnAddOrUpdate();
            entity.Property(e => e.SysEndTime).ValueGeneratedOnAddOrUpdate();

            entity.HasOne(d => d.AttributeSet)
                        .WithMany(p => p.FeatureAttributeSet)
                        .HasForeignKey(d => d.AttributeSetId)
                        .OnDelete(DeleteBehavior.Cascade)
                        .HasConstraintName("FK_FeatureAttributeSet_AttributeSet");
            entity.HasOne(d => d.Feature)
                        .WithMany(p => p.FeatureAttributeSet)
                        .HasForeignKey(d => d.FeatureId)
                        .OnDelete(DeleteBehavior.Restrict)
                        .HasConstraintName("FK_FeatureAttributeSet_Feature");
        });

Answer 1

You can model this in EF by using a value conversion.

The first step is to change the type of Product.Type:

public class Product
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public DateTime SysStartTime { get; set; }
    public DateTime SysEndTime { get; set; }

    public Guid Type { get; set; } // <= Guid, not string
    public FeatureType FeatureType { get; }
}

This satisfies EF's requirement that primary key and foreign key properties should have the same type.

But that alone would generate SQL that throws an exception because the database type is different. That's where value conversion are helpful. Just add one line in OnModelCreating:

entity.ToTable("Product")
    .HasKey(k => new { k.Id })
    .HasName("PK_Product");
// To convert string value from the database:
entity.Property(e => e.Type).HasConversion<string>();
...

Now EF accepts the association and also knows how to generate correct SQL for queries and inserts.

Answer 2

The solution is to configure the PrincipalKey. The PrincipalKey will allow us to define the reference key with a unique restriction which will be the destination of the relationship. So you can use like this

 modelBuilder.Entity<Product>(entity =>
    {
            entity.ToTable("Product", CoreSchema)
                .HasKey(k => new { k.Id }) 
                .HasName("PK_Product");
            entity.Property(a => a.Id).HasColumnName("id");
            entity.Property(a => a.Name).HasColumnName("name").IsRequired();
            entity.Property(a => a.Type).HasColumnName("type").HasMaxLength(50).IsRequired().IsUnicode(false);
            entity.Property(e => e.SysStartTime).HasColumnName("SysStartTime").ValueGeneratedOnAddOrUpdate();
            entity.Property(e => e.SysEndTime).HasColumnName("SysEndTime").ValueGeneratedOnAddOrUpdate();
            entity.HasOne(p => p.FeatureType)
                .WithMany(d => d.Product)
                .HasPrincipalKey(p => p.Type)
                .HasForeignKey(p => p.Type)
                .OnDelete(DeleteBehavior.Restrict);
        });