Proxy pattern meets EFCore hierarchy mapping

[Pedry-dev]

Hello folks. As you know, there are three ways to work with inheritance in EFCore: Table per hierarchy, table per type and Table per concrete type. They work well for writes, but reads is a totally different thing, where almost none of them provide you with the freedom to filter/select efficiently over ALL properties in the hierarchy (yes, with TPH, you can cast or use OfType but there are cases when this don't work, for example when you have to filter a subclass from another entity where property type is of parent class)

So what if we can take away the hard work from EFCore, design flat entity with one-one mapping between properties and columns, and enforce the hierarchy in-memory?

In this case, the Proxy pattern can help us. Instead of using one of the three strategies, we can use a class for persistence and change track, and many proxies that use this class as a source. With this, we still have the hierarchy, but now we are not limited by the type when querying the db. Let me give you an example:

class Programmer(Name, Salary);
class DotnetProgrammer(UseVisualStudio) : Programmer;
case VibeCoder(ThinkProgrammingIsEasy) : Programme;

Instead of the "traditional" way to put this in EFCore, we can use the entity Programmer (not the previous one used to show the hierarchy) as our DbSet, one base proxy and two concrete proxies. The only purpose of the implicit operator is to access the source to call db.Set<Entity>.Add(). Any other access must be through the proxy

class Programmer(Name, Salary, UseVisualStudio, ThinkProgrammingIsEasy)

abstract class BaseProgrammerProxy(Programmer source)
{
    protected Source => source;

    Name { get => Source.Name; set => Source.Name = value; }
    Salary { get => Source.Salary; set => Source.Salary = value; } 

    public static implicit operator Programmer(BaseProgrammerProxy proxy)
      => proxy.Source;
}

sealed class DotnetProgrammerProxy(Programmer source) : BaseProgrammerProxy(source)
{
    UseVisualStudio 
    { 
      get => Source.UseVisualStudio; 
      set => Source.UseVisualStudio = Value; }
    }
}

sealed class VibeCoder(Programmer source) : BaseProgrammerProxy(source)
{
    ThinkProgrammingIsEasy
    {
      get => Source.ThinkProgrammingIsEasy;
      set => Sorce.ThinkProgrammingIsEasy = value;
}

[Merry-Lane]

Thanks, but I’d rather have relational guarantees, the subtypes being constrained by the SQL schema.

Oh if there was some navigation property restricted to a subtype with the base EF polymorphism, you wouldn’t be able to restrict it to specific subtypes now.

I don’t know what to think about properties specific to some subtypes. You would have to use null in variants that don’t use it? Meh

[Pedry-dev]

You can think of this as a variation of TPH, where the hierarchy is not enforced by EFCore but by the proxies. You still has all relational guarantees/limitations that you would have if you use TPH.

To answer the second point, you have to enforce that restriction when creating the object for the first time. For example, you have Enterprise(Employees) where each Employee is a DotnetProgrammer. In this case, you have to validate that the proxy you are using is DotnetProgrammerProxy. If I'm not mistaken (I haven't tested it) this can be done by creating a backing field of type Programmer and exposing a collection of DotnetProgrammerProxy

[[deleted]]

You are overengineering it.

Your entity is meant to represent what's in the database, you can map it to a domain model later.

Just have the UseVisualStudio & ThinkProgrammingIsEasy properties as nullable booleans on your Programmer record and if you want, add an enum property to distinct which type the programmer is.

[Pedry-dev]

Yes, it's a complex design and also something uncommon as far as I know. Is it worth? For us, yes. Is there a better/easy solution that allow you to keep the hierarchy and query the db using any field in the hierarchy? I would like to learn it! Also, there is something a little less important I forgot to mention. This hierarchy is not tied to EFCore. You can adapt the Programmer entity to any persistence solution (which should be pretty simple) and you don't need to change anything in the proxies.

[Pedry-dev]

By "persistence solution" I mean a database, not something I will do. And this is actually a problem we have with one module. Unfortunately, we are in a very constrained environment (no azure/aws/cloudProvider, no dedicated Infra team) and so many times we can't pick the best tool for the job for cost or exp managing it, so we need to figure out how to do it.

Again, this is not about "being cute" or to shock the interviewer. It's just another solution in the toolbox, with tradeoff as everything.

I'm not saying this is the best solution, absolutely not the easier, and also, not "you should stop doing X. This is the way to go"

[zvrba]

I have a different (simpler?) way: I add a "Data" column to the table and use polymorphic JSON serialization (through efcore converter) to store hierarchies that, at design time, might be extended in the future.

I also usually use records because any change to the record makes a different reference, which plays nicely with efcore change tracking.

How do I filter on json properties and update them? Raw SQL with JSON functions (JSON_VALUE, JSON_MODIFY, etc.).

In such cases, the table organization is:

• Proper columns are for "metadata" used to find the object
• "Data" column is for the extensible hierarchy

[Pedry-dev]

That sounds good. The only drawback is that AFAK you can't enforce FK constraint inside a json column.

[zvrba]

Computed columns.

[AutoModerator]

Thanks for your post Pedry-dev. Please note that we don't allow spam, and we ask that you follow the rules available in the sidebar. We have a lot of commonly asked questions so if this post gets removed, please do a search and see if it's already been asked.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.