Query of a GUID field doesn't work

jesuissur · Post by **jesuissur** » Thu 28 Jul 2011 19:07

Hi,

We have the latest version of dotConnect for Oracle (6.30.196 28-Jul-11) and are using it with EF 4.1 (and code configuration, not edmx).

We got an entity with a field of type GUID mapped to a VARCHAR2(32) column.
When the data is saved into the database, the format is upper case without any dash (ex.: A0D8F7E720DDBF4296C932F7A730A635).

We later try to query thie entity with a where clause on this GUID field but cannot get any result. Our where clause looks like this (VB.NET)

Code: Select all

Where(Function(x) x.GuidField = anotherGuid)

Both variable are GUID type.

Our model configuration of this field does not specified any database type because we want our configuration to be database agnostic.

We've tried RAW(16) and VARCHAR2(36) as column's type but got the same (no) result.

We've profiled the application with EfProf but the guid always appears as lower case with dash in both insert and select (where) clause. So we're not sure how to get help out of this.

Anyone has an idea about this problem. Is there any tips for us or workaround?

Any help is greatly appreciated
Thanks
Phil

Shalex · Post by **Shalex** » Mon 01 Aug 2011 16:23

Our current support of System.Guid in Entity Framework assumes that you are working with the RAW(16) column.

I have tried the following code with EF 4.1 and dotConnect for Oracle v 6.30.196. It works.

Code: Select all

        'CREATE TABLE GUIDTABLE (
        '  ID NUMBER(38),
        '  GUIDCOLUMN RAW(16),
        '  CONSTRAINT PK_GUIDTABLE PRIMARY KEY (ID));

        Dim guid As Guid = guid.NewGuid()

        Dim ent As New SCOTTEntities()
        Dim tbl As New GUIDTABLE
        tbl.ID = 1
        tbl.GUIDCOLUMN = guid
        ent.GUIDTABLEs.Add(tbl)
        ent.SaveChanges()

        Dim myt As New GUIDTABLE()
        myt = ent.GUIDTABLEs.Where(Function(x) x.GUIDCOLUMN = guid).SingleOrDefault()
        Console.WriteLine("ID of inserted record = {0}", myt.ID)

Be aware that there are two representations of GUID:
1) GUID itself;
2) representation of binary data of GUID (as it is stored in database).
Make sure that anotherGuid in your sample is representation of GUID itself.

jesuissur · Post by **jesuissur** » Mon 01 Aug 2011 17:10

Thanks for your answer.

Did you use the model configuration by code (or edmx file)?

Maybe it has a different behavior depends on the configuration we use.
Our code configuration looks like this

Code: Select all

config.Property(Function(x) x.GUIDCOLUMN).HasColumnName("GUIDCOLUMN ").IsRequired()

Would you mind to try the test of yours with code configuration if your previous tests were not.

Thanks again
Phil

jesuissur · Post by **jesuissur** » Mon 01 Aug 2011 17:42

Another quick question...

Is it possible to use a VARCHAR2 to store a Guid with EF and dotConnect for Oracle?
How the GUID is transformed in binary data? Oracle method or .NET framework?

Thanks
Phil

jesuissur · Post by **jesuissur** » Mon 01 Aug 2011 18:28

We finally did further testing and the cause of our problem is actually what you said about the binary form of the GUID.

When a GUID is stored in the database in a VARCHAR2 column, it is under the binary form. And when it is retrieved (or queried), it is still in the binary form. Hence, the retrieved GUID is not at all the same we saved into the database.

We'll then go back to our previous question and ask if it is possible to yuse a VARCHAR2 column to store a GUID with EF and dotConnect for Oracle?

Meanwhile, we will try again with a RAW(16) column and hope the result would be fine.

Thanks

Shalex · Post by **Shalex** » Tue 02 Aug 2011 13:06

jesuissur wrote:Is it possible to use a VARCHAR2 to store a Guid with EF and dotConnect for Oracle?

Our current support of System.Guid in Entity Framework assumes that you are working with the RAW(16) column (not VARCHAR2).

jesuissur wrote:How the GUID is transformed in binary data? Oracle method or .NET framework?

The GUID is transformed in binary data by .NET Framework.