以前项目基本上全部使用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 /// 3 /// 分页查询集合 4 /// 5 /// 查询条件 6 /// 当前页 7 /// 页容量 8 /// 总条数 9 /// 要查询的字段,不写时查询全部10 /// 要排序的字段11 /// 12 public static ListFindListByPage(FilterDefinitionfilter, int pageIndex, int pageSize, out int count, string[] field = null, SortDefinitionsort = null)13 {14 try15 {16 MongodbHostOptions host = Tools.AppSettingsTools.MongodbConfig();17 host.Table = "WSMessageLog";18 var client = MongodbClient.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>();30 for (int i = 0; i < field.Length; i++)31 {32 fieldList.Add(Builders.Projection.Include(field[i].ToString()));33 }34 var projection = Builders.Projection.Combine(fieldList);35 fieldList?.Clear();36 37 //不排序38 if (sort == null) return client.Find(filter).Project(projection).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToList();39 40 //排序查询41 return client.Find(filter).Sort(sort).Project(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 Get_operate_log_mongo(OprLogModel qModel) 2 { 3 LogPagerpager = new LogPager(); 4 FilterDefinition filters; 5 6 var sortbuilder = Builders.Sort; 7 var sort = sortbuilder.Descending("operate_time"); 8 9 #region 用户权限过滤10 IEnumerable<string> IdList = dev_deviceRepository.GetBinding(qModel.user_id);11 filters = Builders.Filter.In("device_id", IdList);12 #endregion13 14 if (!string.IsNullOrEmpty(qModel.device_id))15 {16 var filters_did = Builders.Filter.Eq("device_id", qModel.device_id);17 filters = Builders.Filter.And(filters, filters_did);18 }19 if (qModel.sDate != null)20 {21 var filters_sdate = Builders.Filter.Gte("operate_time", Convert.ToDateTime(qModel.sDate));22 filters = Builders.Filter.And(filters, filters_sdate);23 }24 if (qModel.eDate != null)25 {26 var filters_edate = Builders.Filter.Lte("operate_time", Convert.ToDateTime(qModel.eDate));27 filters = Builders.Filter.And(filters, filters_edate);28 } 29 int total;30 pager.data = MongoTools.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
Copyright © 2004-2024 Ynicp.com 版权所有 法律顾问:建纬(昆明)律师事务所 昆明市网翼通科技有限公司 滇ICP备08002592号-4
