Comparing dates in Mongodb with C# LINQ driver

Question

I have a MongoDB collection with a string field representing a date. In my C# app I map the results to a class with an aggregate field that convert that string to a DateTime object

[BsonIgnoreExtraElements]
public class Tweet
{
    public ObjectId Id { get; set; }

    [BsonElement("text")]
    public string Texto { get; set; }

    [BsonElement("created_at")]
    public string Fecha { get; set; }

    public DateTime FechaConvertida
    {
        get
        {
            var formato = "ddd MMM dd HH:mm:ss zzzz yyyy"; //'Sun Oct 23 19:42:04 +0000 2016'
            var enUS = new CultureInfo("en-US");
            var fechaConvertida = DateTime.ParseExact(this.Fecha, formato, enUS, DateTimeStyles.None);
            return fechaConvertida;
        }
    }
}

Then on my api I make a query filtering elements between two dates (using 'CSharp Driver LINQ')

public IEnumerable<Tweet> GetTweetsDePeriodo(string nombreColeccion, int dias)
    {
        var hoy = DateTime.Today;
        var fechaInicial = hoy.AddDays(-dias);

        var coleccion = _db.GetCollection<Tweet>(nombreColeccion).AsQueryable<Tweet>();
        var tweetsFiltrados = (from c in coleccion
                               where c.FechaConvertida >= fechaInicial
                               select c
                               ).ToList();
        return coleccion;
    }

And then I get the following error: *An unhandled exception occurred while processing the request. InvalidOperationException: {document}.FechaConvertida is not supported

Any idea? Thanks in advance,

@viveknuna I've tried it but doesn´t change anything. Thanks anyway, — Pedraz
– Pedraz, Commented Nov 7, 2016 at 9:07
@Dudemanword The tweet data object is a big json, in fact it´s just the object that returns the Twitter API. In this case, the relevant thing is the format of the string with date that returns in the field "Created_at" that is pretty strange: 'Sun Oct 23 19:42:04 +0000 2016'. I think that this is the main problem because the library is not able to parse it. Thanks for your help. — Pedraz
– Pedraz, Commented Nov 7, 2016 at 9:10

Pedraz · Accepted Answer · 2016-11-07 17:45:05Z

Finally I've found a solution using a custom serializer for the Date field. This is how it looks like.

My class:

[BsonIgnoreExtraElements]
public class Tweet
{
    public ObjectId Id { get; set; }

    [BsonElement("text")]
    public string Texto { get; set; }

    [BsonElement("created_at")]
    [BsonSerializer(typeof(FechaTweetsSerializer))]
    public DateTime Fecha { get; set; }
}

And my custom serializer:

public class FechaTweetsSerializer : SerializerBase<DateTime>
{

    public override void Serialize(BsonSerializationContext context, BsonSerializationArgs args, DateTime value)
    {
        context.Writer.WriteString(value.ToString(CultureInfo.InvariantCulture));
    }

    public override DateTime Deserialize(BsonDeserializationContext context, BsonDeserializationArgs args)
    {
        var fecha = context.Reader.ReadString();
        return ConvertirFecha(fecha);
    }

    private DateTime ConvertirFecha(string fechaFormatoTwitter)
    {
        var formato = "ddd MMM dd HH:mm:ss zzzz yyyy"; //'Sun Oct 23 19:42:04 +0000 2016'
        var enUS = new CultureInfo("en-US");
        var fechaConvertida = DateTime.ParseExact(fechaFormatoTwitter, formato, enUS, DateTimeStyles.None);
        return fechaConvertida;
    }
}

Hope it helps someone.

pieperu · Accepted Answer · 2016-11-04 15:55:39Z

1

As mentioned already in the comments, if you were to declare your property in your model as a Datetime

[BsonElement("created_at")]
public DateTime Fecha { get; set; }

then it shouldn't be a problem. You can also decorate your property using BsonRepresentation in order to ensure the correct (de)serielization happens

[BsonRepresentation(BsonType.DateTime)]
[BsonElement("created_at")]
public DateTime Fecha { get; set; }

And to perform your lookup a simple solution would be

var coleccion = _db.GetCollection<Tweet>(nombreColeccion).Find(x => x.Fecha >= fechaInicial).ToList();

Creating a c# property to parse the string as a date, and then referencing that in your filter will not work because the conversion is not happening in the database scope, but rather in the c# code. If storing the date as a DateTime (MongoDb ISODate()) is not an option, you could look into Project() to project a converted date from your string, and then perform your query

answered Nov 4, 2016 at 15:55

pieperu

2,7823 gold badges22 silver badges31 bronze badges

1 Comment

Pedraz Over a year ago

I've tried to declare the field as DateTime and added the 'BsonRepresentation' tag, but it throws an exception: FormatException: String was not recognized as a valid DateTime The problem is that the string stored in BD has a weird datetime format: 'Sun Oct 23 19:42:04 +0000 2016'. Do you know a way to serialize that 'on the fly'? Maybe a custom Bson serializer? Thanks for your help

Collectives™ on Stack Overflow

Comparing dates in Mongodb with C# LINQ driver

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related