.Net Core对MongoDB执行多条件查询

以前项目基本上全部使用MySQL数据库, 最近项目排期空出了一点时间leader决定把日志模块迁移到插入/查询性能更好的MongoDB上. 多条件查询的写法着实费了些功夫, 撰文记录一下.

一、准备工作

1. 安装过程, 不赘述了

2. 添加ReferencePackage

dotnet add package mongodb.bsondotnet 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         try15         {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 　　#endregion13 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);