Vfrdtyky

I'm stuck trying to get EF to work with foreign keys in a TPT scenario. I think it is complicated in that one table has a one to many relationship, and the other has a one to one relationship.

This code is boiled down to illustrate the problem (GitHub: https://github.com/Dash/EFTest for VS project).

When I create a foreign key in the base Persons table I get an error about the type not being matched:

Product: FromRole: NavigationProperty 'Product' is not valid. Type
'Buyer' of FromRole 'Product_Buyers_Target' in AssociationType
'Product_Buyers' must exactly match with the type 'Person' on which
this NavigationProperty is declared on.

To try and tackle this, I move the Product navigation property up to the children, but then I get an error due to the ProductId column not existing in the database - it is trying to look in the derived tables and not in the base table.

I'm not entirely clear how to resolve this problem and have been going around in circles and googling until my eyes bleed. Can anybody help?

Model

public abstract class Person

{

    public int Id { get; set; }

    public string Name { get; set; }



    public virtual Product Product { get; set; }

}



public class Buyer : Person

{

    public string ShipTo { get; set; }

}



public class Seller : Person

{

    public int Rating { get; set; } = 0;

}



public class Product

{

    public int Id { get; set; }



    public string Description { get; set; }

    public virtual Seller Seller { get; set; }

    public virtual IList<Buyer> Buyers { get; set; }

}

Schema

public class ProductSchema : EntityTypeConfiguration<Product>

{

    public ProductSchema()

    {

        HasKey(p => p.Id)

            .Property(p => p.Id)

            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);



        Property(p => p.Description)

            .HasMaxLength(256);



        HasOptional(p => p.Seller)

            .WithRequired(i => i.Product)

            .Map(m => m.MapKey("ProductId"));



        HasMany(p => p.Buyers)

            .WithRequired(i => i.Product)

            .Map(m => m.MapKey("ProductId"));

    }

}



public class PersonSchema : EntityTypeConfiguration<Person>

{

    public PersonSchema()

    {

        ToTable("Persons");



        HasKey(p => p.Id)

            .Property(p => p.Id)

            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);



        Property(p => p.Name)

            .HasMaxLength(128);

    }

}



public class SellerSchema : EntityTypeConfiguration<Seller>

{

    public SellerSchema()

    {

        ToTable("Sellers");



        HasKey(s => s.Id)

            .Property(s => s.Id)

            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);



        Property(s => s.Rating);

    }

}



public class BuyerSchema : EntityTypeConfiguration<Buyer>

{

    public BuyerSchema()

    {

        ToTable("Buyers");



        HasKey(b => b.Id)

            .Property(b => b.Id)

            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);



        Property(b => b.ShipTo)

            .HasMaxLength(256);

    }

}

SQL

CREATE TABLE Products (

Id INT IDENTITY PRIMARY KEY,

[Description] nvarchar(256),

)



create table Persons (

    Id int IDENTITY PRIMARY KEY,

    [Name] nvarchar(128),

    ProductId INT NOT NULL,

    FOREIGN KEY (ProductId) REFERENCES Products(Id)

)



CREATE TABLE Buyers (

    Id INT PRIMARY KEY,

    ShipTo nvarchar(256)

)



CREATE TABLE Sellers (

    Id INT PRIMARY KEY,

    Rating INT NOT NULL DEFAULT 0

)



DECLARE @ProductId int;

INSERT INTO Products ([Description]) VALUES ('Widget');

SET @ProductId = @@IDENTITY;



INSERT INTO Persons ([Name], ProductId) VALUES ('Bob the seller', @ProductId);

INSERT INTO Sellers (Id, Rating) VALUES (@@IDENTITY, 0);



INSERT INTO Persons ([Name], ProductId) VALUES ('Bert the buyer', @ProductId);

INSERT INTO Buyers (Id, ShipTo) VALUES (@@IDENTITY, 'Somewhere');