Oracle: how to deal with an identity fiel of type RAW?

[Nestor Soriano Vilchez]

I have an Oracle 10g database in which the ASP.NET membership tables have been created by using the scripts available when installing Oracle ODP.NET. I want to handle these tables by using OpenAccess, so I have generated an entity diagram from the database and I have included these tables.

I have a problem when I try to invoke the context.GetAll method for one of these tables. I get the following exception:

Telerik.OpenAccess.Exceptions.MetadataException
Message=The metadata for field 'APPLICATIONID1' of class 'OpenAccessTest.ORA_ASPNET_APPLICATION' cannot be initialized: Type converter initialization failed. The converter with name 'String2GuidConverter' does not convert from CLR type 'System.Guid' to SQL type 'RAW'.
Nombre del parámetro: converterName

It turns out that these tables have some fields of type RAW, that actually hold GUIDs. OpenAccess correctly assumes that the appropriate data type for the generated entity class should be System.Guid, but as I have read in the documentation the RAW fields must be mapped to the type byte[]. So I have changed the data type of all the RAW columns to byte[] on the entity designer.

However now I have another problem. When I try to compile the project I get the following error:

Field: 'System.Byte[] EntityFrameworkTest.ORA_ASPNET_MEMBERSHIP::USERID1': The type of the single field identity field must be System.Byte, System.Int16, System.Int32, System.Int64, System.Guid, System.Char or System.String.
Parameter name: fieldType

So I can't compile the project because the column is not mapped to byte[], but I can't map to byte[] because the colum is an identity field.

What can I do then?

[Damyan Bogoev]

Hello Nestor Soriano Vilchez,

Please change the data type of all columns that hold the Guid values to RAW.
I implemented custom converters for your case. You could find attached a sample application which demonstrates how to work with the custom converters for Guid property mapped to a Varchar/Varchar2/Raw column. The example is working against MSSQL but the approach can be applied to Oracle backend without any modifications.
You should set the correct converters for each of Guid properties mapped to Varchar2 columns in your project using the following approach:
1.   Create partial class for the YourContextName class;
2.   Create a static constructor and initialize the MetaColumn.Converter property(see the DataContext.partial.cs file);
The internal build with the fix will be available in the next few weeks.
Hope that helps.

All the best,
Damyan Bogoev
the Telerik team

[Nestor Soriano Vilchez]

Ok, it worked, but I had to make a small change in your code.

In GuidTypeConverter.cs, line 49, you have the following:

Guid guid = data.Reader.GetGuid(data.Position);

This throws a NotSupportedException. I have changed it to the following two lines:

byte[] guidValue = (byte[])data.Reader.GetValue(data.Position);
Guid guid = new Guid(guidValue);

and now it works fine.

[Damyan Bogoev]

Hello Nestor Soriano Vilchez,

I am glad to see that you managed to resolve the problem.
This cast is needed if the column that stores the Guid value is of byte[] type.
If any other questions arise please contact us back.

Best wishes,
Damyan Bogoev
the Telerik team

[Jiri]

Hi there, there's another small change in Write method (if you're using oracle and raw(16) column for guid values):

you have to use this piece of code :

parameter.DbType = DbType.Binary;
parameter.Size = 16; //byte[16]

instead of this

parameter.DbType = DbType.Guid;

@Damyan Bogoev  : is there any possibility to have it implemented in ORM ? Not all the cases but the one for raw(16) which is in my opinion in most cases used as holder for guid data on oracle (pk's and fk's also).

Thx & Best regards

Jiri

[Damyan Bogoev]

Hi Jiri,

Firstly I want to thank you for the valuable input.
We will consider your suggestion in the future improvements of the product.

All the best,
Damyan Bogoev
the Telerik team

[Grigoriev]

Greate job!

[Laurent]

Hello,

2 years later and we still have to modify our mapping for every guid. I don't get the point of storing guid as varchar.
Do you think it will be updated in a next version or not?

best regards,

Laurent

[Damyan Bogoev]

Hello Laurent,

I am sorry for your inconvenience caused.

You could add a feature request in the product’s Ideas and Feedback portal, where you can vote for it and track its progress. If this appears to be a common request, it will be included in the future versions of the product.

Regards,
Damyan Bogoev
Telerik

[Jiri]

And that's it. And it is always like that when you're requesting more advanced feature, they told you just add it somewhere maybe you'll be lucky enought and somebody will vote for it.

Once upon a time they told me (as an answer for my request) that the requested feature is scheduled/planed for next release, after some time and some releases later, they told me that this feature will be added to the project backlog and maybe there will be some time to implement it. Now they have Ideas and Feedback portal, wow such a nice customer service. To my understanding this is a nicer way for saying forget about it and use workaround (if there's any) or don't use this feature at all if you want to use our product.

I see the future clearly now ... They will be starting to suggest that if we already have the source code, we can take it and implement the requested feature by ourselves :) Well done folks, well done :)

So it is up to us, we can live with it with hope that someday somebody will implement it or just try to find product that will better suits our needs.

I am sorry for your inconvenience caused :)

Best regards

Jirka

Ps: "Trifles make perfection, and perfection is no trifle." - Michelangelo Buonarroti

[Ivailo]

Hello Jiri,

I agree with you that when advanced and highly specific features are discussed, in a large percentage of the scenarios the outcome is not a change in OpenAccess, but rather the definition of a workaround and a feature request on our feedback portal. 

The reason for that is in the prioritization of such features - when they do not involve a core functionality, and the needs of very few members of our community require it, the priority of features that would create benefits for hundreds of people is usually higher. Still, all requests are considered when we are planning the next release and we are trying to make the best out of each Q.

This is the purpose of the portal - to help us with additional input from you and the community when we have to implement for instance 3 out of 5 requested features. Here are only a few examples of features that were not originally part of our plans for OpenAccess ORM, but were implemented based on user votes:
- Database default values support
- Native 1:1 relationships in the designer
- Automated implementation of INotifyPropertyChanged/Changing
- Data Annotation attributes generation

As visible on the portal - currently we have several items in development, hoping to release them with Q3 2013. In other words, I would say that the feedback items are seriously considered for implementation. Of course, we wish we could release even more of these requests per Q, but no amount of efforts is limitless.

Please accept our apologies if we have disappointed you in any support situation with false promises, in case you would like to bring to our attention a certain case where you are unhappy with the outcomes, do not hesitate to get back to us with the reference. 
 

Regards,
Ivailo
Telerik

[Jiri]

Hello Ivailo,

thank you for your reply.

Its nice to have all these fantastic features but I will expect that features like INSERT x INTO y RETURNING id INTO z will work not only for number but also for other types such as characters (?threadid=535160) or guids (?threadid=620241). Or feature like support for synonyms(?threadid=693113) will be fully functional before all these super-features will be implemented or at least most of them ... Another chapter are stored functions (not implemented yet) and packages (two different packages which contains proc/fnc with same names are impossible for the designer to process, domain method from package naming without package prefix).

Maybe you will agree with me or maybe you won't but I don't see all these features as hard-core stuff which is used by very few users. And if it is then God help us :)

Thanks & Regards

Jiri

[Ivailo]

Hi Jiri,

I agree with the validity of those feature requests. Indeed, there is room for improvement when it comes to supporting Oracle-specific features in Telerik OpenAccess ORM. Usually we assign a higher value to cross-database features (as we support numerous back-ends) and for that purpose database specifics like the ones that you mentioned could sometimes seem neglected. We have recognized this trend and we are already investing efforts in this direction. For instance, two years ago we didn't have any support for auto-generated columns by a trigger in Oracle, now the number columns are supported. Even in our last official release, we have added support for global Oracle functions and procedures, defined outside packages, which was previously not possible.

Note that we already support Database Functions as well - we introduced that feature in 2012. Furthermore, the functions can be called in LINQ statements and executed on the server side in the SQL query for maximum performance and flexibility.

Regarding the feature requests:

1. I have opened this feature request for supporting character data types in Oracle. Note that it is important in this case to find out how frequently such types are chosen as primary keys. Also, in case this is applicable for your scenarios, have in mind that generating GUIDs can also happen within the application with OpenAccess ORM, using our client-side sequential GUIDs functionality. 

2. I have also created an item on synonyms support. 

3. Indeed, our checks have shown that while functions and procedures are currently recognized even within packages, the actual domain method definition is confused by the duplicated names due to the lack of information about the package name in our model. I will include this item in our list of issues and we will consider how we can add this information in the RLINQ. I will get back to you in case we find a valid workaround or we manage schedule the fix for the next release.

Thank you for the provided feedback.

Regards,
Ivailo
Telerik