當麻許的碎念筆記

最近因為在測試 LiteDB ，所以這邊筆記幾個查詢方法，可以方便自己之後可以快速的回憶起來要怎麼寫

下面主要就是列舉幾種查詢資料的方法，最後一個測試比較久因為我查詢了 物件中的 List<Object> 

裡面的屬性並且使用 %LIKE% 

1. 建立十萬筆資料，這邊附上我的測試 CLASS 還有新增詞十萬筆的  code ，這樣才會知道，我之後測試的搜尋到底在搜尋什麼

//Friend Model public class Friend { public string Name { get; set; } public string _id { get; set; } public DateTime CreateDate { get; set; } public List<Phone> Phones { get; set; } public bool IsActive { get; set; } public decimal Salary { get; set; } public Friend() { Phones = new List<Phone>(); } public class Phone { public string Num { get; set; } public string Type { get; set; } } } //Create 10w Data using (var db = new LiteDatabase(AppDomain.CurrentDomain.BaseDirectory + "sample.db")) { var userColleciton = db.GetCollection<Friend>("FRIENDS"); for (var i = 1; i <= 100_000; i++) { var u = new Friend { _id = "friend" + i, CreateDate = DateTime.Now, IsActive = true, Name = "FRIEND" + i, Salary = 10 * i }; u.Phones.Add(new Friend.Phone { Num = "09" + i, Type = "HOME" }); userColleciton.Upsert(u); } }

2.類 SQL 查詢 %LIKE% - 透過 類 SQL 語法查詢 在 Name 中包含有 IEND9999 的字串

//%LIKE% 使用 類SQL 語法查詢 using (var db = new LiteDatabase(AppDomain.CurrentDomain.BaseDirectory + "sample.db")) { var data1 = db.Execute("SELECT $ FROM FRIENDS WHERE Name LIKE '%IEND9999%'").ToArray(); if (data1 != null) { foreach (var data in data1) { Console.WriteLine(JsonConvert.SerializeObject(BsonMapper.Global.Deserialize<Friend>(data))); } Console.WriteLine(); Console.WriteLine("Count:" + data1.Length); } } //Result: /* {"Name":"FRIEND9999","_id":"friend9999","CreateDate":"2024-01-26T17:10:44.664+08:00","Phones":[{"Num":"099999","Type":"HOME"}],"IsActive":true,"Salary":99990.0} {"Name":"FRIEND99990","_id":"friend99990","CreateDate":"2024-01-26T17:10:59.594+08:00","Phones":[{"Num":"0999990","Type":"HOME"}],"IsActive":true,"Salary":999900.0} {"Name":"FRIEND99991","_id":"friend99991","CreateDate":"2024-01-26T17:10:59.594+08:00","Phones":[{"Num":"0999991","Type":"HOME"}],"IsActive":true,"Salary":999910.0} {"Name":"FRIEND99992","_id":"friend99992","CreateDate":"2024-01-26T17:10:59.594+08:00","Phones":[{"Num":"0999992","Type":"HOME"}],"IsActive":true,"Salary":999920.0} {"Name":"FRIEND99993","_id":"friend99993","CreateDate":"2024-01-26T17:10:59.594+08:00","Phones":[{"Num":"0999993","Type":"HOME"}],"IsActive":true,"Salary":999930.0} {"Name":"FRIEND99994","_id":"friend99994","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999994","Type":"HOME"}],"IsActive":true,"Salary":999940.0} {"Name":"FRIEND99995","_id":"friend99995","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999995","Type":"HOME"}],"IsActive":true,"Salary":999950.0} {"Name":"FRIEND99996","_id":"friend99996","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999996","Type":"HOME"}],"IsActive":true,"Salary":999960.0} {"Name":"FRIEND99997","_id":"friend99997","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999997","Type":"HOME"}],"IsActive":true,"Salary":999970.0} {"Name":"FRIEND99998","_id":"friend99998","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999998","Type":"HOME"}],"IsActive":true,"Salary":999980.0} {"Name":"FRIEND99999","_id":"friend99999","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999999","Type":"HOME"}],"IsActive":true,"Salary":999990.0} Count:11 */

3.僅回傳某些屬性 - 查詢跟上面類似，直接對 DB 查詢 Name 中含有  IEND9999的 字串，但是我只拿回 _id 還有 Phones 的 Property 

這邊我有還原回 Friend 物件，所以導致除了 _id 還有 Phones 的屬性有資料其他都是預設值，下面我也有做單獨把那兩個屬性取出

//搜尋但是不要拿這麼多欄位 using (var db = new LiteDatabase(AppDomain.CurrentDomain.BaseDirectory + "sample.db")) { var userColleciton = db.GetCollection<Friend>("FRIENDS"); var data1 = db.Execute("SELECT _id,Phones FROM FRIENDS WHERE Name LIKE '%IEND9999%'").ToArray(); if (data1 != null) { foreach (var data in data1) { Console.WriteLine(JsonConvert.SerializeObject(BsonMapper.Global.Deserialize<Friend>(data))); Console.WriteLine(data["_id"].ToString() + "," + JsonConvert.SerializeObject(BsonMapper.Global.Deserialize<List<Friend.Phone>>(data["Phones"]))); } Console.WriteLine(); Console.WriteLine("Count:" + data1.Length); } } //Result /* {"Name":null,"_id":"friend9999","CreateDate":"0001-01-01T00:00:00","Phones":[{"Num":"099999","Type":"HOME"}],"IsActive":false,"Salary":0.0} "friend9999",[{"Num":"099999","Type":"HOME"}] {"Name":null,"_id":"friend99990","CreateDate":"0001-01-01T00:00:00","Phones":[{"Num":"0999990","Type":"HOME"}],"IsActive":false,"Salary":0.0} "friend99990",[{"Num":"0999990","Type":"HOME"}] {"Name":null,"_id":"friend99991","CreateDate":"0001-01-01T00:00:00","Phones":[{"Num":"0999991","Type":"HOME"}],"IsActive":false,"Salary":0.0} "friend99991",[{"Num":"0999991","Type":"HOME"}] {"Name":null,"_id":"friend99992","CreateDate":"0001-01-01T00:00:00","Phones":[{"Num":"0999992","Type":"HOME"}],"IsActive":false,"Salary":0.0} "friend99992",[{"Num":"0999992","Type":"HOME"}] {"Name":null,"_id":"friend99993","CreateDate":"0001-01-01T00:00:00","Phones":[{"Num":"0999993","Type":"HOME"}],"IsActive":false,"Salary":0.0} "friend99993",[{"Num":"0999993","Type":"HOME"}] {"Name":null,"_id":"friend99994","CreateDate":"0001-01-01T00:00:00","Phones":[{"Num":"0999994","Type":"HOME"}],"IsActive":false,"Salary":0.0} "friend99994",[{"Num":"0999994","Type":"HOME"}] {"Name":null,"_id":"friend99995","CreateDate":"0001-01-01T00:00:00","Phones":[{"Num":"0999995","Type":"HOME"}],"IsActive":false,"Salary":0.0} "friend99995",[{"Num":"0999995","Type":"HOME"}] {"Name":null,"_id":"friend99996","CreateDate":"0001-01-01T00:00:00","Phones":[{"Num":"0999996","Type":"HOME"}],"IsActive":false,"Salary":0.0} "friend99996",[{"Num":"0999996","Type":"HOME"}] {"Name":null,"_id":"friend99997","CreateDate":"0001-01-01T00:00:00","Phones":[{"Num":"0999997","Type":"HOME"}],"IsActive":false,"Salary":0.0} "friend99997",[{"Num":"0999997","Type":"HOME"}] {"Name":null,"_id":"friend99998","CreateDate":"0001-01-01T00:00:00","Phones":[{"Num":"0999998","Type":"HOME"}],"IsActive":false,"Salary":0.0} "friend99998",[{"Num":"0999998","Type":"HOME"}] {"Name":null,"_id":"friend99999","CreateDate":"0001-01-01T00:00:00","Phones":[{"Num":"0999999","Type":"HOME"}],"IsActive":false,"Salary":0.0} "friend99999",[{"Num":"0999999","Type":"HOME"}] Count:11 */

4.查詢 List<Objec>屬性中的屬性  - 因為 Friend 中的 Phones 是 Phone 物件，我要查詢裡面的 Num 屬性字串 包含了 099999

//搜尋子物件List 中有符合條件的 using (var db = new LiteDatabase(AppDomain.CurrentDomain.BaseDirectory + "sample.db")) { var userColleciton = db.GetCollection<Friend>("FRIENDS"); sw.Restart(); var data1 = db.Execute("select $ from FRIENDS include Phones where $.Phones[*].Num any LIKE '%099999%'").ToList(); if (data1 != null) { foreach (var d in data1) { Console.WriteLine(JsonConvert.SerializeObject(BsonMapper.Global.Deserialize<Friend>(d))); } sw.Stop(); Console.WriteLine("Cost time:" + sw.Elapsed); Console.WriteLine("Count:" + data1.Count()); } } //Result /* {"Name":"FRIEND9999","_id":"friend9999","CreateDate":"2024-01-26T17:10:44.664+08:00","Phones":[{"Num":"099999","Type":"HOME"}],"IsActive":true,"Salary":99990.0} {"Name":"FRIEND99990","_id":"friend99990","CreateDate":"2024-01-26T17:10:59.594+08:00","Phones":[{"Num":"0999990","Type":"HOME"}],"IsActive":true,"Salary":999900.0} {"Name":"FRIEND99991","_id":"friend99991","CreateDate":"2024-01-26T17:10:59.594+08:00","Phones":[{"Num":"0999991","Type":"HOME"}],"IsActive":true,"Salary":999910.0} {"Name":"FRIEND99992","_id":"friend99992","CreateDate":"2024-01-26T17:10:59.594+08:00","Phones":[{"Num":"0999992","Type":"HOME"}],"IsActive":true,"Salary":999920.0} {"Name":"FRIEND99993","_id":"friend99993","CreateDate":"2024-01-26T17:10:59.594+08:00","Phones":[{"Num":"0999993","Type":"HOME"}],"IsActive":true,"Salary":999930.0} {"Name":"FRIEND99994","_id":"friend99994","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999994","Type":"HOME"}],"IsActive":true,"Salary":999940.0} {"Name":"FRIEND99995","_id":"friend99995","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999995","Type":"HOME"}],"IsActive":true,"Salary":999950.0} {"Name":"FRIEND99996","_id":"friend99996","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999996","Type":"HOME"}],"IsActive":true,"Salary":999960.0} {"Name":"FRIEND99997","_id":"friend99997","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999997","Type":"HOME"}],"IsActive":true,"Salary":999970.0} {"Name":"FRIEND99998","_id":"friend99998","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999998","Type":"HOME"}],"IsActive":true,"Salary":999980.0} {"Name":"FRIEND99999","_id":"friend99999","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999999","Type":"HOME"}],"IsActive":true,"Salary":999990.0} Cost time:00:00:02.0725930 Count:11 */

5.透過LIQN方式查詢 List<Object> 屬性中的屬性 - 跟上述案例一樣只是透過 LINQ 的方法查詢

using (var db = new LiteDatabase(AppDomain.CurrentDomain.BaseDirectory + "sample.db")) { sw.Start(); var userColleciton = db.GetCollection<Friend>("FRIENDS"); var data1 = userColleciton.FindAll().Where(x => x.Phones.Any(y => y.Num.Contains("099999"))); if (data1 != null) { foreach (var d in data1) { Console.WriteLine(JsonConvert.SerializeObject(d)); } sw.Stop(); Console.WriteLine("Cost time:" + sw.Elapsed); Console.WriteLine("Count:" + data1.Count()); } } //Result /* {"Name":"FRIEND9999","_id":"friend9999","CreateDate":"2024-01-26T17:10:44.664+08:00","Phones":[{"Num":"099999","Type":"HOME"}],"IsActive":true,"Salary":99990.0} {"Name":"FRIEND99990","_id":"friend99990","CreateDate":"2024-01-26T17:10:59.594+08:00","Phones":[{"Num":"0999990","Type":"HOME"}],"IsActive":true,"Salary":999900.0} {"Name":"FRIEND99991","_id":"friend99991","CreateDate":"2024-01-26T17:10:59.594+08:00","Phones":[{"Num":"0999991","Type":"HOME"}],"IsActive":true,"Salary":999910.0} {"Name":"FRIEND99992","_id":"friend99992","CreateDate":"2024-01-26T17:10:59.594+08:00","Phones":[{"Num":"0999992","Type":"HOME"}],"IsActive":true,"Salary":999920.0} {"Name":"FRIEND99993","_id":"friend99993","CreateDate":"2024-01-26T17:10:59.594+08:00","Phones":[{"Num":"0999993","Type":"HOME"}],"IsActive":true,"Salary":999930.0} {"Name":"FRIEND99994","_id":"friend99994","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999994","Type":"HOME"}],"IsActive":true,"Salary":999940.0} {"Name":"FRIEND99995","_id":"friend99995","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999995","Type":"HOME"}],"IsActive":true,"Salary":999950.0} {"Name":"FRIEND99996","_id":"friend99996","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999996","Type":"HOME"}],"IsActive":true,"Salary":999960.0} {"Name":"FRIEND99997","_id":"friend99997","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999997","Type":"HOME"}],"IsActive":true,"Salary":999970.0} {"Name":"FRIEND99998","_id":"friend99998","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999998","Type":"HOME"}],"IsActive":true,"Salary":999980.0} {"Name":"FRIEND99999","_id":"friend99999","CreateDate":"2024-01-26T17:10:59.595+08:00","Phones":[{"Num":"0999999","Type":"HOME"}],"IsActive":true,"Salary":999990.0} Cost time:00:00:02.0914854 Count:11 */

大概先寫到這邊，主要是第四個花了我一點時間測試