GUID String Representation in SQL Developer and EF differ

GUID String Representation in SQL Developer and EF differ

Postby christian_bertram » Thu 13 Aug 2015 15:19

I'm using dotconnect to work with a oracle 11g database and so far i created the db with code first and i´m using GUIDs as identifiers

At the moment the guids are created with the 'default value ' feature and the use of the SYS_GUID() function of oracle.

After insert of new data the guids look like this:

GUID reptresentation when i select it with linq to ef:
'55D3C5756E9342939F5CCE44AC87AEE2'
GUID representation in SQL-Developer
'75c5d355-936e-9342-9f5c-ce44ac87aee2'

So the string representations differ. I read some articles and found out that .net guids ("Guid") and oracle guids(RAW) have a different structure.

So my conclusion is that i always have to use the .net GUID type to generate new guids and cant use SYS_GUID() because the oracle guid would always have the "wrong" value in .NET . IS that right?

Or did i something wrong at the code first part? I used the Guid datatype and it is mapped to oracle type "RAW" .

Thanks.
christian_bertram
 
Posts: 16
Joined: Mon 22 Jun 2015 08:38

Re: GUID String Representation in SQL Developer and EF differ

Postby Shalex » Thu 13 Aug 2015 17:30

These are two representations of the same GUID:
Code: Select all
    Guid guid = new Guid("75c5d355-936e-9342-9f5c-ce44ac87aee2");
    string stringGuid = guid.ToString();
    string binaryGuid = null;
    Byte[] bytes = guid.ToByteArray();
    foreach (var byt in bytes)
        binaryGuid += String.Format("{0:X2} ", byt);
    Console.WriteLine("String representation: " + stringGuid);
    Console.WriteLine("Binary representation: " + binaryGuid);
    Console.ReadKey();

The output would be:
String representation: 75c5d355-936e-9342-9f5c-ce44ac87aee2
Binary representation: 55 D3 C5 75 6E 93 42 93 9F 5C CE 44 AC 87 AE E2

If this doesn't help, please send us a small test project with the corresponding DDL/DML script to reproduce the issue in our environment.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: GUID String Representation in SQL Developer and EF differ

Postby christian_bertram » Mon 17 Aug 2015 09:08

Ok thanks.

Looks like i should always assign a new guid with Guid.NewGuid().ToByteArray(). instead of Guid.NewGuid()
christian_bertram
 
Posts: 16
Joined: Mon 22 Jun 2015 08:38

Re: GUID String Representation in SQL Developer and EF differ

Postby christian_bertram » Tue 06 Oct 2015 17:41

I still have problems with that guids in my web project:

The application gets the guid in this representation: '61AA7DD78C39614696358AD4E45366FA'

But in a linq where statement i need the Guid datatype so i convert it into a guid:
var guid = Guid.Parse(id);


So the guid representation is changed to:
61aa7dd7-8c39-6146-9635-8ad4e45366fa

and the linq statement converts the where part to:
Code: Select all
...WHERE "Extent1".FK = (CAST('61aa7dd7-8c39-6146-9635-8ad4e45366fa' AS RAW(16)))...


result: records with FK=61AA7DD78C39614696358AD4E45366FA are not found :(

So it is not possible to use dotConnect and entity framework with oracle guids ???
Last edited by christian_bertram on Wed 07 Oct 2015 08:43, edited 1 time in total.
christian_bertram
 
Posts: 16
Joined: Mon 22 Jun 2015 08:38

Re: GUID String Representation in SQL Developer and EF differ

Postby christian_bertram » Wed 07 Oct 2015 08:41

Ok so i made some more research and find a half satisfying solution.

When i get back a guid string vom the client i always have to change the byte order of this guid to use it in a linq expression:
Code: Select all
var guid = Guid.Parse(id).FlipEndian();


with the extension method i got from
http://stackoverflow.com/questions/8064648/net-native-guid-conversion?lq=1

Only that way the guid is parsed correctly inside the sql query created yb EF:
(CAST(:p__linq__0 AS RAW(16))

can somebody confirm this or give me an other solution?

i also cant understand why in other hand this gives the correct results:
Code: Select all
var newID = Guid.NewGuid();
newSubItem = new A();
newSubItem.ID = newID;
... save changes ...
var newQuery = from i in context.A where i.ID == newID select i;


It looks like the wrong parsing during a select (oracledb-->entity) is the actual problem but i cant
control that or can i ?
thanks.
christian_bertram
 
Posts: 16
Joined: Mon 22 Jun 2015 08:38

Re: GUID String Representation in SQL Developer and EF differ

Postby Shalex » Thu 08 Oct 2015 07:06

Please send us a small test project with the corresponding DDL/DML script so that we can reproduce the problem in our environment.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle