乘风原创程序

  • .Net Core如何对MongoDB执行多条件查询
  • 2020/7/7 11:09:47
  • 以前项目基本上全部使用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类的属性.

    #region FindListByPage 分页查询集合
      /// <summary>
      /// 分页查询集合
      /// </summary>
      /// <param name="filter">查询条件</param>
      /// <param name="pageIndex">当前页</param>
      /// <param name="pageSize">页容量</param>
      /// <param name="count">总条数</param>
      /// <param name="field">要查询的字段,不写时查询全部</param>
      /// <param name="sort">要排序的字段</param>
      /// <returns></returns>
      public static List<T> FindListByPage(FilterDefinition<T> filter, int pageIndex, int pageSize, out int count, string[] field = null, SortDefinition<T> sort = null)
      {
        try
        {
          MongodbHostOptions host = Tools.AppSettingsTools.MongodbConfig();
          host.Table = "WSMessageLog";
          var client = MongodbClient<T>.MongodbInfoClient(host);
          count = Convert.ToInt32(client.CountDocuments(filter));
          //不指定查询字段
          if (field == null || field.Length == 0)
          {
            if (sort == null) return client.Find(filter).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToList();
            //进行排序
            return client.Find(filter).Sort(sort).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToList();
          }
    
          //指定查询字段
          var fieldList = new List<ProjectionDefinition<T>>();
          for (int i = 0; i < field.Length; i++)
          {
            fieldList.Add(Builders<T>.Projection.Include(field[i].ToString()));
          }
          var projection = Builders<T>.Projection.Combine(fieldList);
          fieldList?.Clear();
    
          //不排序
          if (sort == null) return client.Find(filter).Project<T>(projection).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToList();
    
          //排序查询
          return client.Find(filter).Sort(sort).Project<T>(projection).Skip((pageIndex - 1) * pageSize).Limit(pageSize).ToList();
    
        }
        catch (Exception ex)
      {
        throw ex;
      }
    }
    #endregion

    三、调用查询方法

    这里还踩了一个坑. MongoDB里存储的时间是格林尼治时间, 插入8:00, 查询时会发现变成了0:00,所以定义时间属性的时候需要加个标签

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

    这里的OprLogModel是定义了查询条件的类.

    public static LogPager<log_operate> Get_operate_log_mongo(OprLogModel qModel)
    {
      LogPager<log_operate> pager = new LogPager<log_operate>();
      FilterDefinition<log_operate> filters;
    
       var sortbuilder = Builders<log_operate>.Sort;
      var sort = sortbuilder.Descending("operate_time");
    
      #region 用户权限过滤
      IEnumerable<string> IdList = dev_deviceRepository.GetBinding(qModel.user_id);
       filters = Builders<log_operate>.Filter.In("device_id", IdList);
      #endregion
    
      if (!string.IsNullOrEmpty(qModel.device_id))
      {
        var filters_did = Builders<log_operate>.Filter.Eq("device_id", qModel.device_id);
        filters = Builders<log_operate>.Filter.And(filters, filters_did);
      }
      if (qModel.sDate != null)
      {
        var filters_sdate = Builders<log_operate>.Filter.Gte<DateTime>("operate_time", Convert.ToDateTime(qModel.sDate));
        filters = Builders<log_operate>.Filter.And(filters, filters_sdate);
      }
      if (qModel.eDate != null)
      {
        var filters_edate = Builders<log_operate>.Filter.Lte<DateTime>("operate_time", Convert.ToDateTime(qModel.eDate));
        filters = Builders<log_operate>.Filter.And(filters, filters_edate);
      }
      int total;
      pager.data = MongoTools<log_operate>.FindListByPage(filters, qModel.pageindex, (qModel.pageindex - 1) * qModel.pagesize, out total, null, sort);
      pager.total = total;
      return pager;
    }
    #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);