public void PipelineDefinitionCanBeBuild(string groupByArguments) { JsonPipelineStageDefinition <BsonDocument, BsonDocument> stage = null; Assert.That(() => stage = GroupDefinitionBuilder.Build <BsonDocument>(groupByArguments), Throws.Nothing); Assert.That(stage, Is.Not.Null); }
//关联查询查询数据 public List <BsonDocument> Get_Aggregate(string coll, List <string> key, List <string> value, List <string> coll_name, List <string> localField, List <string> foreignField, List <string> ASS) { try { var oll = database.GetCollection <BsonDocument>(coll); IList <IPipelineStageDefinition> stages = new List <IPipelineStageDefinition>(); string match = "{$match:{"; for (int i = 0; i < key.Count; i++) { match += "'" + key[i] + "':'" + value[i] + "',"; } match += "}}"; PipelineStageDefinition <BsonDocument, BsonDocument> stage1 = new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(match); stages.Add(stage1); for (int i = 0; i < localField.Count; i++) { string pipelineJson = "{$lookup:{from: '" + coll_name[i] + "',localField: '" + localField[i] + "',foreignField: '" + foreignField[i] + "',as: '" + ASS[i] + "'}}"; PipelineStageDefinition <BsonDocument, BsonDocument> stage = new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(pipelineJson); stages.Add(stage); } PipelineDefinition <BsonDocument, BsonDocument> pipeline = new PipelineStagePipelineDefinition <BsonDocument, BsonDocument>(stages); return(oll.Aggregate(pipeline).ToList()); } catch { return(null); } }
/// <summary> /// 从歌单中删除某些歌曲 /// </summary> /// <param name="userId"></param> /// <param name="listId"></param> /// <param name="sids">Sid,(int)DataSource_(string)Id格式</param> /// <returns></returns> public async Task <Result> RemoveMusicFromList(long userId, string listId, List <string> sids) { if (!await ValidPlaylistOwner(userId, listId)) { return(Result.FailResult("您不是歌单的拥有者哦", 403)); } //聚合查询元素所在索引位置 IPipelineStageDefinition stage0 = new JsonPipelineStageDefinition <UserMusic, UserMusic> ("{\"$match\":{\"_id\" :" + userId.ToString() + "}}"); IPipelineStageDefinition stage1 = new JsonPipelineStageDefinition <UserMusic, IndexInfo> ("{ \"$project\": { \"index\": { \"$indexOfArray\":[\"$playlists._id\",ObjectId(\"" + listId + "\")] },\"_id\":0}}"); var pipeline = new PipelineStagePipelineDefinition <UserMusic, IndexInfo> (new List <IPipelineStageDefinition>() { stage0, stage1 }); var indexResult = (await this.userMusicCollection.AggregateAsync(pipeline)).FirstOrDefault(); if (indexResult == null) { return(Result.FailResult("操作失败,目标不存在!", 404)); } var update = Builders <UserMusic> .Update.PullFilter <MusicInfo>( $"playlists.{indexResult.Index}.tracks", Builders <MusicInfo> .Filter.In(mic => mic.Sid, sids)); var updateResult = await this.userMusicCollection.UpdateManyAsync( Builders <UserMusic> .Filter.Eq(usr => usr.UserId, userId), update); return(updateResult.MatchedCount > 0 ? Result.SuccessReuslt("操作成功") : Result.FailResult("操作失败,目标不存在!", 404)); }
/// <summary> /// 获取用户创建的歌单,不包含音乐列表 /// </summary> /// <param name="userId"></param> /// <param name="limit"></param> /// <param name="offset"></param> /// <returns></returns> public async Task <PageResult <UserPlaylist> > GetUserPlaylist(long userId, int limit, int offset) { //查询用户歌单,排除不需要的字段 var projection = Builders <UserMusic> .Projection.ElemMatch(usr => usr.UserPlaylists, Builders <UserPlaylist> .Filter.Eq(list => list.UserId, userId)) .Slice(usr => usr.UserPlaylists, offset, limit) .Exclude(usr => usr.Likes) .Exclude(usr => usr.SubscribePlaylists) .Exclude("playlists.tracks"); Task <UserMusic> list_task = this.userMusicCollection.Find(item => item.UserId == userId) .Project <UserMusic>(projection).FirstOrDefaultAsync(); //统计总数 var aggregateOption = new AggregateOptions(); //管道件一,输入类型UserMusic,筛选出对应_id的用户 IPipelineStageDefinition stage0 = new JsonPipelineStageDefinition <UserMusic, UserMusic> ("{\"$match\":{\"_id\" :" + userId.ToString() + "}}"); //管道件二,通过$size聚合得到数组长度,输出类型为内部类CountInfo IPipelineStageDefinition stage1 = new JsonPipelineStageDefinition <UserMusic, CountInfo> ("{ \"$project\": { \"count\": { \"$size\":\"$playlists\" },\"_id\":0}}"); //生成管道 var pipeline = new PipelineStagePipelineDefinition <UserMusic, CountInfo> (new List <IPipelineStageDefinition>() { stage0, stage1 }); var count_task = this.userMusicCollection.AggregateAsync(pipeline); List <UserPlaylist> playlists = (await list_task)?.UserPlaylists; var countInfo = (await count_task).FirstOrDefault(); return(playlists == null ? PageResult <UserPlaylist> .FailResult("用户不存在!", 404) : PageResult <UserPlaylist> .SuccessResult(playlists, countInfo != null?countInfo.Count : 0)); }
public void PipelineDefinitionCanBeBuild() { var orderByArguments = "index ASC, timestamp DESC"; JsonPipelineStageDefinition <BsonDocument, BsonDocument> stage = null; Assert.That(() => stage = SortDefinitionBuilder.Build <BsonDocument>(orderByArguments), Throws.Nothing); Assert.That(stage, Is.Not.Null); }
public void PipelineDefinitionCanBeBuild() { var limitArguments = "31"; JsonPipelineStageDefinition <BsonDocument, BsonDocument> stage = null; Assert.That(() => stage = LimitDefinitionBuilder.Build <BsonDocument>(limitArguments), Throws.Nothing); Assert.That(stage, Is.Not.Null); }
public void LikeClauseCanHandleRegexCharacters() { var whereArguments = "Data.BatchId LIKE '%|%'"; JsonPipelineStageDefinition <BsonDocument, BsonDocument> stage = null; Assert.That(() => stage = FilterDefinitionBuilder.Build <BsonDocument>(whereArguments), Throws.Nothing); Assert.That(stage.Json.Contains(".*\\\\|.*")); }
public void PipelineDefinitionAsExpected(string whereArguments, string expected) { JsonPipelineStageDefinition <BsonDocument, BsonDocument> stage = null; Assert.That(() => stage = FilterDefinitionBuilder.Build <BsonDocument>(whereArguments), Throws.Nothing); Assert.That(stage, Is.Not.Null); Assert.That(stage.Json, Is.EqualTo(expected)); }
public void PipelineDefinitionCanBeBuild() { var selectArguments = "id, test.subtest, product.price.tax as tax"; JsonPipelineStageDefinition <BsonDocument, BsonDocument> stage = null; Assert.That(() => stage = ProjectionDefinitionBuilder.Build <BsonDocument>(selectArguments), Throws.Nothing); Assert.That(stage, Is.Not.Null); Assert.That(stage.Json, Is.EqualTo("{ $project : { \"id\" : true, \"test_subtest\" : \"$test.subtest\", \"tax\" : \"$product.price.tax\" } }")); }
public IAsyncCursor <T> _GroupBy <T>(string collName, string[] strStag) //where T : class, new() { IList <IPipelineStageDefinition> stages = new List <IPipelineStageDefinition>(); AggregateOptions aggs = new AggregateOptions(); for (int i = 0; i < strStag.Length; i++) { PipelineStageDefinition <T, T> stage = new JsonPipelineStageDefinition <T, T>(strStag[i]); stages.Add(stage); } PipelineDefinition <T, T> pipeline = new PipelineStagePipelineDefinition <T, T>(stages); //排序查询 try { var client = _database.GetCollection <T>(collName); return(client.Aggregate(pipeline)); } catch (Exception e) { return(null); } }
/// <summary> /// 根据管道获取数据[异步] /// </summary> /// <param name="search"></param> /// <returns></returns> public async Task <BaseResultModel <T> > GetListAggregateAsync(ManagePostAttPageSearch search) { Dictionary <string, string> searchDic = Newtonsoft.Json.JsonConvert.DeserializeObject <Dictionary <string, string> >(search.SearchDic.ToString()); Dictionary <string, string> sortDic = Newtonsoft.Json.JsonConvert.DeserializeObject <Dictionary <string, string> >(search.SortDic.ToString()); PipeLineBsonHelper pipeLineBsonHelper = new PipeLineBsonHelper(); List <string> pipeLinelist = new List <string>(); #region 过滤(条件查询) pipeLineBsonHelper.AddMatchList(searchDic, pipeLinelist); #endregion #region 分组(用 $last排序,后续改为属性映射独立方法 ) if (string.IsNullOrEmpty(search.GroupField)) { throw new ArgumentNullException($"分组参数{nameof(search.GroupField)}不能为空!"); } string group = "{'" + search.GroupField + "':'$" + search.GroupField + "'}"; string pipelineGroup = @"{$group: { _id:" + group + @", 'id':{ '$first':'$_id'}, 'ConstructPermitNum':{ '$first' :'$ConstructPermitNum'}, 'ProjectName':{ '$last' :'$ProjectName'}, 'ProjectGuid':{ '$last' :'$ProjectGuid'}, 'Company':{ '$last':'$Company'}, 'OrganizationCode':{ '$last':'$OrganizationCode'}, 'CorpType':{ '$last':'$CorpType'}, 'SegmentAddressArea':{ '$last':'$SegmentAddressArea'}, 'AttendanceTime':{ '$last':'$AttendanceTime'}, 'AttendanceId':{ '$last':'$AttendanceId'}, 'PersonGUID':{ '$last':'$PersonGUID'}, 'PersonName':{ '$last':'$PersonName'}, 'IdCard':{ '$last':'$IdCard'}, 'PostType':{ '$last':'$PostType'}, 'ImageBuffer':{ '$last':'$ImageBuffer'}, 'SupervisionDepartment':{ '$last':'$SupervisionDepartment'}, 'SupervisionDepartmentGUID':{ '$last':'$SupervisionDepartmentGUID'}, 'CreateTime':{ '$last':'$CreateTime'} } }"; pipeLinelist.Add(pipelineGroup); #endregion #region 总数(根据条件查询总数) string pipelineCount = "{$count:'total'}"; pipeLinelist.Add(pipelineCount); IList <IPipelineStageDefinition> stageList = new List <IPipelineStageDefinition>(); foreach (string item in pipeLinelist) { PipelineStageDefinition <BsonDocument, BsonDocument> stageGroup = new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(item); stageList.Add(stageGroup); } BaseResultModel <T> baseResultModel = new BaseResultModel <T>(); BsonDocument bsonDocument = await mongodbBsonService.GetAggregateAsync(stageList); if (null != bsonDocument) { baseResultModel = BsonSerializer.Deserialize <BaseResultModel <T> >(bsonDocument); } else { baseResultModel.total = 0; baseResultModel.rows = new List <T>(); baseResultModel.success = false; return(baseResultModel); } #endregion //移除总数 pipeLinelist.Clear(); stageList.RemoveAt(stageList.Count - 1); #region 排序 pipeLineBsonHelper.AddSortList(sortDic, pipeLinelist); #endregion #region 分页 string pipelineSkip = "{$skip:" + ((search.PageIndex - 1) * search.PageSize) + "}"; string pipelineLimit = "{$limit:" + search.PageSize + "}"; pipeLinelist.Add(pipelineSkip); pipeLinelist.Add(pipelineLimit); #endregion #region 射 string pipelineProject = "{$project:{'_id':0,'id':0}}"; pipeLinelist.Add(pipelineProject); #endregion foreach (string item in pipeLinelist) { PipelineStageDefinition <BsonDocument, BsonDocument> stageGroup = new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(item); stageList.Add(stageGroup); } //baseResultModel.rows = await mongodbBsonService.GetListAggregateAsync(stageList); List <T> list = new List <T>(); foreach (var item in await mongodbBsonService.GetListAggregateAsync(stageList)) { var d = BsonSerializer.Deserialize <BsonDocument>(item); list.Add(BsonSerializer.Deserialize <T>(item)); } baseResultModel.rows = list; baseResultModel.success = true; return(baseResultModel); }
/// <summary> /// 根据施工许可证号码、身份证号码、日期分组统计考勤人数 /// </summary> /// <param name="search"></param> /// <returns></returns> public async Task <IEnumerable <ManagePostAttStatistics> > GetListStatisticsAsync(ManagePostAttPageSearch search) { if (null == search) { throw new ArgumentNullException($"查询条件{nameof(search.SearchDic)}不能为空!"); } Dictionary <string, string> searchDic = Newtonsoft.Json.JsonConvert.DeserializeObject <Dictionary <string, string> >(search.SearchDic.ToString()); PipeLineBsonHelper pipeLineBsonHelper = new PipeLineBsonHelper(); List <string> pipeLinelist = new List <string>(); #region 过滤(条件查询) pipeLineBsonHelper.AddMatchList(searchDic, pipeLinelist); #endregion #region 分组(用 $last排序,后续改为属性映射独立方法 ) string group = "{'ConstructPermitNum':'$ConstructPermitNum'"; group += ",'IdCard':'$IdCard'"; group += ",'day':{'$substr':[{'$add':['$AttendanceTime', 28800000]},0,10]}"; group += "}"; string pipelineGroup1 = @"{$group: { _id:" + group + @", 'ConstructPermitNum':{ '$first' :'$ConstructPermitNum'}, 'ProjectName':{ '$last' :'$ProjectName'}, 'ProjectGuid':{ '$last' :'$ProjectGuid'}, 'AttendanceTime':{ '$last':'$AttendanceTime'} } }"; pipeLinelist.Add(pipelineGroup1); string pipelineGroup2 = @"{$group: { _id:" + group + @", 'ConstructPermitNum':{ '$first' :'$ConstructPermitNum'}, 'ProjectName':{ '$last' :'$ProjectName'}, 'ProjectGuid':{ '$last' :'$ProjectGuid'}, 'AttendanceTime':{ '$last':'$AttendanceTime'}, 'AttPersonCount':{'$sum':1} } }"; pipeLinelist.Add(pipelineGroup2); #endregion #region 排序 if (null != search.SortDic) { Dictionary <string, string> sortDic = Newtonsoft.Json.JsonConvert.DeserializeObject <Dictionary <string, string> >(search.SortDic.ToString()); pipeLineBsonHelper.AddSortList(sortDic, pipeLinelist); } #endregion #region 射 string pipelineProject = "{$project:{'_id':0}}"; pipeLinelist.Add(pipelineProject); #endregion IList <IPipelineStageDefinition> stageList = new List <IPipelineStageDefinition>(); foreach (string item in pipeLinelist) { PipelineStageDefinition <BsonDocument, BsonDocument> stageGroup = new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(item); stageList.Add(stageGroup); } //baseResultModel.rows = await mongodbBsonService.GetListAggregateAsync(stageList); List <ManagePostAttStatistics> list = new List <ManagePostAttStatistics>(); foreach (var item in await mongodbBsonService.GetListAggregateAsync(stageList)) { var d = BsonSerializer.Deserialize <BsonDocument>(item); list.Add(BsonSerializer.Deserialize <ManagePostAttStatistics>(item)); } return(list); }
//按照指定的元属性值查找 public IActionResult query_zc_1(IFormCollection f) { var ysx_name = f["ysx_name"]; var ysx_value = f["ysx_value"]; try { //查询管理者维度 M_Db_Operate m_Db_Operate = new M_Db_Operate(); var collection1 = m_Db_Operate.database.GetCollection <Dimension_Information_Model>("Dimension_Information"); List <string> key1 = new List <string>(); key1.Add("TYPE"); List <string> value1 = new List <string>(); value1.Add("管理者维度"); var Result = m_Db_Operate.Inquire_Data(collection1, key1, value1); List <string> name = new List <string>(); for (var i = 0; i < Result.Count; i++) { name.Add(Result[i].NAME); } ViewData["glz_list"] = name; //在这里查询数据库并整合数据 M_Db_Operate m_Db_ = new M_Db_Operate(); var coll = m_Db_.database.GetCollection <YSX_Model>("Meta_attribute"); var res = m_Db_.Inquire_Data(coll, new List <string> { "Name" }, new List <string> { ysx_name.ToString() })[0]; //var collection = m_Db_Operate.database.GetCollection<SJ_ZC_Model>("ZC_data"); //List<string> key = new List<string>(); //List<string> value = new List<string>(); //key.Add("ZC_SX.mp"); //key.Add("ZC_SX.V"); //value.Add(res.ID); //value.Add(ysx_value.ToString()); //List <BsonDocument> result = m_Db_Operate.Get_Aggregate( // "ZC_data", // key, // value, // new List<string>() { "Meta_attribute" }, // new List<string>() { "ZC_SX.mp" }, // new List<string>() { "ID" }, // new List<string>() { "ZC_SXN" } // ); #region 聚合查询方法 IList <IPipelineStageDefinition> stages = new List <IPipelineStageDefinition>(); string match = "{$match:{ 'ZC_SX':{ 'V':'" + ysx_value.ToString() + "','mp':'" + res.ID + "'}}}"; string lookup = "{$lookup:{from: 'Meta_attribute',localField: 'ZC_SX.mp',foreignField: 'ID', as: 'ZC_SXN'}}"; string skip = "{$skip: 0}"; string limit = "{$limit:30}"; PipelineStageDefinition <BsonDocument, BsonDocument> stage1 = new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(match); PipelineStageDefinition <BsonDocument, BsonDocument> stage2 = new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(lookup); PipelineStageDefinition <BsonDocument, BsonDocument> stage3 = new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(skip); PipelineStageDefinition <BsonDocument, BsonDocument> stage4 = new JsonPipelineStageDefinition <BsonDocument, BsonDocument>(limit); stages.Add(stage1); stages.Add(stage2); stages.Add(stage3); stages.Add(stage4); PipelineDefinition <BsonDocument, BsonDocument> pipeline = new PipelineStagePipelineDefinition <BsonDocument, BsonDocument>(stages); var oll = m_Db_Operate.database.GetCollection <BsonDocument>("ZC_data"); List <BsonDocument> result = oll.Aggregate(pipeline).ToList(); List <List <string> > ZC_datas = new List <List <string> >(); for (var i = 0; i < result.Count; i++) { List <string> ZC_data = new List <string>(); string kz_sx = ""; int j = 0; while (true) { try { if (result[i]["ZC_SXN"][j]["Dimension_dic"]["资产类别维度"].ToString().Contains("通用集")) { string uu = result[i]["ZC_SXN"][j]["Name"].ToString() + ":"; int k = 0; while (true) { try { if (result[i]["ZC_SXN"][j]["ID"].ToString() == result[i]["ZC_SX"][k]["mp"].ToString()) { uu += result[i]["ZC_SX"][k]["V"].ToString(); break; } k++; } catch { ; } } ZC_data.Add(uu); } else { string uu = result[i]["ZC_SXN"][j]["Name"].ToString() + ":"; int k = 0; while (true) { try { if (result[i]["ZC_SXN"][j]["ID"].ToString() == result[i]["ZC_SX"][k]["mp"].ToString()) { uu += result[i]["ZC_SX"][k]["V"].ToString(); break; } k++; } catch { ; } } ZC_data.Add(uu); } } catch { break; } j++; } ZC_data.Add(kz_sx); ZC_datas.Add(ZC_data); } #endregion #region 其他方法 //var collection = m_Db_Operate.database.GetCollection<BsonDocument>("ZC_data"); //FilterDefinitionBuilder<BsonDocument> builderFilter = Builders<BsonDocument>.Filter; //var filter = builderFilter.ElemMatch("ZC_SX", builderFilter.And(builderFilter.Eq("V", ysx_value.ToString()), builderFilter.Eq("mp", res.ID))); //var result = collection.Find(filter).ToList(); //List <List<string>> ZC_datas = new List<List<string>>(); //FilterDefinitionBuilder<YSX_Model> builderFilter1 = Builders<YSX_Model>.Filter; //FilterDefinition<YSX_Model> filt = builderFilter1.And(builderFilter1.Ne("ID", "")); //var res2 = coll.Find(filt).ToList(); //for (var i = 0; i < result.Count; i++) //{ // List<string> ZC_data = new List<string>(); // string kz_sx = ""; // int j = 0; // while (true) // { // try // { // YSX_Model q = res2.TakeWhile(x => x.ID == result[i]["ZC_SX"][j]["mp"].ToString()).First(); // if (q.Dimension_dic["资产类别维度"].ToString().Contains("通用集")) // { // ZC_data.Add(q.Name.ToString() + ":" + result[i]["ZC_SX"][j]["V"].ToString()); // } // else // { // kz_sx += q.Name.ToString() + ":" + result[i]["ZC_SX"][j]["V"].ToString(); // } // } // catch // { // break; // } // j++; // } // ZC_data.Add(kz_sx); // ZC_datas.Add(ZC_data); //} #endregion ViewData["ZC_datas"] = ZC_datas; ViewData["sx_name"] = sx_name; } catch (InvalidCastException e) { ViewData["e"] = e.ToString(); return(View("query_ERROR")); } ViewData["wd_type"] = null; ViewData["wd_xx"] = null; ViewData["zc_name"] = null; ViewData["ysx_name"] = ysx_name; ViewData["ysx_value"] = ysx_value; return(View("query_zc")); }