導航到通用定義實體的正確方法

October 31, 2017

新賞金 2017/10/31
不幸的是，由於 TPC 的限制，自動接受的答案不適用於我目前的實體模型。我迫切需要找到一種通過介面或抽像類促進雙嚮導航的方法，所以我開始另一個賞金。
請注意，我必須使用現有的模型設計，因此重構不是一種選擇。
下面的原始問題
我有一個與多個可能的表具有一對一關係的父實體（FK 在子表上）。因為孩子的導航屬性是由介面定義的，所以我沒有導航到關係的另一端。
我知道這是一個自然的限制，但仍然尋求一種在使用抽像類型或泛型時實現雙嚮導航的方法。我遇到了許多與我想做的類似的問題，但它們要麼很老，要麼我認為它們與我想要實現的目標不完全匹配。我尋求一個針對我的困境的更目前的答案。
這是我的程式碼，可以輕鬆複製/粘貼到測試應用程序中：
編輯（響應 Ivan Stoev 的回答）：當我嘗試實施您的解決方案時，在嘗試創建遷移時出現此錯誤：
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.
所以看來我需要編輯我的原始程式碼以反映我最初為了簡潔而省略的更複雜的模型。我很抱歉，因為直到現在我才認為附加程式碼是相關的。
請注意，我現在讓所有實體都繼承自MyEntity.
結束編輯
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&lt;SoftwareApplicationBase&gt; 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; }
}
我想到的一個解決方案是創建一個方法，它將 GetById 委託傳遞給實現ISoftwareApplicationData. 我不喜歡在迭代中執行 GetById 的想法，但可能只有五種類型我需要執行此操作，因此這是一個可行的解決方案，其他所有方法都失敗了。

因為孩子的導航屬性是由介面定義的，所以我沒有導航到關係的另一端。
我知道這是一個自然的限制，但仍然在使用抽像類型或泛型時尋求一種實現導航的方法。

此設計中的主要問題是介面，因為 EF 僅適用於類。但是如果你可以用abstract class替換它，並且如果子表中的 FK 也是 PK （即遵循共享主鍵關聯模式來表示一對一關係），那麼你可以使用 EF Table per Concrete Type ( TPC）繼承策略來映射現有的子表，這反過來又允許 EF 自動為您提供所需的導航。

這是範例修改模型（排除ISoftwareApplicationBase和SystemConfiguration不相關的）：

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; }
}

配置：

modelBuilder.Entity&lt;SoftwareApplicationBase&gt;()
   .HasOptional(e =&gt; e.Data)
   .WithRequired(e =&gt; e.Application);

modelBuilder.Entity&lt;SoftwareApplicationData&gt;()
   .HasKey(e =&gt; e.ApplicationId);

modelBuilder.Entity&lt;SoftwareApplication&gt;()
   .Map(m =&gt; m.MapInheritedProperties().ToTable("SoftwareApplication"));

modelBuilder.Entity&lt;OperatingSystem&gt;()
   .Map(m =&gt; m.MapInheritedProperties().ToTable("OperatingSystem"));

modelBuilder.Entity&lt;VideoGame&gt;()
   .Map(m =&gt; m.MapInheritedProperties().ToTable("VideoGame"));

生成的表和關係：

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

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

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

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

導航測試：

var test = db.Set&lt;SoftwareApplicationBase&gt;()
   .Include(e =&gt; e.Data)
   .ToList();

EF 從上面生成的 SQL 查詢：

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]

不是最好看，但對你來說臟活:)

編輯： MyEntity基類和每個實體類必須從它繼承的要求高度限制了選項。由於在基類中定義導航屬性的關係（另一個 EF 限制），TPC 不再適用。因此，唯一可行的自動 EF 選項是使用其他兩種 EF 繼承策略中的一些，但它們需要更改數據庫結構。

如果您有能力引入包含公共SoftwareApplicationData屬性和關係的中間表，您可以使用Table Per Type (TPT)策略，如下所示：

模型：

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; }
}

配置：

modelBuilder.Entity&lt;SoftwareApplicationBase&gt;()
   .HasOptional(e =&gt; e.Data)
   .WithRequired(e =&gt; e.Application);

modelBuilder.Entity&lt;SoftwareApplicationData&gt;()
   .ToTable("SoftwareApplicationData");

modelBuilder.Entity&lt;SoftwareApplication&gt;()
   .ToTable("SoftwareApplication");

modelBuilder.Entity&lt;OperatingSystem&gt;()
   .ToTable("OperatingSystem");

modelBuilder.Entity&lt;VideoGame&gt;()
   .ToTable("VideoGame");

相關表格：

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

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

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

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

所需的導航與以前一樣，並允許預先載入基本導航屬性：

var test = db.Set&lt;SoftwareApplicationBase&gt;()
   .Include(e =&gt; e.Data)
   .Include(e =&gt; e.Data.CreatedBy)
   .ToList();

回顧一下，在 EF 中獲得自動導航的唯一方法是使用抽像類和 EF 繼承，以及相應的約束。如果它們都不適用於您的場景，則必須求助於類似於問題末尾提到的自定義程式碼處理選項。

引用自：https://stackoverflow.com/questions/46811017

導航到通用定義實體的正確方法

相關問答

如何使用 Entity Framework 6.1 正確標記標識列？

SQL 伺服器視圖在實體框架中返回不同的結果

Entity Framework 6 設置連接字元串執行時

集成 ASP.NET Identity 的最佳實踐——它們存在嗎？

實體框架的 SQL 列預設值

Entity Framework 6.1 是否原生支持 XML 數據類型？