none
How to specify DateTimeKind of DateTime Property?

    Question

  •  

    Hi All,

    What I need:

    DateTime lastModified = entity.LastModified;

    Assert(lastModified.Kind == DateTimeKind.Utc);

     

    I use SS2000 / SS2005. The column ‘lastmodified’ is of type SQL Type: ‘datetime’. I my ObjectServices layer I use these values as UTC. How can I change the kind of the propertyvalue?

    What I tried:

    1. In CSDL I set the Facet ‘DateTimeKind’to Utc.

    <Property Name="lastmodified" Type="DateTime" Nullable="false" DateTimeKind="Utc" />

    But then the validation failed:

    Error 2041: There is no store type that maps to edm type 'DateTime'  with the following facet values  'DateTimeKind=Utc, PreserveSeconds=True'.  Please change the type or facets of property 'lastmodified' in csdl to match with one of the store types.

    Then I tried:

    2.  In SSDL I added the Facet ‘DateTimeKind’ to Utc  - I don’t like that :-)

    <Property Name="lastmodified" Type="datetime" DateTimeKind="Utc" Nullable="false" />

    But then I got the error:

    Error 64: Facet 'DateTimeKind' must not be specified for type 'datetime'.

    3. So I decided to try with CodeGeneration (CustomEntityClassGenerator). I wanted something like this:

    //ORIGINAL:

    this._lastmodified = StructuralObject.SetValidValue(value, DateTimeKind.Unspecified, true);

    // What I need:

    this._lastmodified = StructuralObject.SetValidValue(valueDateTimeKind.Utc, true);

    But there is no chance to change the FacetCollection of EdmProperty.

    EdmProperty Item = ….;

    Item.TypeUsage.Facets["DateTimeKind"] = DateTimeKind.Utc;

     

    So, How To specify DateTimeKind of DateTime Property?

    Thanks, BENHAU

     

    Monday, March 31, 2008 11:00 AM

Answers

  • Since we do not know wether the datetime column value in the database is a UTC value or not, we create the DateTime properties with "Unspecified" DateTimeKind when materializing the objects. Using the DateTimeKind facet does not change the default materialization semantics. In the next public EF beta, we are actually planning to remove the DateTimeKind facet completely from CSDL.

    Here are a couple of ways I can think of for working around the problem:

    1. You can add a property on the partial class that returns the DateTime value whose Kind is set to UTC and in your ObjectServices layer work with this new property. The disadvantage with using this approach will be that you won't be able to use this property in Esql queries.

     Ex :

    public partial class Orders

    {

      public DateTime OrderDateUtc

        {

          get

          {

            return new DateTime(this.OrderDate.Value.Ticks, DateTimeKind.Utc);

          }

          set

          {

             this.OrderDate = value;

          }

        }

    }

     

    2. In SqlServer 2008, there is a new Data type called DateTimeOffset which might be more suitable if you are always storing UTC datetime values. EF will start supporting this type in the next public Beta and it will be mapped to System.DateTimeOffset type in the CLR. If you are planning to move to Sql 2008, you could try this approach.

     

    Thanks

    Srikanth

    Monday, March 31, 2008 9:20 PM

All replies

  • Since we do not know wether the datetime column value in the database is a UTC value or not, we create the DateTime properties with "Unspecified" DateTimeKind when materializing the objects. Using the DateTimeKind facet does not change the default materialization semantics. In the next public EF beta, we are actually planning to remove the DateTimeKind facet completely from CSDL.

    Here are a couple of ways I can think of for working around the problem:

    1. You can add a property on the partial class that returns the DateTime value whose Kind is set to UTC and in your ObjectServices layer work with this new property. The disadvantage with using this approach will be that you won't be able to use this property in Esql queries.

     Ex :

    public partial class Orders

    {

      public DateTime OrderDateUtc

        {

          get

          {

            return new DateTime(this.OrderDate.Value.Ticks, DateTimeKind.Utc);

          }

          set

          {

             this.OrderDate = value;

          }

        }

    }

     

    2. In SqlServer 2008, there is a new Data type called DateTimeOffset which might be more suitable if you are always storing UTC datetime values. EF will start supporting this type in the next public Beta and it will be mapped to System.DateTimeOffset type in the CLR. If you are planning to move to Sql 2008, you could try this approach.

     

    Thanks

    Srikanth

    Monday, March 31, 2008 9:20 PM
  •  

    Hi Srikanth,

     

    Thanks for the suggestion, but:

     

    Option2: Due to backwards compatibility I can not use SS2008 features. Our customers use SS2000 && SS2005.

     

    Option1: Unfortunately Orders should has property 'OrderDate' based on Kind=Utc and not like 'OrderDateUtc', 'OrderDateLocal' or 'OrderDateUnspecified', cause all "LINQ 2 Entities" expressions are built on 'OrderDate' and not on 'OrderDateXXX’.

     

    Question: "...Since we do not know wether the datetime column value in the database is a UTC value or not, we create the DateTime properties with "Unspecified" DateTimeKind when materializing the objects..."

     

    I’m confused a little:

    1.     In SS05 the column is DateTime(Unspecified) -> SSDL -> Property type=”datetime” kind=”Unspecified”> -- That’s OK. Which means “…Since we do not know wether the datetime column value in the database is a UTC value or not…” -> The answer is “Unspecified”

     

    2.     CSDL: Property type=”DateTime” Kind=”Utc” -> Designer’s decision! “…we create the DateTime properties with "Unspecified" DateTimeKind when materializing the objects..." -> Why? You known it now! -> The DateTime’s ticks should be UTC and not Unspecified or Local. So when the ‘value’ of property setter is not of kind Utc, the input should be converted. And that is exactly what the method ‘SetValidValue’does.

     

    Cheers, BENHAU

     

    Tuesday, April 01, 2008 7:19 AM
  • The way we are enforcing the facets( that the user has specified in the CSDL ) in Object Services layer is very inconsistent in Beta 3 as you probably found out while trying out the DateTimeKind facet. There were some facets that we were validating against and some we were not. And if the class is not a generated class but a IPOCO implementation then it is not guarenteed to comply with the facets on the CSDL.

    Having seen all these problems, we are actually making facets optional in our next public Beta and the EF stack will not reason about them( neither validates nor uses these facets). The only reason we are allowing them is because there are some tools( in plans) that produce the database from the CSDL and they use the facets when they are specified on the CSDL.

     

    I know this might not answer your question completely but I can't think of a easy way to always get back a UTC DateTime property from a datetime column in the database.

     

    Thanks

    Srikanth

     

    Tuesday, April 01, 2008 7:49 AM