Oracle Mapping to DateTimeOffset

[Cory]

Hi,

I'm trying to implement a Fluent Model that I can use for OData Web Api services. I know that it isn't fully supported but it's somewhat necessary for my project. I've manually changed the models to use DateTimeOffset instead of DateTime so that I can implement OData but I am unable to map these properties to Oracle DATE/TIMESTAMP/TIMESTAMP WITH TIME ZONE columns. 

Is this possible? Can I do it with the WithConverter method? And if so, how does it work? I couldn't find any working examples on the site.

Thanks.

 

[Ralph Waldenmaier]

Hi Cory,
Thank you for contacting us.
Unfortunately the CLR type DateTimeOffset is not yet supported for Oracle backends. Though you can use a DateTime CLR type and store it in a "TIMESTAMP WITH TIME ZONE" column. The timezone information will be preserved.

See the following mapping snippet as example:

productConfiguration.HasProperty(x => x.SomeDate).HasColumnType("timestamp with time zone");

I hope this information is helpful for you.
Do come back in case you have any other question.

Regards,
Ralph Waldenmaier
Telerik

 

 

[Cory]

Thanks. I actually have another question concerning the Fluent Model mapping which probably deserves its own thread but..

I need different mapping configurations for both SQL and Oracle. I was hoping I could create my own FluentModelMetadataSource classes for each back end but it doesn't seem to like that I have different mappings for each. If I comment out either one of the classes then I can build fine, but having both is giving me this type of error for every property that has a different Sql Type mapping:

Error 2 Found explicit primitive change to 'SqlType' which says that the old value 'TIMESTAMP' should be changed with 'datetime'. One can only add elements and not modify primitive properties of MetaItems.This has occurred on the MetaItem with name 'ActualStartTime' and type 'Telerik.OpenAccess.Metadata.Relational.MetaColumn'

What's the proper way to do this? I found a small bit in the documentation for what I'm trying to do but it was a little vague and only applied to strings and numbers. 

E.g. productConfiguration.HasProperty( x => x.ProductName ).WithFixedLength( 25 ).ToColumn( "Name" );

What about for other types such as Guids and DateTimes?

Being able to map Sql Types for each back ends would help me tremendously.

Thanks,

- Cory

[Ralph Waldenmaier]

Hello Cory,
You can perform backend independent mapping using either the built in backend Independent fluent configuration, therefore you can find more details here,  or you can modify the MetadataContainer during the OnDatabaseOpen phase.

Please see this snippet as an example.

protected override void OnDatabaseOpen(BackendConfiguration backendConfiguration, MetadataContainer currentMetadataContainer, MetadataContainer aggregatedMetadataContainer)
{
    var dates = currentMetadataContainer.PersistentTypes.SelectMany(x => x.Members).Where(x => x.MemberType.Name == "DateTime");
    foreach (var item in dates)
    {
        var mpm = item as MetaPrimitiveMember;
        if(mpm != null)
        {
            if (backendConfiguration.Backend == "oracle")
            {
                mpm.Column.SqlType = "timestamp with time zone";
            }
            else if (backendConfiguration.Backend == "mssql")
            {
                mpm.Column.SqlType = "datetimeoffset";
            }
        }
    }
}

Here I am searching for all members which have the type 'DateTime' and change their sql type based on the specified backend. Or course you are not limited 'DateTime' members but you can modify whatever you want. This could give you the needed flexibility.


Another approach would be to modify the default mapping depending on the backend. Please see this snippet.

protected override void OnDatabaseOpen(BackendConfiguration backendConfiguration, MetadataContainer currentMetadataContainer, MetadataContainer aggregatedMetadataContainer)
{
    if (backendConfiguration.Backend == "oracle")
    {
        metadataContainer.DefaultMapping.ClrMap.Add(new Telerik.OpenAccess.Metadata.Relational.DefaultTypeMapping()
        {
            ClrType = typeof(DateTime).FullName,
            SqlType = "timestamp with time zone"
        });
    }
    else if (backendConfiguration.Backend == "mssql")
    {
        metadataContainer.DefaultMapping.ClrMap.Add(new Telerik.OpenAccess.Metadata.Relational.DefaultTypeMapping()
        {
            ClrType = typeof(DateTime).FullName,
            SqlType = "datetime"
        });
    }
}

 
Please have in mind that an explicit definition in the fluent configuration will win. Meaning that if you have not specified anything in the fluent configuration for a datetime property, then the default mapping kicks in and will be used. If something is specified, the fluent configuration will win.

I hope this provides you with all the flexibility needed for your projects.
Do come back in case you have any other question.

Regards,
Ralph Waldenmaier
Telerik

 

 

[Cory]

Exactly what I was looking for, thanks!