OrmLite - GUIDs as primary keys in Oracle

user_805407 0
Viewed 222

I'm using OrmLite with both SqlServer, Oracle and PostgreSQL dialects.

I want to use GUIDs as primary keys and have a simple object, using the AutoId attribute:

public class MyObject
{
    [AutoId]
    public Guid Id { get; set; }
    [Required]
    public string Name { get; set; }
    ...

All goes well with SqlServer and PostgreSQL dialetcs, but with Oracle I get an initial GUID with all zeros in the db, and a subsequent INSERT violates the unique key constraint of my primary key. How can this be accomplished db agnostic so it also works with Oracle?

ormlite-servicestack
1 Answers

Based on the source code I'm looking at, it doesn't appear to properly generate GUIDs for anything that's not SQL Server or PostgreSQL, regardless of what the documentation actually says on the README. Relevant code links below:

The best alternative I can provide here is to override the OracleOrmLiteDialectProvider. Specifically, I would override the GetAutoIdDefaultValue method to return "SYS_GUID()" if the field type is a GUID. Sample code below...

public OracleNewGuidOrmLiteDialectProvider : OracleOrmLiteDialectProvider
{
    public static OracleNewGuidOrmLiteDialectProvider Instance = new OracleNewGuidOrmLiteDialectProvider();
    public string AutoIdGuidFunction { get; set; } = "SYS_GUID()";

    public override string GetAutoIdDefaultValue(FieldDefinition fieldDef)
    {
        return fieldDef.FieldType == typeof(Guid)
            ? AutoIdGuidFunction
            : null;
    }   
}

To match the rest of the provider implementations, I would recommend creating a OracleNewGuidDialect class, like below...

public static class OracleNewGuidDialect
{
    public static IOrmLiteDialectProvider Provider => OracleNewGuidOrmLiteDialectProvider.Instance;
}

Then you would set the provider when you instantiate your OrmLiteConnectionFactory to OracleNewGuidOrmLiteDialectProvider.Instance, similar to below...

var dbFactory = new OrmLiteConnectionFactory(oracleConnectionString, OracleNewGuidDialect.Provider);

This isn't the best solution, but the pluggable nature of ServiceStack ORMLite allows you to control everything to the extent that you need. Hope this helps. Also, quick caveat--I didn't fully bake this solution, so you may need to tweak it, but based on the other implementations of the providers, it seems straightforward.

user_14647810
Related