0

I'm developing a WCF RESTful service with C#, .NET Framework 4.0 and Entity Framework Code First.

I have this class (that represents a table on database):

[DataContract]
public class PostLine
{
    public int PostLineId { get; set; }

    [DataMember]
    public int? UserId { get; set; }

    [DataMember]
    public string Description { get; set; }

    [DataMember]
    public string DateUtc { get; set; }

    public User Author { get; set; }
}

And I'm trying to do this:

DateTime fourDaysAgo = DateTime.Now.Date.AddDays(-4);

var postLines = 
    context.PostLines.Where(p => DateTime.Compare(DateTime.Parse(p.DateUtc), fourDaysAgo) > 0).Include("Author");

But I get the following error:

{System.NotSupportedException: LINQ to Entities doesn't recognize the method 'System.DateTime Parse(System.String)', which can not be converted into an expression of the repository.

I need that PostLine.DateUtc to be a string because I'm going to use it on my web service, and send it as JSON, so its better for me to store it as a string.

If I use DateTime type, I'm going to get something like this on JSON response:

{
    "DateUtc": "/Date(1380924000000+0200)/",
    "Description": "post_1",
    "UserId": 1
}

Do you know how can I compare a string with a DateTime on a LINQ expression?

VansFannel
  • 45,055
  • 107
  • 359
  • 626
  • Why isn't `DateUtc` a `DateTime`? Your JSON serializer should be handling this parsing. – Tim S. Oct 05 '13 at 12:45
  • Yes, but how do you parse `"/Date(1380924000000+0200)/"` on iOS and on Android? – VansFannel Oct 05 '13 at 12:47
  • Relevant link: http://stackoverflow.com/questions/10286204/the-right-json-date-format So I can sympathize with the string property used here. –  Oct 05 '13 at 12:52
  • `1380924000000` is the milliseconds since the 1970 epoch, `+0200` is the time zone. It should be possible to parse that out with almost any date/time library. What is the `string` format used? It might be comparable with `string.Compare` if it's the ISO standard and no time zones are involved. – Tim S. Oct 05 '13 at 13:05
  • Could you send me your `web/app.config`? – Aron Oct 05 '13 at 18:34

4 Answers4

3

I think the best approach will be to split the property in two.

Entity Framework wants a DateTime property. That makes perfect sense.

For serialization you want a string property. That also makes perfect sense.

However, you're trying to use a single property for both, and that doesn't make sense, and isn't necessary.

[DataContract]
public class PostLine
{
    ...

    public DateTime DateUtcAsDateTime { get; set; }

    [DataMember, NotMapped]
    public string DateUtcAsString {
        get { return DateUtcAsDateTime.ToString(); }
        set { DateUtcAsDateTime = DateTime.Parse(value); }
    }

    ...
}

Now, DateUtcAsDateTime will be used by Entity Framework, and DateUtcAsString will be ignored by Entity Framework as it has a NotMapped attribute.

DateUtcAsString, on the other hand, is the only one of these properties that has a DataMember attribute, so should be the only one that gets serialized.

You can of course rename one of these properties back to DateUtc, if you want.

Update: as Matt Johnson points out, an improvement would be to specify the format in a way that always results in the exact same string. This ensures your strings don't change, just because your code gets moved to another server that happens to have different regional settings.

[DataMember, NotMapped]
public string DateUtcAsString {
    get { return DateUtcAsDateTime.ToString("o"); }
    set { DateUtcAsDateTime = DateTime.Parse(value, "o", null, DateTimeStyles.AssumeUniversal | DateTimeStyles.AdjustToUniversal); }
}

Note that I am using DateTimeStyles.AssumeUniversal | DateTimeStyles.AdjustToUniversal instead of the DateTimeStyles.RoundTripKind that he suggested, because the name DateUtc strongly suggests you always want UTC, never local time. And I'm not specifying any culture explicitly, as the "o" format is already independent of culture.

You could, if it's easier for your other code to handle, use "r" instead of "o" with the same benefits.

  • 1
    -1 Serialization should not affect the implementation of the class. It is possible to change the behavior of the JsonDataContractSerializer and Newtonsoft's Serializer. – Aron Oct 05 '13 at 18:17
  • @Aron I agree that that would probably be even better, and if you post an answer that gets that working, so long as it's done in a way that has no unexpected side effects (including other code that may rely on other `DateTime` properties being serialised the way they are now), it gets my vote. I've taken a similar approach to this answer myself in slightly different scenarios, because the extensibility I would otherwise need was simply missing, but perhaps that is not the case for this question. –  Oct 05 '13 at 18:28
  • @Aron BTW, thanks for explaining, that's always nicer than a downvote without comment. –  Oct 05 '13 at 18:30
  • I need to know the serializer that is being used. The two standards are the DataContractJsonSerializer and Newtonsoft's Json.Net...I think that DataContractJsonSerializer is being used...But without knowing how the serializer is being created I can't tell you how to set the Serializer DateTimeFormat property. – Aron Oct 05 '13 at 18:30
  • This approach isn't horrible, but it's certainly noisy. If anything, the `"o"` string format and invariant culture should be specified, and it should probably use `ParseExact` with `RoundTripKind`. – Matt Johnson-Pint Oct 06 '13 at 01:26
  • @MattJohnson I agree with the `"o"` (or `"r"`, if sub-second precision is not needed, and that format happens to be easier to process on the other end), but if you're using a format that doesn't depend on culture, culture doesn't need to be specified anymore. As for `RoundTripKind`, the property is named `DateUtc`, so I think `AssumeUniversal | AdjustToUniversal` would make more sense, but the documentation suggests these don't really work right. I will edit my answer when I can do some testing (and it's possible that testing will show that your suggestion actually works better) –  Oct 06 '13 at 08:31
2

This is certainly an XY problem if I ever saw one. You're asking about comparing datetime as strings in Entity Framework, while it seems the real problem is that you don't like the default date format of the DataContractJsonSerializer that WCF uses by default.

Part of the problem is that you are mixing local and UTC. You're getting /Date(1380924000000+0200)/, which contains the local time zone offset of the server. This is because you started from DateTime.Now, which has a .Kind of DateTimeKind.Local.

If you instead used DateTime.UtcNow, it would have a .Kind of DateTimeKind.Utc, and would be serialized as /Date(1380924000000)/. And yes, the numerical portion of the format would be the same. Even when there is an offset specified, the number part is still related to UTC.

That's just one problem with this format. The other is that while DataContractJsonSerializer writes the local offset during serialization, it doesn't use it properly during deserialization. It just assumes that if any offset is provided, that the time should be local - even if the computer doing the deserialization has a completely different offset.

The better format to use in JSON is the ISO8601 format. For example, this UTC value would look like 2013-10-04T22:00:00.000Z. And while you can easily pass in a different date format to DataContractJsonSerializer if you use it directly, WCF doesn't easily expose this to you.

You could go down the route of changing the DataContractJsonSerializer settings via a custom WCF message formatter, such as described here, here and here, but it gets complicated very quickly. Be careful if you do this, and be sure to test thoroughly!

Another idea would be to write a custom WCF message formatter that uses JSON.Net instead of DataContractJsonSerializer. JSON.Net uses the ISO8601 format by default, so you would be set.

But honestly, the best solution is to not try to use WCF to build your REST endpoints. The date format issue is just the beginning. There are all sorts of other problems that can pop up along the way. Instead, use a modern framework that is designed for this purpose, such as ASP.Net WebAPI, or ServiceStack.

ASP.Net WebAPI uses JSON.Net, and ServiceStack has it's own JSON serializer called ServiceStack.Text. JSON.Net uses ISO8601 as it's default date format. If you use Service Stack, you'll need to set JsConfig.DateHandler = JsonDateHandler.ISO8601;

So in recap, you have two XY problems:

  • First, you chose WCF to build your rest endpoints, which was so problematic that the industry developed other solutions.
  • Second, you couldn't get DateTime to emit the correct format, so you decided to treat it as a string, which then you couldn't compare back to a DateTime in an EF query.

And yes, I realize I didn't answer your question of how to compare a DateTime input against a string property in Entity Framework, but I think now you can see why. If you really want to go down that road, you might find something useful in SqlFunctions or EntityFunctions - but even then, how are you going to build an efficient index on this column in your database? Querying will be really slow if you get a lot of data. I would advise against it.

Community
  • 1
  • 1
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • I checked the EF functions. There isn't a `DateTime.Parse` but come to think of it, you might be able to just use `Convert` or unsafe cast. Still at the end of the day, SEPARATION Of CONCERN people. Your Serialization logic and your persistence logic should be separate! But yeah...brilliant umm...not an answer. – Aron Oct 06 '13 at 04:22
1

I don't know how you are instantiating your DataContractJsonSerializer. If you are directly instantiating it...you can pass a DataContractJsonSerializerSettings, with a DateTimeFormat for setting how DateTime are serialized.

If you are using a behavior to instantiate your Serializer things are a little more complicated.

Aron
  • 15,464
  • 3
  • 31
  • 64
  • WCF creates the `DataContractJsonSerializer` automatically, and uses the constructor that doesn't supply any `DataContractorJsonSerializerSettings` or `DateTimeFormat` object. If you dig deep enough, you can find it in `System.ServiceModel.Dispatcher.SingleBodyParameterDataContractMessageFormatter.CreateSerializer`. – Matt Johnson-Pint Oct 06 '13 at 01:22
  • Well in theory you can inject your own with a behavior. However I agree that you really want to use WebAPI, which strangely enough uses Newtonsoft Json.NET (man they royally screwed up the `DataContractJsonSerializer`). – Aron Oct 06 '13 at 04:19
0

If you really want to go down the route of using a string for your class when transfering your data to your client, you should have a separate DTO class.

You can then use a library like AutoMapper to map your PostLine class to PostLineDto with an expression.

However the next problem you will face then is that your Expression<Func<PostLine,PostLineDto>> will contain Expression.MethodCall(DateTime.Parse) and you will have to inject an ExpressionVisitor that can convert...

p => p.DateUtc > DateTime.Parse("2013 Aug 1") - bool

into

p => p.DateUtc > new DateTime(1, Aug, 2013) - bool

Which is a real pain.

Aron
  • 15,464
  • 3
  • 31
  • 64