EF Core - create relationship without primary/foreign keys

Question

I'm trying to configure EF to include documents when retriving a user or product. The entity Document has a ReferenceId property which should store either UserId or ProductId. This way, when I save a document for a user or product, the UserId or ProductId is saved to Document.ReferenceId.

Entities:

public class User
{
    public string Id { get; set; }
    public ICollection<Document> Documents { get; set; }
}

public class Product
{
    public string Id { get; set; }
    public ICollection<Document> Documents { get; set; }
}

public class Document
{
    public string Id { get; set; }
    public string ReferenceId { get; set; }
}

Configuring:

builder.Entity<User>(e =>
{
    e.HasKey(e => e.Id);
    e.Property(p => p.Id).ValueGeneratedOnAdd();
    e.HasMany(e => e.Documents)
     .WithOne()
     .OnDelete(DeleteBehavior.Cascade);
});

builder.Entity<Product>(e =>
{
    e.HasKey(e => e.Id);
    e.Property(p => p.Id).ValueGeneratedOnAdd();
    e.HasMany(e => e.Documents)
     .WithOne()
     .OnDelete(DeleteBehavior.Cascade);
});

builder.Entity<Document>(e =>
{
    e.HasKey(e => e.Id);
    e.Property(p => p.Id).ValueGeneratedOnAdd();
    e.ToTable("Documents");
});

Saving:

var user = new User { };
var userDocument = new Document { ReferenceId = user.Id };

var product = new Product { };
var productDocument = new Document { ReferenceId = product.Id };

_context.Users.Add(user);
_context.Products.Add(product);
_context.Add(userDocument);
_context.Add(productDocument);
_context.SaveChanges();

Migrations:

migrationBuilder.CreateTable(
    name: "Documents",
    columns: table => new
    {
        Id = table.Column<string>(nullable: false),
        ReferenceId = table.Column<string>(nullable: true),
        ProductId = table.Column<string>(nullable: true),
        UserId = table.Column<string>(nullable: true)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Documents", x => x.Id);
        table.ForeignKey(
            name: "FK_Documents_Products_ProductId",
            column: x => x.ProductId,
            principalTable: "Products",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
        table.ForeignKey(
            name: "FK_Documents_Users_UserId",
            column: x => x.UserId,
            principalTable: "Users",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
    });

I don't want 2 foreign keys (ProductId and UserId) to be created on Documents table. Is there a way to make EF automatically link UserId and ProductId to ReferenceId?

Answer 1

The only way I see, is to use TPH inheritance (See here for more information).

I have quoted and edited the answer by Erik H.

public enum DocumentType
{
    User = 0,
    Product = 1
}

public class BaseObject
{
    public string Id { get; set; }
    public ObjectType DocumentType{ get; set; }
    public virtual ICollection<Document> Documents { get; set; }
}

public class User : BaseObject
{
}

public class Product : BaseObject
{
}

public class Document
{
    public int Id { get; set; }
    public string BaseObjectId { get; set; }
    public virtual BaseObject DocumentObject { get; set; }
}

Via fluent-Api you can set a discriminator. This way ef core will only create one table for for both objects Product and User and distinguishes their type by the value of the discriminator column. But only as long as they have exactly the same properties which they share from the base class. As soon as you add properties to one of those subclasses a new table will be created (with all properties from the base- and subclass). Here is the configuration for the discriminator:

modelBuilder.Entity<BaseObject>()
    .HasDiscriminator<DocumentType>("DocumentType")
    .HasValue<User>(DocumentType.User)
    .HasValue<Product>(DocumentType.Product)

This may not be a clean approach (for me it seems like User and Product should not inherit from the same base class, because they do not share anything than the relations to documents). But it should work as you want it.

Answer 2

You can create a many to many table:

public class Product
{
  public string Id { get; set; }
  public ICollection<ProductDocument> ProductDocuments{ get; set; }
}

public class Document
{
  public string ReferenceId { get; set; }
}

public class ProductDocument
{
  public ICollection<Product> Products{ get; set; }
  public ICollection<Document> Documents { get; set; }
}

You will have to create a separate table for your user table ie UserDocumentes using the same pattern.

Answer 3

The proper way to solve it would be to have User and Product inherit a base class and move the Id and Documents properties to that class.

public class BaseObject
{
    public string Id { get; set; }
    public ICollection<Document> Documents { get; set; }
}

public class User : BaseObject
{
}

public class Product : BaseObject
{
}

public class Document
{
    public string BaseObjectId { get; set; }
}