Mapping ID Guid on Oracle

[Felipe Oriani]

Hi guys, 

We have a table on the database where we want to map the ID as Guid. Our project should run over two databases, Sql Server and Oracle. For Sql Server, we define the column type as uniqueidentifier and it works fine. For Oracle, we try to define it as char(36), raw(36), varchar2(36) but it does not work properly.

After the commit of the transaction, NHibernate generate an ID different what we have on the ID property of the model mapped.

We are mapping it using Fluent NHibernate:

Id(x => x.Id).Column("ID").GeneratedBy.GuidComb();

The question is, how can we map it on Oracle? What is the best column dataType to define? Should we create an IUserType to map it?

Thank you.

--

______________________________________
Felipe B Oriani

felipe...@gmail.com

[mwpowellhtx]

On Friday, December 23, 2016 at 7:22:44 AM UTC-5, Felipe Oriani wrote:

Hi guys, 

We have a table on the database where we want to map the ID as Guid. Our project should run over two databases, Sql Server and Oracle. For Sql Server, we define the column type as uniqueidentifier and it works fine. For Oracle, we try to define it as char(36), raw(36), varchar2(36) but it does not work properly.

The first question I would ask is, why are you mixing providers? Make a decision and stick with it. 

After the commit of the transaction, NHibernate generate an ID different what we have on the ID property of the model mapped.

We are mapping it using Fluent NHibernate:

Id(x => x.Id).Column("ID").GeneratedBy.GuidComb();

Likely you've got some sort of decision tree configured in your Dependency Injection Container as to which provider to use. You may need a similar thing for your mapping(s). 

The question is, how can we map it on Oracle? What is the best column dataType to define? Should we create an IUserType to map it?

I haven't mapped Guid to Oracle, but I have mapped it to MySQL before; it's possible, but I definitely prefer having the database support. You might lose support for things such as NEWSEQUENTIALID(), but you should at least be able to provide some sort of user defined function in the database to fill the gap, and/or a default value function. You would also lose support for things like GeneratedBy and GuidXYZ.

http://stackoverflow.com/questions/6408714/working-with-fluent-nhibernate-and-guid-ids

Short of training the Service provider with some sort of column oriented conventions, I would look at least common denominators; such as VARCHAR(36) across database providers.

SYS_GUID: https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions153.htm

If it's still something you really want to do after weighing these decisions, I would go with Oracle RAW(16) and SQL Server BINARY(16) (i.e. Guid to Byte[]).

Yes, which will either require a second property/field, converting the modeled Guid ID, or an IUserType, most likely.

Thank you.

Good luck!