上一篇文章，我們簡單的測試了 FreeSql ，今天我們要稍微複雜一點了
我們常常再設計資料庫表格的時候假設有一個資料模型非常大，這時候我們會拆開
把熱資料當作主表，其他比較冷的資料，會再用另一張表紀錄，這時候要用到 FreeSql OneToOne 的概念
這邊主要的表是 ORDERINFOS ，主要當熱資料查詢會用到，其餘資料 我們把他放到 ORDER_EXTS
這時候直接上 CODE，應該是一看就懂
Models:
<![CDATA[
[FreeSql.DataAnnotations.Table(Name = "ORDERINFOS")]
public class OrderInfo
{
[FreeSql.DataAnnotations.Column(IsIdentity = true, IsPrimary = true)]
public Guid OrderId { get; set; }
public string Title { get; set; }
public int Index { get; set; }
public DateTime CreateDate { get; set; }
public string Memo { get; set; }
public OrderExtent Ext{get;set;}
}
]]>
<![CDATA[
[FreeSql.DataAnnotations.Table(Name = "ORDER_EXTS")]
public class OrderExtent
{
[FreeSql.DataAnnotations.Column(IsPrimary = true)]
public Guid _id { get; set; }
public string Data1 { get; set; }
public decimal Data2 { get; set; }
public DateTime Data3 { get; set; }
public virtual OrderInfo Order { get; set; }
}
]]>
這裡面，我在 OrderExtent 中加入 public virtual OrderInfo Order { get; set; } 就可以了，這邊要注意一下
寫入，跟 Query Code:
<![CDATA[
var freeSQLiteDB = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite, "data source=" + AppDomain.CurrentDomain.BaseDirectory + "sampleSQLite.db")
.UseAutoSyncStructure(true)
.Build();
var orderId = Guid.Parse(input: "d2222222-3333-4444-5555-955227c9912d");
var o1 = new OrderInfo { OrderId = orderId, Index = 99, Memo = "ONE2ONE", Title = "order99-ttile", CreateDate = DateTime.Now, Ext = new OrderExtent { Data1 = "ORDER99-延伸資料", Data2 = 99.123m, Data3 = DateTime.Now } };
var repo = freeSQLiteDB.GetRepository<OrderInfo>();
repo.DbContextOptions.EnableCascadeSave = true;
repo.InsertOrUpdate(o1);
//查詢
var res = repo.Where(a => a.OrderId == orderId).First();
Console.WriteLine(JsonConvert.SerializeObject(res));
//{"OrderId":"d2222222-3333-4444-5555-955227c9912d","Title":"order99-ttile","Index":99,"CreateDate":"2024-02-21T10:29:01.2156901","Memo":"ONE2ONE","Ext":null}
//要透過 LeftJoin 才會把 Ext 資料關聯出來
var res2 = freeSQLiteDB.Select<OrderInfo>().LeftJoin(a => a.Ext._id == orderId)
.Where(a => a.OrderId == orderId)
.First();
Console.WriteLine(JsonConvert.SerializeObject(res2));
//{"OrderId":"d2222222-3333-4444-5555-955227c9912d","Title":"order99-ttile","Index":99,"CreateDate":"2024-02-21T11:21:21.6194175","Memo":"ONE2ONE","Ext":{"_id":"d2222222-3333-4444-5555-955227c9912d","Data1":"ORDER99-延伸資料","Data2":99.123,"Data3":"2024-02-21T11:21:21.6195739","Order":null}}
]]>
查詢的部分 要使用到 LeftJoin 才會把 Ext 的資料關聯出來
Table 部分:
參考文件:
https://freesql.net/guide/cascade-saving.html#savemany-%E6%89%8B%E5%B7%A5%E4%BF%9D%E5%AD%98
https://freesql.net/guide/repository.html
