學習 Linq to SQL (八)：實體繼承與關聯 — 論壇範例完整實作

資料庫結構

• Categories：CategoryID (PK)、CategoryName
• Boards：BoardID (PK)、BoardName、BoardCategory (FK→Categories)
• Topics：TopicID (PK)、TopicTitle、TopicContent、ParentTopic (自關聯 FK)、TopicType (0=主題, 1=回覆)

單表繼承

基類 Topic 標記 [InheritanceMapping]，判別欄位 TopicType 設 IsDiscriminator=true：

[Table(Name="Topics")]
[InheritanceMapping(Code=0, Type=typeof(NewTopic), IsDefault=true)]
[InheritanceMapping(Code=1, Type=typeof(Reply))]
public class Topic {
  [Column(..., IsPrimaryKey=true, IsDbGenerated=true)] public int TopicID { get; set; }
  [Column(..., IsDiscriminator=true)] public int TopicType { get; set; }
  [Column(...)] public string TopicTitle { get; set; }
  [Column(...)] public string TopicContent { get; set; }
}
public class NewTopic : Topic { public NewTopic() { base.TopicType = 0; } }
public class Reply : Topic {
  public Reply() { base.TopicType = 1; }
  [Column(...)] public int ParentTopic { get; set; }
}

使用繼承實體

var q = from t in ctx.Topics select t;
foreach (Topic t in q) {
  if (t is NewTopic) Console.WriteLine($"主題: {((NewTopic)t).TopicTitle}");
  else if (t is Reply) Console.WriteLine($"回覆: {((Reply)t).TopicTitle} 隸屬 {((Reply)t).ParentTopic}");
}
// 直接查詢衍生型別
var topics = ctx.Topics.OfType<NewTopic>().ToList();
var replies = ctx.Topics.OfType<Reply>().ToList();

新增/刪除同樣透過 ctx.Topics.Add/Remove 操作衍生類別實例。

實體關聯 (Association)

一對多：Categories → Boards

[Table(Name="Categories")]
public class BoardCategory {
  [Column(..., IsPrimaryKey=true)] public int CategoryID { get; set; }
  [Column(...)] public string CategoryName { get; set; }
  private EntitySet<Board> _Boards;
  [Association(OtherKey="BoardCategory", Storage="_Boards")]
  public EntitySet<Board> Boards { get { return _Boards; } set { _Boards.Assign(value); } }
  public BoardCategory() { _Boards = new EntitySet<Board>(); }
}

[Table(Name="Boards")]
public class Board {
  [Column(..., IsPrimaryKey=true)] public int BoardID { get; set; }
  [Column(...)] public string BoardName { get; set; }
  [Column(...)] public int BoardCategory { get; set; }
  private EntityRef<BoardCategory> _Category;
  [Association(ThisKey="BoardCategory", Storage="_Category")]
  public BoardCategory Category {
    get { return _Category.Entity; }
    set { _Category.Entity = value; value.Boards.Add(this); }
  }
}

關聯查詢與 DataLoadOptions

// 查詢 CategoryID=1 的版塊
var q1 = from b in ctx.Boards where b.Category.CategoryID == 1 select b;
// 查詢版塊數>2 的分類
var q2 = from c in ctx.BoardCategories where c.Boards.Count > 2 select c;

// 避免 N+1 問題：預載 Boards
var opts = new DataLoadOptions();
opts.LoadWith<BoardCategory>(c => c.Boards);
ctx.LoadOptions = opts;
// 產生 LEFT OUTER JOIN 單一 SQL

關聯新增

var cat = new BoardCategory { CategoryName = "Database" };
var board = new Board { BoardName = "Oracle", Category = cat };
ctx.BoardCategories.Add(cat); ctx.SubmitChanges();

自動執行 INSERT Categories → 取得 Identity → INSERT Boards。

練習建議

嘗試為 Topics 定義關聯：NewTopic.Replies (EntitySet)、Reply.Parent (EntityRef)，實現主題直接存取回覆、回覆直接存取主題。