Polymorphic Associations: Attaching Comments and Files to Multiple Tables

Version note: The C# code in this post was tested with .NET 10 and EF Core 10, and the SQL examples with SQL Server 2025.

Guess First: Who Owns This Attachment?

In HR Portal, a quote PDF is attached to a purchase request, an invoice scan to an invoice, a payslip to an employee. Not wanting three attachment tables for three tables, the team built a single one:

CREATE TABLE dbo.NaiveInvoices  (Id int PRIMARY KEY, Number varchar(20) NOT NULL);
CREATE TABLE dbo.NaiveEmployees (Id int PRIMARY KEY, FullName nvarchar(100) NOT NULL);
CREATE TABLE dbo.NaiveAttachments
(
    Id         int IDENTITY PRIMARY KEY,
    EntityType varchar(50)   NOT NULL,
    EntityId   int           NOT NULL,
    FileName   nvarchar(200) NOT NULL
);
INSERT dbo.NaiveInvoices VALUES (1, 'INV-2026-0042');
INSERT dbo.NaiveEmployees VALUES (1, N'Ada Lovelace');
INSERT dbo.NaiveAttachments (EntityType, EntityId, FileName) VALUES
    ('Invoice', 1, N'inv-0042.pdf'),
    ('Employee', 1, N'payslip-2026-03.pdf'),
    ('invoice', 1, N'credit-note-118.pdf'),
    ('Invoice', 7, N'scan-0007.pdf');
DELETE dbo.NaiveInvoices WHERE Id = 1;

How many of the four INSERTs fail? What happens to inv-0042.pdf after the invoice is deleted? And when a GET /attachments/2 request arrives, how does the API know that the user asking for this file is downloading a payslip?

Context and Problem

Comments, attachments, tags, notes; anything that can be attached to more than one entity asks the same question: how does one row belong to one of several tables? The EntityType and EntityId pair is the quickest answer, and Rails' polymorphic: true association made it popular. In SQL Antipatterns, Bill Karwin calls this structure Polymorphic Associations and lists it as an antipattern.

The core of the problem: the EntityId column can't be a foreign key, because the table it points to changes from row to row. Without an FK the database knows none of the relationship's rules: attaching to an owner that doesn't exist, a wrong type name, the owner being deleted. In HR Portal this table holds 1.8 million files, and some of them are the most sensitive documents there are: salaries, medical reports and copies of ID cards.

Symptoms

If storage is piling up files that belong to no record, if the EntityType column has Invoice, invoice and Invoices side by side, if the query listing an entity's attachments has a separate UNION ALL branch per type, if the attachment download endpoint only checks whether the file exists, or if someone wrote a nightly job that cleans up attachments of deleted owners, you're paying the price of polymorphic associations.

The Naive Solution: EntityType and EntityId

None of the opening script's INSERTs fail. Finding attachments without an owner after the delete means looking at each type separately:

SELECT a.Id, a.EntityType, a.EntityId, a.FileName
FROM dbo.NaiveAttachments AS a
WHERE NOT EXISTS (SELECT 1 FROM dbo.NaiveInvoices  AS i WHERE a.EntityType = 'Invoice'  AND i.Id = a.EntityId)
  AND NOT EXISTS (SELECT 1 FROM dbo.NaiveEmployees AS e WHERE a.EntityType = 'Employee' AND e.Id = a.EntityId);

Id|EntityType|EntityId|FileName
--|----------|--------|--------
1|Invoice|1|inv-0042.pdf
3|invoice|1|credit-note-118.pdf
4|Invoice|7|scan-0007.pdf

Three of the four attachments are orphans. One belonged to the deleted invoice and stayed because there's no cascade delete. One was written as lowercase invoice; since this database's default collation is case-insensitive the SQL comparison matches it, but a switch (EntityType) on the C# side doesn't. The last one was attached to invoice 7, which never existed. Whoever adds a new type (say, Supplier) and forgets to add another branch to this query will think all attachments of that type are orphans.

The worst break is in authorization. If the download endpoint returns the file once it sees that the row exists in Attachments, a user trying ids in sequence can download other people's payslips. That's the textbook IDOR we saw in the Broken Access Control post of the OWASP Top 10 series. An attachment has no authorization of its own; the authorization belongs to its owner. A design that keeps the owner as a type/id pair leaves finding the right owner and applying its rule on every download to the developer's memory.

Options

Exclusive Arc: A Nullable FK per Owner

Put a separate, nullable FK column on the attachment table for each owner type and write the rule "exactly one is set" as a check constraint. In data modeling literature this structure is called an exclusive arc. The FKs are real, cascade delete works, and the owner is found with a single CASE. The price: adding a new owner type means a column, an FK and a constraint change.

A Join Table per Type

Karwin's first proposed fix is to reverse the reference: the attachment table points to no one, and each owner type gets its own link table.

CREATE TABLE dbo.InvoiceFiles
(
    InvoiceId int NOT NULL REFERENCES dbo.JInvoices (Id) ON DELETE CASCADE,
    FileId    int NOT NULL REFERENCES dbo.Files (Id),
    CONSTRAINT PK_InvoiceFiles PRIMARY KEY (InvoiceId, FileId)
);
INSERT dbo.JInvoices VALUES (1, 'INV-2026-0042');
INSERT dbo.Files (FileName) VALUES (N'inv-0042.pdf');
INSERT dbo.InvoiceFiles VALUES (1, 1);
DELETE dbo.JInvoices WHERE Id = 1;
SELECT (SELECT COUNT(*) FROM dbo.InvoiceFiles) AS LinkRows, (SELECT COUNT(*) FROM dbo.Files) AS FileRows;

LinkRows|FileRows
--------|--------
0|1

Integrity is complete and the same file can be linked to several records, like a supplier contract appearing on five purchase requests. But note the output: when the invoice was deleted the link went away and the file stayed. In a many-to-many relationship the file has no owner, so orphan file cleanup becomes a separate job. And "whose file is this" now requires looking at every link table.

A Common Supertable

Karwin's second proposal: give all owner types a common parent table. Every invoice, request and employee has an Attachables row, and attachments point to that row:

CREATE TABLE dbo.Attachables (Id int IDENTITY PRIMARY KEY, OwnerType varchar(20) NOT NULL);
CREATE TABLE dbo.SInvoices
(
    Id           int IDENTITY PRIMARY KEY,
    AttachableId int NOT NULL UNIQUE REFERENCES dbo.Attachables (Id),
    Number       varchar(20) NOT NULL
);
CREATE TABLE dbo.SAttachments
(
    Id           int IDENTITY PRIMARY KEY,
    AttachableId int NOT NULL CONSTRAINT FK_SAttachments_Attachables REFERENCES dbo.Attachables (Id),
    FileName     nvarchar(200) NOT NULL
);
INSERT dbo.SAttachments (AttachableId, FileName) VALUES (99, N'ghost.pdf');

Msg 547, Level 16, State 1, Server b8be943222fa, Line 19
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SAttachments_Attachables". The conflict occurred in database "HrPortal", table "dbo.Attachables", column 'Id'.

One FK, and adding a new type doesn't touch the attachment table. But every owner table gets an extra column and every insert an extra row, and "which invoice owns this attachment" becomes a reverse join through Attachables. Deleting the owner only reaches the attachments if the Attachables row is deleted.

A JSON Reference

Keeping ownership in a JSON column such as {"type":"Invoice","id":42} is the same as the type/id pair, only less visible. There are no integrity rules at all, and index and query costs are higher. JSON is a good place for the flexible part of attachment metadata (size, content type, scan result), not for ownership.

Implementation: An Exclusive Arc with EF Core and Owner-Based Authorization

In HR Portal an attachment has exactly one owner, and the owner types are few and stable (request, invoice, employee). The choice is an exclusive arc. The entity:

public class Attachment
{
    public int Id { get; set; }
    public string FileName { get; set; } = "";
    public int? PurchaseOrderId { get; set; }
    public PurchaseOrder? PurchaseOrder { get; set; }
    public int? InvoiceId { get; set; }
    public Invoice? Invoice { get; set; }
    public int? EmployeeId { get; set; }
    public Employee? Employee { get; set; }
}

The check constraint and three FKs in the configuration:

modelBuilder.Entity<Attachment>(e =>
{
    e.Property(x => x.FileName).HasMaxLength(200);
    e.ToTable(t => t.HasCheckConstraint("CK_Attachments_ExactlyOneOwner",
        "(CASE WHEN [PurchaseOrderId] IS NULL THEN 0 ELSE 1 END"
        + " + CASE WHEN [InvoiceId] IS NULL THEN 0 ELSE 1 END"
        + " + CASE WHEN [EmployeeId] IS NULL THEN 0 ELSE 1 END) = 1"));
    e.HasOne(x => x.PurchaseOrder).WithMany().HasForeignKey(x => x.PurchaseOrderId).OnDelete(DeleteBehavior.Cascade);
    e.HasOne(x => x.Invoice).WithMany().HasForeignKey(x => x.InvoiceId).OnDelete(DeleteBehavior.Cascade);
    e.HasOne(x => x.Employee).WithMany().HasForeignKey(x => x.EmployeeId).OnDelete(DeleteBehavior.Cascade);
});

In PostgreSQL this condition is shorter with num_nonnulls(...) = 1; SQL Server needs the sum of CASE expressions. Trying to add an attachment without an owner gives:

Rejected: 547 The INSERT statement conflicted with the CHECK constraint "CK_Attachments_ExactlyOneOwner". The conflict occurred in database "HrPortal", table "dbo.Attachments".

The Multiple Cascade Paths Trap

In the first version of the model, the invoice's FK to the purchase request was also cascade, EF Core's default. EnsureCreated couldn't build the schema:

Schema failed: 1785 Introducing FOREIGN KEY constraint 'FK_Attachments_PurchaseOrders_PurchaseOrderId' on table 'Attachments' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

When a request is deleted, its attachments are reachable by two paths: directly and through the invoice. SQL Server doesn't allow cascade in that case. With an exclusive arc you'll hit this error whenever the owners are related to each other. The fix is in the business rule: a request that has invoices shouldn't be deleted anyway, so we made the invoice → request relationship Restrict. If the rule were "delete the invoices when the request is deleted", the cascade would have to happen on the application side, in a single domain service.

Owner Information in One Query

Listing attachments with their owner's name isn't a UNION ALL; it's three LEFT JOINs and a CASE:

var query = db.Attachments
    .OrderBy(a => a.Id)
    .Select(a => new
    {
        a.Id,
        a.FileName,
        Owner = a.PurchaseOrderId != null ? "PurchaseOrder " + a.PurchaseOrder!.Number
              : a.InvoiceId != null ? "Invoice " + a.Invoice!.Number
              : "Employee " + a.Employee!.FullName
    });

SELECT [a].[Id], [a].[FileName], CASE
    WHEN [a].[PurchaseOrderId] IS NOT NULL THEN N'PurchaseOrder ' + COALESCE([p].[Number], N'')
    WHEN [a].[InvoiceId] IS NOT NULL THEN N'Invoice ' + COALESCE([i].[Number], N'')
    ELSE N'Employee ' + COALESCE([e].[FullName], N'')
END AS [Owner]
FROM [Attachments] AS [a]
LEFT JOIN [PurchaseOrders] AS [p] ON [a].[PurchaseOrderId] = [p].[Id]
LEFT JOIN [Invoices] AS [i] ON [a].[InvoiceId] = [i].[Id]
LEFT JOIN [Employees] AS [e] ON [a].[EmployeeId] = [e].[Id]
ORDER BY [a].[Id]
  #1 quote.pdf            PurchaseOrder PO-2026-0108
  #2 payslip-2026-03.pdf  Employee Ada Lovelace
  #3 inv-0042.pdf         Invoice INV-2026-0042

You can also model the same arc in EF Core with TPH inheritance: subtypes such as InvoiceAttachment and EmployeeAttachment, each with its own FK, in one table with a discriminator column. Type safety in code goes up and the table stays the same; weigh which path suits your team together with the Inheritance Mapping post in the EF Core series.

Authorization Lives with the Owner

The attachment download endpoint should never ask "does the attachment exist"; it should ask "can this user see the attachment's owner":

// An attachment's authorization isn't on its own row, it's on its owner
public async Task<bool> CanReadAsync(int attachmentId, UserInfo user, CancellationToken ct = default)
{
    Attachment? a = await db.Attachments
        .AsNoTracking()
        .Include(x => x.PurchaseOrder)
        .Include(x => x.Invoice)
        .Include(x => x.Employee)
        .SingleOrDefaultAsync(x => x.Id == attachmentId, ct);

    return a switch
    {
        null => false,
        { PurchaseOrder: PurchaseOrder po } => po.CompanyId == user.CompanyId
            && (po.RequestedBy == user.Name || user.Roles.Contains("Purchasing")),
        { Invoice: Invoice inv } => inv.CompanyId == user.CompanyId
            && (user.Roles.Contains("Finance") || user.Roles.Contains("Purchasing")),
        { Employee: Employee emp } => emp.CompanyId == user.CompanyId
            && (emp.UserName == user.Name || user.Roles.Contains("HR")),
        _ => false
    };
}

Attachment 2 for alan : denied
Attachment 2 for grace: allowed
Attachment 2 for ada  : allowed
Attachment 3 for alan : allowed
Attachment 3 for grace: denied
Attachment 3 for ada  : denied

Alan from purchasing can't see the payslip; Grace from HR and Ada, whose payslip it is, can. Purchasing sees the invoice, HR doesn't. The _ => false branch of the switch expression matters: if a new owner type is added and this method isn't updated, attachments stay closed by default (fail closed). In a real application each branch should call the owner entity's own authorization policy, so the rule "who sees payslips" isn't written in two places. In ASP.NET Core that's a resource-based authorization handler for the owner entity. The endpoint should also return the same 404 for missing and unauthorized attachments, so scanning ids leaks nothing.

Finally, the cascade:

Attachments after deleting the invoice: 2

Deleting the invoice deleted its attachment too; no nightly job cleaning orphans is needed. The file itself in blob storage stays outside the database cascade, though; deleting it needs an outbox entry when the attachment row is deleted, or a periodic reconciliation.

Trade-off Table

When Not to Use It

If there are dozens of owner types and they change often (say, a platform where every module's entities can take comments), an exclusive arc needs a migration for every new type and the table keeps widening; a supertable or a join table per type fits better there. If linking the same file to several records is a real need, don't use an arc; that relationship is many-to-many. The only place a type/id pair is acceptable is where integrity is meaningless anyway: "which entity was touched" information in an immutable audit log or a log table. Even there, don't use it as a source of authorization.

Checklist

• Question every EntityType/EntityId pair on a table linked to several tables; an FK-less relationship should be a written decision.
• In an exclusive arc, put the "exactly one owner" rule in the database with a check constraint.
• Design cascade delete; on a multiple cascade paths error, choose Restrict based on the business rule.
• Derive download authorization from the owner's policy, not from the attachment itself; fail closed for unknown types.
• Return the same response for missing and unauthorized attachments.
• Plan deleting the file in blob storage separately from the database cascade.
• Use a single query with LEFT JOINs instead of UNION ALL to list attachments with their owner's name.

Related Patterns and Sources

IDOR and resource-based authorization are covered in the OWASP Top 10 series post OWASP A01: Broken Access Control (1) — IDOR, Admin Endpoints and Authorization in ASP.NET Core and in the Authorization post of the ASP.NET Core Web API series. Cascade delete behavior is in the EF Core series post Relationships in EF Core, Part 1: One-to-Many, Foreign Keys and Cascade Delete; for modeling the same arc with TPH, Inheritance Mapping in EF Core: TPH, TPT and When to Choose Which; for many-to-many join tables, Relationships in EF Core, Part 2. Upload limits and streaming are in the File Upload and Download post of the ASP.NET Core Web API series. How deleting attachments relates to soft delete and KVKK is in this series' Doing Soft Delete Right. Sources: Bill Karwin, SQL Antipatterns Volume 1 (2022), the Polymorphic Associations chapter (the Reverse the References and Common Super-Table solutions); Microsoft Learn, Cascade Delete (EF Core) and SQL Server's multiple cascade paths restriction; Microsoft Learn, EF Core model configuration (HasCheckConstraint); PostgreSQL documentation, num_nonnulls; OWASP, Insecure Direct Object Reference Prevention Cheat Sheet.

A Small Experiment

★☆☆ Add a fourth owner for Supplier: column, FK, check constraint and a CanReadAsync branch. How many places changed, and forgetting which one would have created a silent security hole?

★★☆ Build the same model with TPH: an abstract Attachment base with PurchaseOrderAttachment, InvoiceAttachment and EmployeeAttachment subtypes. Compare the table EF Core generates with the exclusive arc; do you still need the check constraint?

★★★ Write a migration plan that moves the existing 1.8-million-row EntityType/EntityId table to an exclusive arc without downtime: add the new columns, fill them in batches, report the orphans, add the check constraint WITH NOCHECK and validate it later. At which step do you have to stop writes?

In the next post we make the data itself multilingual, not the UI translation: we'll compare translation tables, JSON columns and a fallback chain for storing expense category and department names in Turkish and English.