上一篇文章，我們針對 資料如果過於龐大，拆成兩張表，並且同 Key 狀況下
進行 Table 擴充，還有一種狀況在開發上會常遇到的就是 OneToMany 的狀況
我有一個 OrderInfo 他有一個 Propery 物件為 Cate ，但是 CATES 表格中 中，每一個 Cate 會被許多 OrderInfo 的 CateId 給關聯著
在案例中，我們新增兩筆 OrderInfo ，並且也新增兩個 Cate ( Cate1, Cate2 ) ，但是我把兩筆訂單都關聯再 Cate2
Data Model:
<![CDATA[
[FreeSql.DataAnnotations.Table(Name = "CATES")]
public class Cate
{
[FreeSql.DataAnnotations.Column(IsPrimary = true)]
public Guid CateId { get; set; }
[Navigate(nameof(OrderInfo.CateId))]
public List<Order> Orders { get; set; }
public string Display { get; set; }
}
]]>
<![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 Guid CateId { get; set; }
// [Navigate(nameof(CateId))]
public Cate CateInfo { get; set; }
public OrderExtent Ext{get;set;}
}
]]>
寫入 OrderInfo , Cate
<![CDATA[
var dbPath = AppDomain.CurrentDomain.BaseDirectory + "sampleSQLite.db";
var freeSQLiteDB = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite, "data source=" + dbPath)
.UseAutoSyncStructure(true)
.Build();
var orderId1 = Guid.Parse("d1111111-90ee-4e96-af38-955227c9912d");
var orderId2 = Guid.Parse("d2222222-90ee-4e96-af38-955227c9912d");
var cateId1 = Guid.Parse("ce1e1111-5085-449d-b07e-b804796f14de");
var cateId2 = Guid.Parse("ce1e2222-5085-449d-b07e-b804796f14de");
var cate1 = new Cate { CateId = cateId1, Display = "分類1" };
var cate2 = new Cate { CateId = cateId2, Display = "分類2" };
//兩筆訂單都是為 分類2
var o1 = new OrderInfo { OrderId = orderId1, Title = "order1-ttile", CreateDate = DateTime.Now, CateId = cateId2 };
var o2 = new OrderInfo { OrderId = orderId2, Title = "order2-ttile", CreateDate = DateTime.Now, CateId = cateId2 };
var repoCate = freeSQLiteDB.GetRepository<Cate>();
repoCate.DbContextOptions.EnableCascadeSave = true;
repoCate.InsertOrUpdate(cate1);
repoCate.InsertOrUpdate(cate2);
var repoOrder = freeSQLiteDB.GetRepository<OrderInfo>();
repoOrder.InsertOrUpdate(o1);
repoOrder.InsertOrUpdate(o2);
]]>
查詢:
<![CDATA[
var res1 = freeSQLiteDB.Select<OrderInfo>().LeftJoin(a => a.CateInfo.CateId == a.CateId)
.Where(a => a.OrderId == orderId1)
.ToList();
Console.WriteLine(JsonConvert.SerializeObject(res1));
//[{"OrderId":"d1111111-90ee-4e96-af38-955227c9912d","Title":"order1-ttile","Index":0,"CreateDate":"2024-02-21T12:18:57.1913341","Memo":null,"CateId":"ce1e2222-5085-449d-b07e-b804796f14de","CateInfo":{"CateId":"ce1e2222-5085-449d-b07e-b804796f14de","Orders":null,"Display":"分類2"},"Ext":null}]
Console.WriteLine("\r\n =================================================================== r\n");
var res2 = freeSQLiteDB.Select<Cate>().IncludeMany<Order>(x => x.Orders)
.ToList();
foreach (var data in res2)
{
Console.WriteLine(JsonConvert.SerializeObject(data));
}
//{"CateId":"ce1e1111-5085-449d-b07e-b804796f14de","Orders":[],"Display":"分類1"}
//{ "CateId":"ce1e2222-5085-449d-b07e-b804796f14de","Orders":[{ "OrderId":"d1111111-90ee-4e96-af38-955227c9912d","Title":"order1-ttile","CreateDate":"2024-02-21T08:29:34.3986627","Memo":null,"CateId":"ce1e2222-5085-449d-b07e-b804796f14de","CateInfo":null,"CustomerData":null},{ "OrderId":"d2222222-90ee-4e96-af38-955227c9912d","Title":"order2-ttile","CreateDate":"2024-02-21T08:29:34.3989013","Memo":null,"CateId":"ce1e2222-5085-449d-b07e-b804796f14de","CateInfo":null,"CustomerData":null}],"Display":"分類2"}
]]>
資料庫結果:
測試大概到這裡，其實 FreeSql 是一套試著簡化開發者去操控資料庫的工具
單看程式碼的確是簡化很多，但是前提是你要夠熟練，因為其實我測是很久，很多時候都不如我達到的測試結果
所以在這裡筆記一下，這系列目前就先寫到這，如果之後我有用 FreeSql 開發，有遇到問題我們再繼續聊..
