以前專案基本上全部使用MySQL資料庫, 最近專案排期空出了一點時間leader決定把日誌模組遷移到插入/查詢效能更好的MongoDB上. 多條件查詢的寫法著實費了些功夫, 撰文記錄一下.
相關學習推薦：C#.Net開發圖文教程
一、準備工作
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);以上是.Net Core對MongoDB執行多條件查詢的詳細內容。更多資訊請關注PHP中文網其他相關文章！
