I was giving a version of the workshop on CQRS and Event Sourcing to a JasperFx client today (because that’s something that we do if you’re interested!). In the workshop, I give a little almost digression into Marten’s old document database functionality just to introduce that side of things because Marten’s projections subsystem relies heavily on that same document database functionality. I was speaking to an EF Core heavy crowd and they stopped me to ask questions about Marten’s ability to store and query hierarchies of related types — which is something Marten does very well and has done so for over a decade now.
Most of my time is spent reacting to various problems or trying to fill in the handful of gaps in Critter Stack capabilities, so it was nice to just stop and talk about something that Marten does very well and that’s been so stable for so long that I basically forget about it. Long story short, this functionality is easier and simpler in Marten (or Polecat or Fisher too) than it is with EF Core.
Let’s say you’ve got a Payment concept with a handful of specialized flavors. This is close to the example that Milan Jovanović used in his post on EF Core inheritance mapping back in August, and I’m borrowing it here because it’s about as representative of the “real” problem as anything I could make up:
public abstract class Payment{ public Guid Id { get; set; } public decimal Amount { get; set; } public string Currency { get; set; } = "USD"; public DateTimeOffset CreatedAt { get; set; }}public class CreditCardPayment : Payment{ public string CardHolderName { get; set; } public string LastFour { get; set; }}public classBankTransferPayment : Payment{ public string BankName { get; set; } public string AccountNumber { get; set; }}public class CryptoPayment : Payment{ public string WalletAddress { get; set; } public string Network { get; set; }}
You want to be able to query for “all payments over $100 last week” and get back a mixed bag of concrete types, and you want to be able to say “give me the credit card payments for this card holder” without wading through the bank transfers. That’s not an exotic requirement. That’s Tuesday.
The EF Core way, where you have to pick
If you’re using EF Core, the official inheritance docs give you three mapping strategies, and you really do have to choose one up front because the choice changes your database schema, your generated SQL, and in one case, how your primary keys get generated. Quickly:
Table per hierarchy (TPH) is the default. All four types land in one Payments table with a discriminator column, and every subclass-only property becomes a nullable column that’s NULL on every row of every other subtype:
public class PaymentConfiguration : IEntityTypeConfiguration<Payment>{ public void Configure(EntityTypeBuilder<Payment> builder) { builder.ToTable("Payments"); builder.HasDiscriminator<string>("PaymentType") .HasValue<CreditCardPayment>("CreditCard") .HasValue<BankTransferPayment>("BankTransfer") .HasValue<CryptoPayment>("Crypto"); builder.Property("PaymentType").HasMaxLength(50); }}
Table per type (TPT) gives you a Payments table plus one table per subclass joined back to the base by a foreign key. Your schema looks exactly like your class diagram, which is appealing right up until you look at the query plans. Loading “all payments” is a LEFT JOIN per subclass:
modelBuilder.Entity<Payment>().ToTable("Payments");modelBuilder.Entity<CreditCardPayment>().ToTable("CreditCardPayments");modelBuilder.Entity<BankTransferPayment>().ToTable("BankTransferPayments");modelBuilder.Entity<CryptoPayment>().ToTable("CryptoPayments");
Table per concrete type (TPC) gives you one standalone table per concrete class, each with the full set of columns copied in. Polymorphic queries become a UNION ALL across every table. Because there’s no longer a single table to own the identity, EF has to hand out ids from a shared database sequence, and you can’t put a real foreign key on anything that points at “a payment” because a payment’s key might live in any of three tables:
modelBuilder.Entity<Payment>().UseTpcMappingStrategy();
To be fair to the EF team, they’re pretty candid about all this. The modeling for performance page says in so many words that TPT is “in most cases the inferior mapping technique from a performance standpoint,” and their own benchmark loading 35,000 rows across a 7-type hierarchy clocks TPT at roughly double the time and allocations of TPH. Their summary guidance is: use TPH unless you’ve measured a reason not to, use TPC when you mostly query one leaf type at a time, and “use TPT only if constrained to do so by external factors.”
So the recommended answer is “put it all in one table with a discriminator column.” Great! That’s the right answer. Now notice how much ceremony it took to get there, and how many ways there are to get it wrong: nullable sparse columns, HasValue<T>() registrations that have to be kept in sync with your class list, the IsComplete(false) escape hatch for when the database has discriminator values your model doesn’t know about, shared columns between siblings needing explicit HasColumnName() calls, and a migration every single time a subclass grows a property.
The Marten way, where the choice collapses
Marten stores documents (entities) as JSONB in PostgreSQL. There is no column per property, so there is no such thing as a sparse column, and no reason to ever spread a class hierarchy across multiple tables. The whole TPH/TPT/TPC decision tree just isn’t there. One table, one row per document, and a small discriminator column so Marten knows which concrete type to deserialize. Just get things done.
Here’s the entire configuration for the payment hierarchy above:
var store = DocumentStore.For(opts =>{ opts.Connection(connectionString); opts.Schema.For<Payment>() .AddSubClassHierarchy( typeof(CreditCardPayment), typeof(BankTransferPayment), typeof(CryptoPayment));});
That’s it. No OnModelCreating(), no IEntityTypeConfiguration<T>, no discriminator values to hand-maintain, no column mapping. Add a property to CryptoPayment next month and nothing in this configuration changes and there’s no migration, because the property just shows up in the JSON. If Payment were a concrete class you’d use the same call, and if you’d rather Marten found the subtypes for you, there’s a parameterless AddSubClassHierarchy() overload that scans the base type’s assembly (I’d use the explicit list in anything with a big assembly, but it’s there). You can read more on the document hierarchies page, including the multi-level case where you want to query against an intermediate type in the hierarchy.
What does the table actually look like? I ran this configuration against PostgreSQL 17 and pulled the shape straight out of information_schema:
column type nullableid uuid NOdata jsonb NOmt_last_modified timestamp with time zone YESmt_version uuid NOmt_dotnet_type character varying YESmt_doc_type character varying YES
Same table you’d get for any Marten document (see document storage and metadata), plus mt_doc_type, which is the discriminator. Marten derives the discriminator value from the type name, so the three rows I stored look like this:
mt_doc_type databank_transfer_payment {"Id": "...", "Amount": 1000.0, "BankName": "First Bank", "Currency": "USD", "AccountNumber": "123", ...}credit_card_payment {"Id": "...", "Amount": 250.0, "Currency": "USD", "LastFour": "4242", "CardHolderName": "Jeremy", ...}crypto_payment {"Id": "...", "Amount": 50.0, "Network": "ETH", "Currency": "USD", "WalletAddress": "0xabc", ...}
No NULL columns for the properties a subtype doesn’t have. Each row carries exactly the JSON for its own type. Marten also drops a btree index on mt_doc_type for you automatically, so narrowing to a subtype is cheap from day one.
One operational note: because the discriminator comes from the class name, renaming CryptoPayment to CryptocurrencyPayment would orphan the existing rows. If you think a subtype’s name might change, pin the alias explicitly with a MappedType and Marten will use that instead of the derived name:
opts.Schema.For<Payment>() .AddSubClassHierarchy( typeof(CreditCardPayment), typeof(BankTransferPayment), new MappedType(typeof(CryptoPayment), "crypto"));
How LINQ works against the hierarchy
The part I’m proudest of is that querying a hierarchy in Marten is just… querying. You query for the base type and get the whole family back as their concrete types, or you query for a subtype and get only that subtype. Here are the queries and the SQL Marten actually generated for them, lifted from ToCommand() on a real session:
// Every payment, deserialized to its concrete typevar all = await session.Query<Payment>().ToListAsync();// select d.id, d.data, d.mt_doc_type from public.mt_doc_payment as d;// Polymorphic filter on a base type propertyvar big = await session.Query<Payment>() .Where(x => x.Amount > 100) .ToListAsync();// select d.id, d.data, d.mt_doc_type from public.mt_doc_payment as d// where CAST(d.data ->> 'Amount' as decimal) > :p0;// Just the credit card paymentsvar cards = await session.Query<CreditCardPayment>().ToListAsync();// select d.id, d.data, d.mt_doc_type from public.mt_doc_payment as d// where (d.mt_doc_type = 'credit_card_payment');// A subtype query filtering on a subtype-only propertyvar mine = await session.Query<CreditCardPayment>() .Where(x => x.CardHolderName == "Jeremy") .ToListAsync();// select d.id, d.data, d.mt_doc_type from public.mt_doc_payment as d// where ((d.mt_doc_type = 'credit_card_payment') and d.data ->> 'CardHolderName' = :p0);
A few things to notice. Marten selects mt_doc_type alongside the JSON so it knows which concrete type to deserialize each row into, which is how Query<Payment>() hands you back a list with a CryptoPayment, a CreditCardPayment, and a BankTransferPayment in it. Querying for a subtype adds the discriminator predicate for you, and that predicate hits the automatic index. There’s no UNION ALL, no LEFT JOIN chain, and no “shared column” gymnastics, because the subtypes don’t have separate columns.
The same holds for loading by id. LoadAsync<Payment>(id) returns the concrete subtype, LoadAsync<CreditCardPayment>(id) returns the document if it’s a credit card payment, and LoadAsync<CryptoPayment>(sameId) returns null rather than mis-casting somebody’s card into a wallet.
If you need to filter on a subtype’s property while still querying for the base type, there’s a WhereSub<TSub>() operator for exactly that:
var eth = await session.Query<Payment>() .WhereSub<CryptoPayment>(x => x.Network == "ETH") .ToListAsync();// select d.id, d.data, d.mt_doc_type from public.mt_doc_payment as d// where d.data ->> 'Network' = :p0;
I’ll be honest that WhereSub() is a little bit of a specialty item. It’s really there for the case where you’re composing one query across the hierarchy and want to reach into a subtype’s members; if you just want crypto payments, Query<CryptoPayment>() is the better call. The full list of what Marten’s LINQ provider supports lives under querying with LINQ, and when you want to see what any query is going to do, ToCommand() or ExplainAsync() will show you.
Indexes, including partial indexes against a single subtype
This was a request from a JasperFx client (well, actually a pull request, but hey, that means that we support this going forward if there’s any issues with it).
Now for the part that I think makes the single table approach genuinely better in Marten than TPH is in a relational ORM, and not merely less work.
PostgreSQL supports partial indexes: an index with a WHERE clause, so it only covers the rows that match. Because every subtype in a Marten hierarchy lives in one table with a discriminator column, you can build an index that only covers one subtype, on a property that only that subtype has, and PostgreSQL will keep it tiny and use it when the query’s predicate lines up.
Marten exposes this through the Predicate option on a computed index, and on the index that comes with a duplicated field. Here’s the payment configuration again with two subtype-only indexes added:
opts.Schema.For<Payment>() .AddSubClassHierarchy( typeof(CreditCardPayment), typeof(BankTransferPayment), typeof(CryptoPayment)) // A computed index on Amount, but ONLY for credit card payments.// Bank transfers and crypto payments never enter this index. .Index(x => x.Amount, i => { i.Predicate = "mt_doc_type = 'credit_card_payment'"; }) // CardHolderName only exists on CreditCardPayment, so duplicate it// into its own column and index that column, again only for the// credit card rows. .Duplicate<CreditCardPayment>(x => x.CardHolderName, configure: i => i.Predicate = "mt_doc_type = 'credit_card_payment'");
That Duplicate<TSub>() overload is the thing to notice. The docs will tell you that searchable fields are declared on the top type of the hierarchy, and that’s true, but you declare them for a subtype by naming the subtype in the generic argument. Marten adds a card_holder_name column to the table, keeps it populated on write for credit card rows, and leaves it NULL for everybody else, which is exactly the one “sparse column” you actually asked for rather than one per property per subtype.
Here is the DDL Marten generated, straight from pg_indexes:
CREATEINDEX mt_doc_payment_idx_amount ON public.mt_doc_payment USING btree ((((data ->> 'Amount'::text))::numeric)) WHERE ((mt_doc_type)::text = 'credit_card_payment'::text);CREATEINDEX mt_doc_payment_idx_card_holder_name ON public.mt_doc_payment USING btree (card_holder_name) WHERE ((mt_doc_type)::text = 'credit_card_payment'::text);-- Marten adds this one on its own for any hierarchyCREATEINDEX mt_doc_payment_idx_mt_doc_type ON public.mt_doc_payment USING btree (mt_doc_type);
And because the LINQ provider adds the mt_doc_type = 'credit_card_payment' predicate to every Query<CreditCardPayment>() call, the generated query matches the partial index’s WHERE clause and PostgreSQL picks it up without any extra hinting on your part:
var mine = await session.Query<CreditCardPayment>() .Where(x => x.CardHolderName == "Jeremy") .ToListAsync();// select d.id, d.data, d.mt_doc_type from public.mt_doc_payment as d// where ((d.mt_doc_type = 'credit_card_payment') and d.card_holder_name = :p0);
Index Scan using mt_doc_payment_idx_card_holder_name on mt_doc_payment d Index Cond: ((card_holder_name)::text = 'Jeremy'::text)
Note that with the duplicated field in place, the generated SQL switched from data ->> 'CardHolderName' to the real card_holder_name column with no change to the LINQ. That’s the whole point of duplicated fields.
You can do the equivalent in EF Core, for what it’s worth. You’d add HasFilter("[PaymentType] = 'CreditCard'") to an HasIndex() call in your TPH configuration. The difference is that in EF Core you had to have already chosen TPH, already named the discriminator column and its values, and already accepted a nullable column for every subtype property, to earn the right to do it. In Marten it’s one option on an index you were probably going to declare anyway.
What this means for the table
Since this is all one table, everything else Marten does to a document table applies uniformly to the hierarchy:
- GIN indexes on the
datacolumn cover every subtype at once, which is a cheap way to get “good enough” querying across the whole family before you commit to targeted indexes. - Soft deletes, optimistic concurrency, and the metadata columns (last modified, causation, correlation, and so on) are configured once on the base type and apply to every subtype. You don’t configure deletes three times for three tables.
- Table partitioning and multi-tenancy work exactly as they do for any other document, because to PostgreSQL this is just a table.
- Growth is linear. Adding a fourth subtype is one more
typeof()in the configuration and zero schema changes, because the only new thing in the database is a newmt_doc_typevalue.
The honest tradeoffs are the same ones TPH carries anywhere. One hot table means autovacuum and bloat are shared across subtypes that may have very different write rates, and you can’t declare a database-level NOT NULL constraint on a subtype-only property (although Duplicate<TSub>() with notNull: true gets you partway there for the fields you care about). If two of the subtypes have nothing in common and are never queried together, they probably shouldn’t be a hierarchy in the first place, and Marten is perfectly happy to store them as two unrelated document types in two tables.
Summary
Marten collapses the table-per-whatever decision down to the one answer the relational ORMs end up recommending anyway, and then makes it close to free: one table, one line of configuration, no ORM mapping, LINQ that already knows about the discriminator, and partial indexes that let you tune for a single subtype without the rest of the hierarchy paying for it. Hierarchy storage has been in Marten since the very early days and I still think it’s one of the quieter wins of putting a document store on top of PostgreSQL.
Questions, war stories, or a hierarchy that doesn’t fit this shape? Find us in the Critter Stack Discord and we’ll talk it through.
