當麻許的碎念筆記

今天繼續聊一個最近測試 LiteDB 遇到一個很有趣也很方便的東西，  LiteDB 雖然主要是 NoSQL 但是

他也有可以支持類似關聯式資料庫 的操作，畢竟透過正規化資料庫，對於資料規劃也會比較符合主流邏輯

這篇就來聊在 LiteDB  如何做到兩張表( collection ) 的關聯

先說案例說明，有兩個 collection 一個是 ORDERS 負責放訂單資料，一個是 CUSTOMER 負責放客戶資料

所以在每一個 Order 物件中有一個屬性，原本在一般資料庫中，你可以會設計一個 CustomerId 去 relation 到 CUSTOMER　去可以撈取用戶的資料

這裡面，我在 Order 這物件中設計一個 為 Customer 物件型別的屬性為  CustomerData

Data Model:

public class Customer { public string _id { get; set; } public string Name { get; set; } } public class Order { public string OrderId { get; set; } public string Title { get; set; } public DateTime CreateDate { get; set; } public string Memo { get; set; } public string UserId { get; set; } //for mapping. public Customer CustomerData { get; set; } }

之後我們先建立兩筆客戶資料到 CUSTOMER

code:

using (var db = new LiteDatabase(AppDomain.CurrentDomain.BaseDirectory + "sample2.db")) { var userColl = db.GetCollection<Customer>("CUSTOMER"); var u1 = new Customer { _id = "CUSTOMER1", Name = "CUST1" }; var u2 = new Customer { _id = "CUSTOMER2", Name = "CUST2" }; userColl.Upsert(u1); userColl.Upsert(u2); }

資料結果:

再來我們建立 Order 這邊順便測試一些其他功能 BsonMapper.Global 中你可以設定 把某個 Property 存入資料庫中的時候是使用什麼欄位

也可以忽略某一個不存，有一個很特別的功能就是 DbRef 他就是敘述該物件中哪一個屬性對應到哪一張表並且使用該 ID 進行 MAPPING

var mapper = BsonMapper.Global; mapper.Entity<Order>() //OrderId 對應到 DB 中的 _id .Id(x => x.OrderId) //忽略 Memo 不要進 DB .Ignore(x => x.Memo) //將 Title 屬性在資料庫中存為 OrderTitle .Field(x => x.Title, "OrderTitle") //Mapping Table 到 CUSTOMER collection .DbRef(x => x.CustomerData, "CUSTOMER"); using (var db = new LiteDatabase(AppDomain.CurrentDomain.BaseDirectory + "sample2.db", mapper)) { var orderColl = db.GetCollection<Order>("ORDERS"); //兩個客戶 各建立五筆資料 for (var i = 1; i <= 2; i++) { for (var j = 1; j <= 5; j++) { var order = new Order { OrderId = "order" + i + "-" + j, UserId = "friend" + i, CreateDate = DateTime.Now, Title = "ORDER-Friend" + i + "-" + j }; order.CustomerData = new Customer { _id = "CUSTOMER" + i }; orderColl.Upsert(order); } } }

寫入後 資料庫內容長這樣

這時候我們把資料庫撈出來看，這邊不要忘記 Query 後面要去加入 Include  該屬性

Result:

{"OrderId":"order1-1","Title":"ORDER-Friend1-1","CreateDate":"2024-02-02T15:59:19.666+08:00","Memo":null,"UserId":"friend1","CustomerData":{"_id":"CUSTOMER1","Name":"CUST1"}}

{"OrderId":"order1-2","Title":"ORDER-Friend1-2","CreateDate":"2024-02-02T15:59:19.674+08:00","Memo":null,"UserId":"friend1","CustomerData":{"_id":"CUSTOMER1","Name":"CUST1"}}

{"OrderId":"order1-3","Title":"ORDER-Friend1-3","CreateDate":"2024-02-02T15:59:19.674+08:00","Memo":null,"UserId":"friend1","CustomerData":{"_id":"CUSTOMER1","Name":"CUST1"}}

{"OrderId":"order1-4","Title":"ORDER-Friend1-4","CreateDate":"2024-02-02T15:59:19.674+08:00","Memo":null,"UserId":"friend1","CustomerData":{"_id":"CUSTOMER1","Name":"CUST1"}}

{"OrderId":"order1-5","Title":"ORDER-Friend1-5","CreateDate":"2024-02-02T15:59:19.675+08:00","Memo":null,"UserId":"friend1","CustomerData":{"_id":"CUSTOMER1","Name":"CUST1"}}

{"OrderId":"order2-1","Title":"ORDER-Friend2-1","CreateDate":"2024-02-02T15:59:19.675+08:00","Memo":null,"UserId":"friend2","CustomerData":{"_id":"CUSTOMER2","Name":"CUST2"}}

{"OrderId":"order2-2","Title":"ORDER-Friend2-2","CreateDate":"2024-02-02T15:59:19.675+08:00","Memo":null,"UserId":"friend2","CustomerData":{"_id":"CUSTOMER2","Name":"CUST2"}}

{"OrderId":"order2-3","Title":"ORDER-Friend2-3","CreateDate":"2024-02-02T15:59:19.676+08:00","Memo":null,"UserId":"friend2","CustomerData":{"_id":"CUSTOMER2","Name":"CUST2"}}

{"OrderId":"order2-4","Title":"ORDER-Friend2-4","CreateDate":"2024-02-02T15:59:19.677+08:00","Memo":null,"UserId":"friend2","CustomerData":{"_id":"CUSTOMER2","Name":"CUST2"}}

{"OrderId":"order2-5","Title":"ORDER-Friend2-5","CreateDate":"2024-02-02T15:59:19.678+08:00","Memo":null,"UserId":"friend2","CustomerData":{"_id":"CUSTOMER2","Name":"CUST2"}}

這時候  CustomerData 就會根據 CUSTOMER 那張表的資料還原回該物件

這設計我覺得很好寫起來很舒服推薦給大家