Proper method of navigation to generically-defined entity

Question

New bounty 2017/10/31

The auto-accepted answer, unfortunately, does not work with my current entity model due to TPC limitations. I am in dire need of finding a way to facilitate two-way navigation via interface or abstract class, so I am starting another bounty.

Note that I must use existing model design, so refactoring is not an option.

Original question below

I have an parent entity that has a one-to-one relationship to multiple possible tables (FK is on child tables). Because the navigation property to the child is defined by an interface, I have no navigation to the other end of the relationship.

I understand that this is a natural limitation, but still seek a means to achieve two-way navigation while using abstract types or generics. I've come across a number of questions similar to what I want to do, but they are either quite old or I don't think they are an exact match for what I am trying to achieve. I seek a more current answer that's specific to my dilemma.

This is my code and can easily be copied/pasted into a test app:

Edit (in response to Ivan Stoev's answer): When I tried implementing your solution, I got this error when trying to create migration:

The association 'SoftwareApplicationData_CreatedBy' between entity types 'SoftwareApplicationData' and 'AppUser' is invalid. In a TPC hierarchy independent associations are only allowed on the most derived types.

So it appears that I need to edit my original code to reflect the more complex model which I originally omitted for brevity. My apologies as I didn't think the additional code would be relevant until now.

Notice that I made all entities now inherit from MyEntity.

End edit

public abstract class MyEntity
{
    public int Id { get; set; }

    public AppUser CreatedBy { get; set; }
}

public class AppUser : MyEntity { }

public interface ISoftwareApplicationData
{
    SoftwareApplicationBase Application { get; set; }
}

//Parent entity representing a system installation and the software installed on it.
//The collection property is *not* the generic entity I mentioned earlier.
public class SystemConfiguration : MyEntity
{
    public ICollection<SoftwareApplicationBase> Applications { get; set; }
}

//Represents the software itself. Has other generic attributes that I've ommitted for brevity.
//The Data property represents additional, application-specific attributes. I need to be able
//to navigate from SoftwareApplicationBase to whatever may be on the other end
public class SoftwareApplicationBase : MyEntity
{
    public SystemConfiguration Configuration { get; set; }

    public string ApplicationName { get; set; }

    public ISoftwareApplicationData Data { get; set; }
}

//This is a generic, catch-all application class that follows a basic Application/Version
//convention. Most software will use this class
public class SoftwareApplication : MyEntity, ISoftwareApplicationData
{
    public SoftwareApplicationBase Application { get; set; }

    public string Version { get; set; }
}

//Operating systems have special attributes, so they get their own class.
public class OperatingSystem : MyEntity, ISoftwareApplicationData
{
    public SoftwareApplicationBase Application { get; set; }

    public string Version { get; set; }

    public string ServicePack { get; set; }
}

//Yet another type of software with its own distinct attributes
public class VideoGame : MyEntity, ISoftwareApplicationData
{
    public SoftwareApplicationBase Application { get; set; }

    public string Publisher { get; set; }

    public string Genre { get; set; }
}

One solution I have in mind is to create a method that will pass a GetById delegate to a collection of repos of entities that implement ISoftwareApplicationData. I don't like the idea of doing a GetById within iterations, but there will probably only ever be five types for which I need to do this, so it's a tenable solution failing all else.

Answer 1

Because the navigation property to the child is defined by an interface, I have no navigation to the other end of the relationship.

I understand that this is a natural limitation, but still seek a means to achieve navigation while using abstract types or generics.

The main problem in this design is the interface since EF works only with classes. But if you can replace it with abstract class, and if the FK in the child tables are also PK (i.e. follow the Shared Primary Key Asociation pattern for representing one-to-one relationship), then you can use EF Table per Concrete Type (TPC) inheritance strategy to map the existing child tables, which in turn would allow EF providing the desired navigation automatically for you.

Here is the sample modified model (excluding ISoftwareApplicationBase and SystemConfiguration which are irrelevant):

public class SoftwareApplicationBase
{
    public int Id { get; set; }
    public string ApplicationName { get; set; }
    public SoftwareApplicationData Data { get; set; }
}

public abstract class SoftwareApplicationData
{
    public int ApplicationId { get; set; }
    public SoftwareApplicationBase Application { get; set; }
}

public class SoftwareApplication : SoftwareApplicationData
{
    public string Version { get; set; }
}

public class OperatingSystem : SoftwareApplicationData
{
    public string Version { get; set; }
    public string ServicePack { get; set; }
}

public class VideoGame : SoftwareApplicationData
{
    public string Publisher { get; set; }
    public string Genre { get; set; }
}

configuration:

modelBuilder.Entity<SoftwareApplicationBase>()
    .HasOptional(e => e.Data)
    .WithRequired(e => e.Application);

modelBuilder.Entity<SoftwareApplicationData>()
    .HasKey(e => e.ApplicationId);

modelBuilder.Entity<SoftwareApplication>()
    .Map(m => m.MapInheritedProperties().ToTable("SoftwareApplication"));

modelBuilder.Entity<OperatingSystem>()
    .Map(m => m.MapInheritedProperties().ToTable("OperatingSystem"));

modelBuilder.Entity<VideoGame>()
    .Map(m => m.MapInheritedProperties().ToTable("VideoGame"));

Generated tables and relationships:

CreateTable(
    "dbo.SoftwareApplicationBase",
    c => new
        {
            Id = c.Int(nullable: false, identity: true),
            ApplicationName = c.String(),
        })
    .PrimaryKey(t => t.Id);

CreateTable(
    "dbo.SoftwareApplication",
    c => new
        {
            ApplicationId = c.Int(nullable: false),
            Version = c.String(),
        })
    .PrimaryKey(t => t.ApplicationId)
    .ForeignKey("dbo.SoftwareApplicationBase", t => t.ApplicationId)
    .Index(t => t.ApplicationId);

CreateTable(
    "dbo.OperatingSystem",
    c => new
        {
            ApplicationId = c.Int(nullable: false),
            Version = c.String(),
            ServicePack = c.String(),
        })
    .PrimaryKey(t => t.ApplicationId)
    .ForeignKey("dbo.SoftwareApplicationBase", t => t.ApplicationId)
    .Index(t => t.ApplicationId);

CreateTable(
    "dbo.VideoGame",
    c => new
        {
            ApplicationId = c.Int(nullable: false),
            Publisher = c.String(),
            Genre = c.String(),
        })
    .PrimaryKey(t => t.ApplicationId)
    .ForeignKey("dbo.SoftwareApplicationBase", t => t.ApplicationId)
    .Index(t => t.ApplicationId);

Navigation test:

var test = db.Set<SoftwareApplicationBase>()
    .Include(e => e.Data)
    .ToList();

EF generated SQL query from the above:

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[ApplicationName] AS [ApplicationName],
    CASE WHEN ([UnionAll4].[ApplicationId] IS NULL) THEN CAST(NULL AS varchar(1)) WHEN ([UnionAll4].[C5] = 1) THEN '2X0X' WHEN ([UnionAll4].[C6] = 1) THEN '2X1X' ELSE '2X2X' END AS [C1],
    [UnionAll4].[ApplicationId] AS [C2],
    CASE WHEN ([UnionAll4].[ApplicationId] IS NULL) THEN CAST(NULL AS varchar(1)) WHEN ([UnionAll4].[C5] = 1) THEN [UnionAll4].[C1] WHEN ([UnionAll4].[C6] = 1) THEN CAST(NULL AS varchar(1)) END AS [C3],
    CASE WHEN ([UnionAll4].[ApplicationId] IS NULL) THEN CAST(NULL AS varchar(1)) WHEN ([UnionAll4].[C5] = 1) THEN [UnionAll4].[C2] WHEN ([UnionAll4].[C6] = 1) THEN CAST(NULL AS varchar(1)) END AS [C4],
    CASE WHEN ([UnionAll4].[ApplicationId] IS NULL) THEN CAST(NULL AS varchar(1)) WHEN ([UnionAll4].[C5] = 1) THEN CAST(NULL AS varchar(1)) WHEN ([UnionAll4].[C6] = 1) THEN [UnionAll4].[Version] END AS [C5],
    CASE WHEN ([UnionAll4].[ApplicationId] IS NULL) THEN CAST(NULL AS varchar(1)) WHEN ([UnionAll4].[C5] = 1) THEN CAST(NULL AS varchar(1)) WHEN ([UnionAll4].[C6] = 1) THEN CAST(NULL AS varchar(1)) ELSE [UnionAll4].[C3] END AS [C6],
    CASE WHEN ([UnionAll4].[ApplicationId] IS NULL) THEN CAST(NULL AS varchar(1)) WHEN ([UnionAll4].[C5] = 1) THEN CAST(NULL AS varchar(1)) WHEN ([UnionAll4].[C6] = 1) THEN CAST(NULL AS varchar(1)) ELSE [UnionAll4].[C4] END AS [C7]
    FROM   [dbo].[SoftwareApplicationBase] AS [Extent1]
    LEFT OUTER JOIN  (SELECT
        [Extent2].[ApplicationId] AS [ApplicationId]
        FROM [dbo].[SoftwareApplication] AS [Extent2]
    UNION ALL
        SELECT
        [Extent3].[ApplicationId] AS [ApplicationId]
        FROM [dbo].[VideoGame] AS [Extent3]
    UNION ALL
        SELECT
        [Extent4].[ApplicationId] AS [ApplicationId]
        FROM [dbo].[OperatingSystem] AS [Extent4]) AS [UnionAll2] ON [Extent1].[Id] = [UnionAll2].[ApplicationId]
    LEFT OUTER JOIN  (SELECT
        [Extent5].[ApplicationId] AS [ApplicationId],
        CAST(NULL AS varchar(1)) AS [C1],
        CAST(NULL AS varchar(1)) AS [C2],
        [Extent5].[Version] AS [Version],
        CAST(NULL AS varchar(1)) AS [C3],
        CAST(NULL AS varchar(1)) AS [C4],
        cast(0 as bit) AS [C5],
        cast(1 as bit) AS [C6]
        FROM [dbo].[SoftwareApplication] AS [Extent5]
    UNION ALL
        SELECT
        [Extent6].[ApplicationId] AS [ApplicationId],
        CAST(NULL AS varchar(1)) AS [C1],
        CAST(NULL AS varchar(1)) AS [C2],
        CAST(NULL AS varchar(1)) AS [C3],
        [Extent6].[Publisher] AS [Publisher],
        [Extent6].[Genre] AS [Genre],
        cast(0 as bit) AS [C4],
        cast(0 as bit) AS [C5]
        FROM [dbo].[VideoGame] AS [Extent6]
    UNION ALL
        SELECT
        [Extent7].[ApplicationId] AS [ApplicationId],
        [Extent7].[Version] AS [Version],
        [Extent7].[ServicePack] AS [ServicePack],
        CAST(NULL AS varchar(1)) AS [C1],
        CAST(NULL AS varchar(1)) AS [C2],
        CAST(NULL AS varchar(1)) AS [C3],
        cast(1 as bit) AS [C4],
        cast(0 as bit) AS [C5]
        FROM [dbo].[OperatingSystem] AS [Extent7]) AS [UnionAll4] ON [Extent1].[Id] = [UnionAll4].[ApplicationId]

Not the best looking, but does the dirty work for you :)

Edit: MyEntity base class and the requirement that every entity class must inherit from it highly limits the options. TPC is no more applicable because of the relationship defining navigation property inside the base class (another EF limitation). Hence the only viable automatic EF option is to use some of the other two EF inheritance strategies, but they require changing the database structure.

In case you can afford introducing intermediate table holding the common SoftwareApplicationData properties and relationships, you can utilize the Table Per Type (TPT) strategy as follows:

Model:

public class SoftwareApplicationBase : MyEntity
{
    public string ApplicationName { get; set; }
    public SoftwareApplicationData Data { get; set; }
}

public abstract class SoftwareApplicationData : MyEntity
{
    public SoftwareApplicationBase Application { get; set; }
}

public class SoftwareApplication : SoftwareApplicationData
{
    public string Version { get; set; }
}

public class OperatingSystem : SoftwareApplicationData
{
    public string Version { get; set; }
    public string ServicePack { get; set; }
}

public class VideoGame : SoftwareApplicationData
{
    public string Publisher { get; set; }
    public string Genre { get; set; }
}

Configuration:

modelBuilder.Entity<SoftwareApplicationBase>()
    .HasOptional(e => e.Data)
    .WithRequired(e => e.Application);

modelBuilder.Entity<SoftwareApplicationData>()
    .ToTable("SoftwareApplicationData");

modelBuilder.Entity<SoftwareApplication>()
    .ToTable("SoftwareApplication");

modelBuilder.Entity<OperatingSystem>()
    .ToTable("OperatingSystem");

modelBuilder.Entity<VideoGame>()
    .ToTable("VideoGame");

Relevant tables:

CreateTable(
    "dbo.SoftwareApplicationData",
    c => new
        {
            Id = c.Int(nullable: false),
            CreatedBy_Id = c.Int(),
        })
    .PrimaryKey(t => t.Id)
    .ForeignKey("dbo.AppUser", t => t.CreatedBy_Id)
    .ForeignKey("dbo.SoftwareApplicationBase", t => t.Id)
    .Index(t => t.Id)
    .Index(t => t.CreatedBy_Id);

CreateTable(
    "dbo.SoftwareApplication",
    c => new
        {
            Id = c.Int(nullable: false),
            Version = c.String(),
        })
    .PrimaryKey(t => t.Id)
    .ForeignKey("dbo.SoftwareApplicationData", t => t.Id)
    .Index(t => t.Id);

CreateTable(
    "dbo.OperatingSystem",
    c => new
        {
            Id = c.Int(nullable: false),
            Version = c.String(),
            ServicePack = c.String(),
        })
    .PrimaryKey(t => t.Id)
    .ForeignKey("dbo.SoftwareApplicationData", t => t.Id)
    .Index(t => t.Id);

CreateTable(
    "dbo.VideoGame",
    c => new
        {
            Id = c.Int(nullable: false),
            Publisher = c.String(),
            Genre = c.String(),
        })
    .PrimaryKey(t => t.Id)
    .ForeignKey("dbo.SoftwareApplicationData", t => t.Id)
    .Index(t => t.Id);

The desired navigation is as before, with the bonus allowing eager loading of the base navigation properties:

var test = db.Set<SoftwareApplicationBase>()
    .Include(e => e.Data)
    .Include(e => e.Data.CreatedBy)
    .ToList();

To recap, the only way to get automatic navigation in EF is to use abstract class and EF inheritance, with the corresponding constraints. If none of them is applicable in your scenario, you have to resort to custom code handling options similar to the one mentioned at the end of the question.