Entity Framework 6 define a REALLY optional foreign key

Question

Say I have two tables:

Products
[ProductID] PK
[Weight]
[ProductCode]

and

KnownProductCodes
[ProductCode] PK
[Description]

Now I want my Product entity to have a KnownProductCodeDetails property. BUT the interesting thing here is that Products.ProductCode may contain EITHER product codes that DO exist in the KnownProductCodes table, OR the product codes THAT DON'T EXIST in KnownProductCodes table.

So, my question is: how do I create such a relationship between the two entities in Entity Framework?

PS. By the way, is it possible for an entity to have a foreign relationship without having a corresponding constraint in the database?

Thanks!

== Details: EF 6.1.2, Code first

Answer 1

Such an association wouldn't be a foreign key.

A FK is a contraint, it uses an entity's primary key values to restrict the domain of values in the referencing entity. However, you don't want the values Product.ProductCode to be constrained, so this field can't be a foreign key by definition. (Nor technically).

A second point is that meaningful primary keys, like KnownProductCodes.ProductCode, are nearly always a bad idea, because one day the business may demand to change their values. And changing primary key values is a hassle.

So the obvious thing here would be to create a real nullable foreign key to a new primary key field, KnownProductCodesId. Then you can get the display value for a product's product code either from this FK (if not null) or Product.ProductCode. And modifying KnownProductCodes.ProductCode is easy now.

Another approach could be to create a "free association". Let this be your classes:

public class Product
{
    public int ProductID { get; set; }
    public decimal? Weight { get; set; }
    public string ProductCode { get; set; }
    public virtual KnownProductCode KnownProductCode { get; set; }
}

public partial class KnownProductCode
{
    public string ProductCode { get; set; }
    public string Description { get; set; }
}

Now in the mappings you can define an association between them:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<KnownProductCode>().HasKey(k => k.ProductCode);
    modelBuilder.Entity<Product>()
        .HasOptional(p => p.KnownProductCode)
        .WithMany()
        .HasForeignKey(p => p.ProductCode);
}

But in the database you avoid creating the actual FK. EF will allow that, it only wants associations to point to an entity's primary key, but the association doesn't have to be a hard FK in the database.

(Note however that this takes special measures if you create the database from the model, I wouldn't recommend it).