The Transact-SQL has date (Format: YYYY-MM-DD) type, but there isn’t a CLR type representing date type. Entity Framework (EF) only supports to use
System.DateTime CLR type to map the date type.
OData V4 lib provides a CLR
struct Date type and the corresponding primitive type kind Edm.Date. Web API OData V4 supports it. However, EF doesn’t recognize this CLR type, and it can’t map
struct Date directly to date type.
So, this doc describes the solution about how to support Edm.Date type with Entity Framework. Meanwhile, this doc also covers the Edm.TimeOfDay type with EF.
It should support to map the type between date type in Database and Edm.Date type through the CLR
System.DateTime type. The map is shown in the following figure:
So, it should provide the below functionalities for the developer:
- Can configure the
System.TimeSpanproperty to Edm.Date/ Edm.TimeOfDay.
- Can serialize the date/ time value in the DB as Edm.Date /Edm.TimeOfDay value format.
- Can de-serialize the Edm.Date/Edm.TimeOfDay value as date/ time value into DB.
- Can do query option on the date/ time value.
Most important, EF doesn’t support the primitive collection. So, Collection of date is not in the scope. The developer can use navigation property to work around.
Date & Time type in SQL DB
Below is the date & time type mapping between DB and .NET:
So, From .NET view, only
System.DateTime is used to represent the date value, meanwhile only
System.TimeSpan is used to represent the time value.
Date & time mapping with EF
In EF Code First, the developer can use two methodologies to map
System.DateTime property to date column in DB:
1 Data Annotation
The users can use the Column Data Annotation to modify the data type of columns. For example:
“date” is case-insensitive.
2 Fluent API
HasColumnName is the Fluent API used to specify a column data type for a property. For example:
For time type, it implicitly maps the
System.TimeSpan to represent the time value. However, you can use string literal “time” in DataAnnotation or fluent API explicitly.
CLR Date Type in ODL
OData Library defines one
struct to hold the value of Edm.Date (Format: YYYY-MM-DD).
Where, Edm.Date is the corresponding primitive type Kind.
OData Library also defines one struct to hold the value of Edm.TimeOfDay (Format: HH:MM:SS. fractionalSeconds, where fractionalSeconds =1*12DIGIT).
Where, Edm.TimeOfDay is the corresponding primitive type Kind.
Configure Date & Time in Web API by Fluent API
By default, Web API has the following mapping between CLR types and Edm types:
We should provide a methodology to map
System.DateTime to Edm.Date type, and
System.TimeSpan to Edm.TimeOfDay type as follows:
We will add the following extension methods to re-configure
For example, the developer can use the above extension methods as follows:
Configure Date & Time in Web API by Data Annotation
We should recognize the Column Data annotation. So, we will add a convention class as follows:
In this class, it will identify the Column attribute applied to
System.TimeSpan property, and call
AsTimeOfDay() extension methods to add a Date or TimeOfDay mapped property. Be caution, EF supports the TypeName case-insensitive.
After insert the instance of ColumnAttributeEdmPropertyConvention into the conventions in the convention model builder:
For example, the developer can do as follows to build the Edm model:
Now, the developer can call as follows to build the Edm model:
System.DateTime value to Edm.Date
We should modify
ODataComplexTypeSerializer to identify whether or not the
System.DataTime is serialized to Edm.Date. So, we should add a function in
System.TimeSpan value to Edm.TimeOfDay
Add the following codes into the above function:
Top level property
If the end user want to query the top level property, for example:
The developer must take responsibility to convert the value into its corresponding type.
Edm.Date to System.DateTime value
It’s easy to add the following code in
EdmPrimitiveHelpers to convert
struct Date to
Edm.TimeOfDay to System.TimeSpan value
Add codes in
EdmPrimitiveHelpers to convert
struct TimeOfDay to
Query options on Date & Time
We should to support the following scenarios:
Fortunately, Web API supports the most scenarios already, however, we should make some codes changes in
FilterBinder class to make TimeOfDay scenario to work.
We re-use the Customer model in the Scope. We use the Lambda expression to build the Edm Model as:
Here’s the metadata document:
We can query:
We can do filter:
~/Customers?$filter=Birthday eq 2017-12-31