无法筛选通过日期列存储在Mongo DB中的字符串 [英] Unable to filter through date column which is stored as string in Mongo DB

问题描述

我有一个将数据写入MONGODB的应用程序。

在文档中，我有一个名为UpdatedOn的字段。在此，我以类似以下的字符串格式编写日期时间：

I have a application which writes data into MONGODB.
In the document, I have a field called UpdatedOn. In this I'm writing datetime in string format like below:

DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss")

我知道我应该只使用日期类型，这被存储为现在在数据库中是字符串。

现在我需要根据两个日期之间的此字段来过滤数据。

像这样说：

开始日期： 01/01/2019

结束日期： 31/01/2019

I know I should have used date type only,this is being stored as string in the database now.
Now I got a requirement to filter data based on this field between 2 dates.
Say something like this :
Start Date : "01/01/2019"
End Date : "31/01/2019"

这是代码，我在下面使用了（

This is the code , I have used below (which is not working)

IMongoCollection<Order> OrderCollection = GetOrderCollection();
List<OrderFilter> lstJobs;

FilterDefinitionBuilder<Order> OrderFilter = Builders<Order>.Filter;

DateTime start = Convert.ToDateTime("01/01/2019");   
DateTime end = Convert.ToDateTime("31/01/2019");

var filter = OrderFilter.Gte("UpdatedOn", start) &
OrderFilter.Lt("UpdatedOn", end);

var fields = Builders<Order>.Projection.Include(p => p.Id);
lstOrders = await OrderCollection.Find(filter).Project<OrderFilter>(fields).ToListAsync<OrderFilter>().ConfigureAwait(false);

OrderFilter类：

OrderFilter class:

public class OrderFilter
{    
   [DataMember(Name = "id")]
   public string Id { get; set; }
}

订单等级：

public class Order
{
   [DataMember(Name = "id")]
   public string Id { get; set; }

   [DataMember(Name = "UpdatedOn")]
   public string UpdatedOn { get; set; }
}

由于UpdatedOn是字符串，已经插入了一些数据。更改它不是实践。

在这种情况下，谁能帮助我进行过滤。我可以在代码本身中进行任何类型转换或转换并进行过滤。

非常感谢！

Since UpdatedOn is string and already some data has been inserted. It is not practicle to change it.
Can anyone help me to filter in this case. Is there any typecasting or conversions I can do in the code itself and do the filtering.
Many thanks!

推荐答案

您必须将字符串解析为Date才能将其他Date与之比较。
请参见此处的文档。

You'll have to parse the string to Date to be able to compare other Dates to it. See the docs here.

要在C＃中执行此操作，必须使用如下所示的Aggregate方法：

To be able to do this in C# you'll have to use the Aggregate method like this:

DateTime start = Convert.ToDateTime("01/01/2019");   
DateTime end = Convert.ToDateTime("31/01/2019");

var projectionDefinition = 
Builders<BsonDocument>.Projection.Exclude("convertedDate");

OrderCollection.Aggregate().AppendStage<BsonDocument>("{ $addFields: {convertedDate: { $toDate: '$UpdatedOn' }}}").Match(
    Builders<BsonDocument>.Filter.Gte(x => x["convertedDate"], new BsonDateTime(start))
    & Builders<BsonDocument>.Filter.Lte(x => x["convertedDate"], new BsonDateTime(end)))
    .Project(projectionDefinition).As<Order>();

通过使用阶段，您也许可以使它更加漂亮。

You'll probably be able to make this a lot prettier by using stages.

例如，

var projectionDefinition = Builders<BsonDocument>.Projection.Exclude("convertedDate");
var expression = new BsonDocument(new List<BsonElement>
{
    new BsonElement("convertedDate", new BsonDocument(new BsonElement("$toDate", "$UpdatedOn")))
});

var addFieldsStage = new BsonDocument(new BsonElement("$addFields", expression));

var gteFilter = Builders<BsonDocument>.Filter.Gte(x => x["convertedDate"], new BsonDateTime(startDate));
var lteFilter = Builders<BsonDocument>.Filter.Lte(x => x["convertedDate"], new BsonDateTime(endDate));

var combinedFilter= Builders<BsonDocument>.Filter.And(gteFilter, lteFilter);

var result = coll.Aggregate().AppendStage<BsonDocument>(addFieldsStage).Match(combinedFilter).Project(projectionDefinition).As<Order>();

如果在上面添加 [BsonIgnoreExtraElements]

或者在您的情况下，可以将其替换为当前使用的投影并设置 .As< Type> 。

Or in your case probably just replace it with the projection you are currently using and setting the .As<Type> accordingly.

这篇关于无法筛选通过日期列存储在Mongo DB中的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！