Querying a table that relates to multiple entity types

Question

So currently I have an application model of:

Note -> Thing

(A note can relate to many things)

A thing can be numerous entities (for this example lets use Computer & Car)

e.g

Note -> Computer
     -> Car

So right now, I have the schema of

Note -> ComputerNote -> Computer
Note -> CarNote      -> Car

The problem is that because the entity links are in separate tables, it requires a new query to be written rather than just using filtering in the WHERE clause.

Ideally it would be nice to have an EntityId & EntityTypeId column & on the Note table that would hold the primary key of the related entity and the type of the entity. Thus application logic could look for all Car notes where the car is x without a separate query for each type, but.. this would mean I lose referential integrity. Is there a better way, or is what I have suggested an acceptable design?

Entity Framework Model's:

public partial class Note
{
    public Note()
    {
        NoteComputer = new HashSet<NoteComputer>();
        NoteCar = new HashSet<NoteCar>();
        NoteThing = new HashSet<NoteThing>();
    }

    public int Id { get; set; }
    public string Value { get; set; }
    public string CreatedByUserId { get; set; }
    public DateTime CreatedDateTime { get; set; }

    public ICollection<NoteComputer> NoteComputer { get; set; }
    public ICollection<NoteCar> NoteCar { get; set; }
    public ICollection<NoteThing> NoteThing { get; set; }

}

public partial class NoteCar
{
    public int Id { get; set; }
    public int NoteId { get; set; }
    public int CarId { get; set; }

    public Car Car { get; set; }
    public Note Note { get; set; }
}

public partial class NoteComputer
{
    public int Id { get; set; }
    public int NoteId { get; set; }
    public int ComputerId { get; set; }

    public Computer Computer { get; set; }
    public Note Note { get; set; }
}

public partial class NoteThing
{
    public int Id { get; set; }
    public int NoteId { get; set; }
    public int ThingId { get; set; }

    public Thing Thing { get; set; }
    public Note Note { get; set; }
}

Can you show your actual models? It's a little unclear what you have here. — DavidG, Jun 24 '19 at 14:06
I've updated the question with what the entity framework models would look like for one entity type. Note that this question is more related to the database design rather than entity framework although there may be a solution to my problem at the application layer. — Kieran Devlin, Jun 24 '19 at 14:11
`Thing` is an abstract entity to explain the problem. A `Car` is a `Thing`, A `Computer` is a `Thing` etc. — Kieran Devlin, Jun 24 '19 at 14:30
You may include `Thing` as a real entity , implement `IS_A` relationship and `Thing-Note` relationship — Serg, Jun 24 '19 at 14:32
I've added `Thing` into the example although it shouldn't make a difference as `Thing` is the abstract term i'm using to describe any entity that relates to a `Note` e.g `Car`, `Computer`. The problem is based on how the relationships are stored in separate tables thus multiple queries are needed to query multiple relationship types. — Kieran Devlin, Jun 24 '19 at 14:41
This is called *polymorphic associations* and it's generally seen as an anti-pattern. I know [from experience](https://stackoverflow.com/q/13953675/861716) that your proposed implementation of the pattern can only be realized with EF database-first, i.e. EDMX, which will soon be deprecated. Other implementations [arent't nice either](https://stackoverflow.com/q/8895806/861716). Separate tables is a normalized solution in line with the relational database paradigm. Other solution fight the paradigm. — Gert Arnold, Jun 24 '19 at 14:47
For reading you can always use a view that unions all Notes. As an indexed view it can even be optimized for search queries. — Gert Arnold, Jun 24 '19 at 14:49
@GertArnold Did you manage to get your code first implementation to work? Although the EF models were generated database first, EF Core has the option to scaffold using data attributes on the models. — Kieran Devlin, Jun 24 '19 at 14:56
In code-first only by having a separate nullable FK for each "Thing" entity. Doable, but ugly. — Gert Arnold, Jun 24 '19 at 19:30
Yeah after some thought I think I'm going to stick with the concrete tables and use entity framework with some voodoo expression magic to dynamically join the relative table on. Its not as nice as one simple query with some variables in the filter, but it should work. — Kieran Devlin, Jun 24 '19 at 19:35

Kieran Devlin · Accepted Answer · 2019-11-10T01:00:59.150

As there seems to be no nice way to handle this at the database level, I've found it best to handle this at the application level using the concrete type database schema, to produce dynamic joins. Example in Entity Framework Core:

public GenericEntityProvider 
{
    private readonly IEnumerable<IEntityProvider> _entityProviders;
    private readonly DatabaseContext _context;

    public GenericEntityProvider(IEnumerable<IEntityProvider> entityProviders, DatabaseContext context) 
    {
        _entityProviders = entityProviders;
        _context = context;
    }

    public IEnumerable<Note> Get(Type type, int id) {
        var provider = _entityProviders.GetPredicate(type, id);
        return _context.Notes.Where(provider);
    }

}

public CarNoteProvider : IEntityProvider 
{
    public Expression<Func<Note, bool>> GetPredicate(Type type, int id) 
    {
        return x => x.CarNote.Any(cn => cn.CarId == id);
    }
}

Querying a table that relates to multiple entity types

1 Answers1