In the past, all projects basically used MySQL database. Recently, the project schedule has some free time. Leader decided to migrate the log module to MongoDB, which has better insertion/query performance. Multi-condition query It took a lot of effort to write, so I will write an article to record it.
Related learning recommendations: C#.Net development graphic tutorial
1. Preparation
1. The installation process will not be described in detail
2. Add ReferencePackage
dotnet add package mongodb.bson dotnet add package mongodb.driver
3. Add connection configuration to appsetting.json
"MongodbHost": { "Connection": "mongodb://[username]:[password]@[ip]:[port]", "DataBase": "[database]", "Table": "" },
4. Method of obtaining 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"] }; }
2. Query method
The query method here is found online and used directly. If it is a single As for the data source, the host here can be extracted and become an attribute of the helper class.
#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
3. Call the query method
There is also a pitfall here. Storage in MongoDB The time is Greenwich Time. If you insert 8:00, you will find that it becomes 0:00 when querying, so you need to add a label when defining the time attribute
[BsonDateTimeOptions(Kind = DateTimeKind.Local)] public DateTime logtime { get; set; }
The OprLogModel here is the class that defines the query conditions.
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
You can also define an empty filterdefinition first, and then aggregate it with each query condition through 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);
The above is the detailed content of .Net Core performs multi-condition query on MongoDB. For more information, please follow other related articles on the PHP Chinese website!