GUID Endian flipping

[martinRocks]

I am working on an old C# project that has 100,000s of stored procedures doing CRUD operations and logic. I'm tasked with upgrading the project to a more modern pattern. I have decided to use Entity Framework and Dot.Net 6 for the new version of the application. However, the app is too big to do all at once, so I can only do parts at a time.

The problem is right now using the stored procedures, when I insert a GUID of 12345678-9012-3456-7890-123456789012, the database stored exactly that in my RAW(16) field. When I move to Oracle.EntityFrameworkCore to do my insert of 12345678-9012-3456-7890-123456789012, the database stored a flipped Endian version of the GUID, 78563412129056347890123456789012.

Doing research, I have found that this is because of the Guid.ToByteArray in System that is being used by Oracle.EntityFrameworkCore. It has a complier directive to say if/when to do Endian flipping. Since my DB and my application are on different systems, I seem to always be getting the wrong endian when combining CRUD operations using entity framework and stored procedures.

Is there a way we can add a configuration object to tell Oracle.EntityFrameworkCore not to do any flipping of Endians?

[alexkeh]

Here's some background on how ODP.NET handles GUIDs and how .NET Guid.ToByteArray() works so that everyone understands the issue better.

[martinRocks]

Thank you Alex again. You have been amazingly helpful getting me up to speed on exactly what the problem is and how there isn't a fix at this time since everything is doing what is expected.

[alexkeh]

@martinRocks Do you have a test case in which the byte flip is a problem? If so, can you share it?

In my experience, developers deal with GUIDs as unique identifiers. Their actual value doesn't really matter. In the .NET realm, that GUID ODP.NET uses remains consistent and unique. When the GUID moves to the Oracle DB realm, the bytes flip. However, the byte flip consistently occurs. Thus, when the GUID/RAW(16) is used in the Oracle DB realm, it is also consistent and unique.

The fundamental question I'm asking is how do you get the GUID from Oracle DB to .NET and perform some GUID usage operation without the byte flipping. That would be the only way for the two values not to match.

[martinRocks]

@alexkeh Here is sample project that produces different values in the table. I need to say, after looking deep into the old code, I think this is a code issue. However, the driver shouldn't allow me to do what I'm doing in the method called InsertStoredProc2.

All you should have to do to get my demo to run is ... 1) create a new DB user 2) grant CONNECT, RESOURCE, DBA, and CREATE TABLE to that user 3) change the connection string in my appsettings.json file 4) run the app

It should create a simple table and a stored procedure. Then it will do an insert of a GUID using EF, command text, and 2 stored procedure calls. 3 of the GUIDs will be the same value in the DB; a flipped Endian. The 4th GUID will be exactly the same as the original GUID.

My problem is the old application is doing things like InsertStoredProc2. What I want to do is things like InsertEf. However, since the application is so big, I can't change everything at once. Also, some GUIDs are shared with other systems. So the value of GUID is important because they are shared keys.

GuidDemo.zip

[martinRocks]

In my opinion, here is what should happen.

Method InsertCommandText, InsertStoredProc, and InsertEf data should look like InsertStoredProc2. Method InsertStoredProc2 should throw an error.

[martinRocks]

I think I came up with a workaround. What do you think?

In the DataContext.cs file from my example... add a method that will flip the Endian in the OnModelCreating method, add a conversion to the propery

private Guid FlipEndian(Guid guid)
{
    var newBytes = new byte[16];
    var oldBytes = guid.ToByteArray();
    for (var i = 8; i < 16; i++)
    {
        newBytes[i] = oldBytes[i];
    }

    newBytes[3] = oldBytes[0];
    newBytes[2] = oldBytes[1];
    newBytes[1] = oldBytes[2];
    newBytes[0] = oldBytes[3];
    newBytes[5] = oldBytes[4];
    newBytes[4] = oldBytes[5];
    newBytes[6] = oldBytes[7];
    newBytes[7] = oldBytes[6];
    return new Guid(newBytes);
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<TheExample>(entity =>
    {
        entity.Property(e => e.TheGuid)
            .HasConversion(to => FlipEndian(to), from => FlipEndian(from));
    });

    base.OnModelCreating(modelBuilder);
}

[alexkeh]

Yes, that could be a custom workaround. You could even open source the code so that all .NET developers could use this since it affects .NET generally.

I ran your submitted test case and observed the non-flipped endian behavior with the last stored procedure as you did. However, the usage is unconventional and is not something we would expect most developers to do. I see the .NET GUID converted to ODP.NET NVarchar2 then to an Oracle RAW in the DB. Is there a technical or business reason the .NET GUID is not being inserted back into the DB instead of the NVarchar2?

[martinRocks]

Alex, thanks for responding.

The best answer I can give you is that the old application devs didn't understand why the value in code wasn't exactly the same as the value in the DB. And they found converting it to a string would "work" making the 2 values the same. I'm just surprised that putting a string in a raw stored procedure field doesn't throw an error.

I tried sending an OracleDbType.Int32 to my stored procedure just to test and that throws an error. ORA-06550 PLS-00306: wrong number or types of arguments in call. That error makes sense to me.

[alexkeh]

Closing due to lack of interest. Oracle will reconsider if there is more customer interest in having this feature.

[olexandrvynnychenko]

Closing due to lack of interest. Oracle will reconsider if there is more customer interest in having this feature.

Hello @alexkeh, I am interested.

We started developing our application almost two years ago. .NET 5 (EF Core) + Oracle DB. So we have been using Oracle.EntityFrameworkCore 5.21.3.

Now I cannot simply switch to .NET 6 because Oracle.EntityFrameworkCore 6.x.x handles GUIDs differently. Apparently newer 5.x.x versions of Oracle.EntityFrameworkCore handle GUIDs in another way too.

Our GUIDs are not just internal DB stuff. Most primary keys are GUIDs. Entities are referred from outside by their GUIDs (API calls, resource URLs etc.).

It would be quite logical to introduce an option specify the way GUIDs are converted. Because those were breaking changes. I am not sure if it is even possible to handle this without huge effort on some hacking our data access and re-testing every little part of the application.

[martinRocks]

I'm very interested in this issue but I found a work around for my code; the OnModelCreating code above. Have you tried to make a variant of it?