欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-03 10:46:44
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);