Is it possible to define relationships that could reference one of multiple tables without foreign keys?

[SoftCircuits]

I have the following entity.

public class Transfer
{
    public int Id { get; set; }

    public TransferType FromType { get; set; }
    public int FromId { get; set; }

    public TransferType ToType { get; set; }
    public int ToId { get; set; }

    public DateTime TimeStamp { get; set; }
    public double Quantity { get; set; }
}

The FromId and ToId properties are foreign keys to another table. However, they can refer to one of several different tables. The table referenced by the FromId property is determined by the FromType property. And the table referenced by the ToId property is determined by the ToType property. So, for example, if FromType is TransferType.Railcar, the FromId property contains the ID of a row in the Railcars table.

As a result, there is not an actual foreign key constraint defined in the database.

Is there any way to represent this in Entity Framework?

The ideal scenario would be if I could define a custom join to the Railcars table as FromType == TransferType.Railcar && FromId == r.Id.

Short of that, is there anyway to define multiple references to other tables and signify that they are based off the same column?

In the end, I have reasons why my primary goal is to be able to write the navigation fields without referencing DbContext.

[roji]

@SoftCircuits this bears some resemblence to TPC (or possibly TPT) hierarchy modeling, where the foreign key can reference any of several tables (but there's no additional column to say which one).

But in any case, no - EF navigations aren't compatible with your above scheme. You can of course perform the joining manually in your query by using the LINQ Join operator based on the value of FromType/ToType. Note that for this to be useful, you're likely modeling an inheritance hierarchy in the database; if the TransferTypes don't share any fields this wouldn't be very useful. At which point it begs the question of why the existing EF inheritance modeling strategies aren't good enough.

[SoftCircuits]

@roji Thanks.

I'm not modeling an inheritance hierarchy. I have several objects (tables) like Railcars, Trucks, Storage, Ships, etc. and am using the Transfers to model product being transferred between them. And the entries in the Transfers table are used to calculate the current amount of product in any of the objects.

It was becoming cumbersome to have RailcarTrucks, RailcarShips, StorageRailcars, StorageShips, ShipTrucks, etc. joining tables.

So the FromId and ToId columns reference the two objects product is being moved between, which could be one of several different objects (tables).

Unfortunately, I've ran into issue after issue with this approach. At this point, I'd be pretty happy if there was a way to set it up so that I could write an expression that could get the related table without needing a reference to DbContext, like I need now if I use a join.

[TonyValenti]

I do something similar in my code.
I have an owned type called "PolymorphicReference" that has an ID and a string Type. I then have a ResolvePolymorphicReference method that inspects the Type and then queries the appropriate table.

I personally prefer not using joins because it complicates the caching story.

I've seen this approach used in many apps on the Rails stack and it seems to work really well. It works great for me too.

[roji]

@TonyValenti that sounds like it needs to do an extra roundtrip to the database just to get the type and query the appropriate related table (roundtrips can have a considerable perf impact); if dependents also have such polymorphic dependents, that's another roundtrip etc.

In any case, this discussion looks like a duplicate of #7623.

[roji]

Duplicate of #7623

[SoftCircuits]

@TonyValenti

I do something similar in my code. I have an owned type called "PolymorphicReference" that has an ID and a string Type. I then have a ResolvePolymorphicReference method that inspects the Type and then queries the appropriate table.

Thanks, but I'm looking for ways to simplify my query. A new C# method would not be translatable to SQL and so it seems that would significantly complicate things.

[SoftCircuits]

@roji Can I just ask this? Is there any way to define a navigation property without creating a foreign key constraint in the database?

[roji]

@SoftCircuits navigation properties aren't conceptually coupled to a constraint in the database. For example, you can use EF against a database that wasn't at all created with EF, and which has no constraints. EF would generate the same SQL - including the same JOINs; although nothing in the database enforces the referential integrity (since no constraint is defined), everything will generally work the same.

However, the concept of a navigation is coupled to the concept of a foreign key; that is, to some value stored in a column that is identical to a key somewhere else.

[SoftCircuits]

@roji Yes, I wonder if there would be utility in allowing EF to define navigation properties without creating those foreign keys.

Then you could have something like the following where VehicleId contains the primary key of one of several different tables. This column has no foreign key because it isn't tied to a single table. And the navigation properties (Car, Truck and Motocycle) could be used to "loosely" reference a row in the joined table using VehicleId.

Of course, they could be null, and code would need to be written to determine which one is valid.

class Person
{
    public int VehicleId { get; set; }

    public Car Car { get; set; }
    public Truck Truck { get; set; }
    public Motorcycle Motorcycle { get; set; }
}

[roji]

How do you propose EF decide which table to JOIN on? Something needs to say whether VehicleId points to a car, truck or motorcycle.

[SoftCircuits]

@roji Based on the property I reference.

If I reference Person.Car, a SQL expression that uses the VehicleId to find the matching row in the Cars table would be generated.

As I described before, some responsibility is assumed by the developer. While another column would likely be used to indicate which table is referenced, this would be checked by the developer and not EF. But Person.Car, Person.Truck and Person.Motorcycle could all potentially be used in the same expression. Each one would be used to create a join to the corresponding table, even though that wouldn't make sense to write it that way.

Perhaps these properties should always be considered nullable because there is no constraint to ensure they exist.

[roji]

Based on the property I reference.

I don't think that's a great design: it means that the user needs to know in advance - for a specific Person - which type of related vehicle it has. Since the user can only know that by looking at some other column in the Person row (VehicleType), at the very least this involves an extra roundtrip (once to fetch the Person and its vehicle type, another to fetch the vehicle). If the extra query and roundtrip are acceptable, then there seems to be little value here over what you can already do today: fetch the Person, and then fetch the related vehicle in a separate EF query based on the vehicle ID and type loaded in the first query. In this version of the feature, EF would be providing pretty thin sugar here, which I don't think would justify the feature.

As a more valuable alternative, EF could be made aware of the vehicle type as a "discriminator", allowing it to load both the Person and the related vehicle in a single query (and roundtrip). Although better from a roundtrips perspective, the single query would be complex, as it would need to join the right table based on the vehicle type etc.; the optimal SQL and its performance would need to be carefully analyzed (across databases) before something like this would make sense.

In short, polymorphic relationships is quite an open-ended field where many things are possible, and different users may want/need different things. We'd need to carefully design here and work out exactly what we want to provide; nothing's impossible technically, but there would a lot to think about. That's what #7623 generally tracks.

[SoftCircuits]

@roji I don't believe it would require another round trip. A single SQL expression could be used that does the join and also checks another column.

var people = DbContext.Persons
    .Where(p => p.VehicleType == VehicleType.Truck)
    .Select(p => p.Truck);

It does put more responsibility on the developer, but no more than having to do all these joins manually using a separate reference to the DbContext and related table.

I hit two roadblocks trying to work through my current task due to the fact that I needed another reference to DbContext, in addition to the queries being quite a bit harder to write.

If it was possible to have EF aware of VehicleType and join accordingly, that could be useful. But I think this is where we start getting into many variations that could run into problems.

[roji]

@SoftCircuits that query above makes sense only if you very specifically want to get all people who have trucks along with their trucks. It doesn't work if you want to get some Person (e.g. whose name you have but nothing else), along with their vehicle of the correct kind.

In any case, if the query above is what you want, then what else do you need from EF? It seems like you can already write precisely what you want as a LINQ query etc.

I hit two roadblocks trying to work through my current task due to the fact that I needed another reference to DbContext, in addition to the queries being quite a bit harder to write.

I don't really understand what this means or what it has to do with what we've been discussing here.

[SoftCircuits]

@roji

I don't really understand what this means or what it has to do with what we've been discussing here.

I've twice run hit roadblocks needing a second reference to DbContext, as shown in the select clause below.

var people = DbContext.Persons
    .Where(p => p.VehicleType == VehicleType.Truck)
    .Select(p => new
    {
        p.Name,
        Truck = DbContext.Trucks
            .Where(t => t.Id == p.VehicleId)
            .Single()
    });

1. In one case, I have a lookup table of Func<IQueryable<T>, IOrderedQueryable<T>>s that specify how to sort the results. Each Func<T> takes an IQueryable<T> and appends an OrderBy() clause. But this all needs to be reworked to sort on the Truck column above because the Func<T> doesn't have a reference to DbContext.

2. Sorry, I have another case, but the explanation is too involved, and I doubt it would be of much interest.

Both of these problems would go away if there was a way to use a navigation property instead of a reference to DbContext, in addition to being more concise and easier to write.

[SoftCircuits]

@roji Anyway, I don't think I'm getting any traction on the specifics of what I'm saying. Certainly, some support for implied foreign keys like this would be a welcome addition. I will follow the related issue.

[roji]

@SoftCircuits just to put some summary points from my point of view:

• If the goal is to have Car/Truck/Motorcycle navigation properties on Person, where every person has only vehicle, then it's perfectly reasonable to simply have three regular option navigations, each with its own foreign key; you can use a check constraint to enforce that only one of the 3 foreign keys is non-null for each given row. That is, all the complexity here comes from wanting to share the same foreign key column rather than having three - consider whether that's really that important (any why).
• If it's really important, you can probably manage sharing the same foreign key across the three navigations, simply by dropping the foreign key constraint (by tweaking the created migration). I can't guarantee there won't be any hurdles (we haven't thoroughly tested or thought about this kind of scenario), but it may work well.
• Importantly, the sort of example you've given (Person with Car/Truck/Motorcycle) is exactly what EF's inheritance modeling was developed for, and you should be able to use TPC successfully for this (assuming a separate table for each type is desired). I still haven't quite understood exactly why you don't want to use TPC.
• Finally, above you're mainly proposing solutions that to the very restricted set of problems you currently have, but we have to think more generally when considering adding a 1st-class feature to EF etc. That's what Support Polymorphic Relationships #7623 tracks.

I think the discussion here has run its course, but of course feel free to respond to any of the points above and I'll do my best to answer. For now I'll go ahead and close this as a duplicate.