.Net Core對MongoDB執行多條件查詢

以前項目基本上全部使用MySQL資料庫, 最近項目排期空出了一點時間leader決定把日誌模塊遷移到插入/查詢性能更好的MongoDB上. 多條件查詢的寫法著實費了些功夫, 撰文記錄一下.

一、準備工作

1. 安裝過程, 不贅述了

2. 添加ReferencePackage

dotnet add package mongodb.bson
dotnet add package mongodb.driver

3. appsetting.json添加連接配置

"MongodbHost": {
    "Connection": "mongodb://[username]:[password]@[ip]:[port]",
    "DataBase": "[database]",
    "Table": ""
  },

4. 獲取MongoDBConfig 的方法

public static MongodbHostOptions MongodbConfig()
{
    var builder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json");

　　IConfiguration Configuration = builder.Build();
　　var option = Configuration.GetSection("MongodbHost");
　　return new MongodbHostOptions { Connection = option["Connection"], DataBase = option["DataBase"], Table = option["Table"] };
}

二、查詢方法

這裡的查詢方法是網上找的, 直接拿來用了. 如果是單一數據源的話, 這裡的host可以提取出來成為helper類的屬性.

 1 #region FindListByPage 分頁查詢集合
 2     /// <summary>
 3     /// 分頁查詢集合
 4     /// </summary>
 5     /// <param name="filter">查詢條件</param>
 6     /// <param name="pageIndex">當前頁</param>
 7     /// <param name="pageSize">頁容量</param>
 8     /// <param name="count">總條數</param>
 9     /// <param name="field">要查詢的欄位,不寫時查詢全部</param>
10     /// <param name="sort">要排序的欄位</param>
11     /// <returns></returns>
12     public static List<T> FindListByPage(FilterDefinition<T> filter, int pageIndex, int pageSize, out int count, string[] field = null, SortDefinition<T> sort = null)
13     {
14         try
15         {
16             MongodbHostOptions host = Tools.AppSettingsTools.MongodbConfig();
17             host.Table = "WSMessageLog";
18             var client = MongodbClient<T>.MongodbInfoClient(host);
19             count = Convert.ToInt32(client.CountDocuments(filter));
20             //不指定查詢欄位
21             if (field == null || field.Length == 0)
22             {
23                 if (sort == null) return client.Find(filter).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToList();
24                 //進行排序
25                 return client.Find(filter).Sort(sort).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToList();
26             }
27 
28             //指定查詢欄位
29             var fieldList = new List<ProjectionDefinition<T>>();
30             for (int i = 0; i < field.Length; i++)
31             {
32                 fieldList.Add(Builders<T>.Projection.Include(field[i].ToString()));
33             }
34             var projection = Builders<T>.Projection.Combine(fieldList);
35             fieldList?.Clear();
36 
37             //不排序
38             if (sort == null) return client.Find(filter).Project<T>(projection).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToList();
39 
40             //排序查詢
41             return client.Find(filter).Sort(sort).Project<T>(projection).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToList();
42 
43         }
44         catch (Exception ex)
45 　　{
46 　　　　throw ex;
47 　　}
48 }
49 #endregion

三、調用查詢方法

這裡還踩了一個坑. MongoDB里存儲的時間是格林尼治時間, 插入8:00, 查詢時會發現變成了0:00,所以定義時間屬性的時候需要加個標簽

[BsonDateTimeOptions(Kind = DateTimeKind.Local)]
public DateTime logtime { get; set; }

   這裡的OprLogModel是定義了查詢條件的類.

 1 public static LogPager<log_operate> Get_operate_log_mongo(OprLogModel qModel)
 2 {
 3 　　LogPager<log_operate> pager = new LogPager<log_operate>();
 4 　　FilterDefinition<log_operate> filters;
 5 
 6　　 var sortbuilder = Builders<log_operate>.Sort;
 7 　　var sort = sortbuilder.Descending("operate_time");
 8 
 9 　　#region 用戶許可權過濾
10 　　IEnumerable<string> IdList = dev_deviceRepository.GetBinding(qModel.user_id);
11　　 filters = Builders<log_operate>.Filter.In("device_id", IdList);
12 　　#endregion
13 
14 　　if (!string.IsNullOrEmpty(qModel.device_id))
15    {
16    　　var filters_did = Builders<log_operate>.Filter.Eq("device_id", qModel.device_id);
17    　　filters = Builders<log_operate>.Filter.And(filters, filters_did);
18 　　}
19 　　if (qModel.sDate != null)
20 　　{
21 　　　　var filters_sdate = Builders<log_operate>.Filter.Gte<DateTime>("operate_time", Convert.ToDateTime(qModel.sDate));
22 　　　　filters = Builders<log_operate>.Filter.And(filters, filters_sdate);
23 　　}
24 　　if (qModel.eDate != null)
25 　　{
26 　　　　var filters_edate = Builders<log_operate>.Filter.Lte<DateTime>("operate_time", Convert.ToDateTime(qModel.eDate));
27 　　　　filters = Builders<log_operate>.Filter.And(filters, filters_edate);
28 　　} 
29 　　int total;
30 　　pager.data = MongoTools<log_operate>.FindListByPage(filters, qModel.pageindex, (qModel.pageindex - 1) * qModel.pagesize, out total, null, sort);
31 　　pager.total = total;
32 　　return pager;
33 }
34 #endregion

也可以先定義一個空的filterdefinition, 然後與各查詢條件通過And聚合:

FilterDefinition<log_operate> filters = FilterDefinition<log_operate>.Empty;
var filters_idlist = Builders<log_operate>.Filter.In("device_id", IdList);
filters = Builders<log_operate>.Filter.And(filters, filters_idlist);