NHibernate dictionary mapping without primary key

Question

Question
How to map dictionary collection cross database with the same .hbm configuration ?

Scenario
I am trying to map a dictionary property:

Dictionary<string, string> Phrases { set; get; }

with the following .hbm configuration:

<map
    name="Phrases"
    cascade="save-update"
    table="ATTRIBUTE_LOCALE"
    lazy="true">
        <key column="RESOURCE_ID" /> <!-- foreign key -->
        <index column="LOCALE_NAME" type="string" />
        <element column="PHRASE" type="string" />
</map>

and the following is the table create SQL of [ATTRIBUTE_LOCALE] for MS SQL:

CREATE TABLE ATTRIBUTE_LOCALE ( 
    CUID int IDENTITY(1, 1) NOT NULL,
    RESOURCE_ID int NOT NULL,
    FIELD_NAME nvarchar(255) DEFAULT 'VALUE' NOT NULL,
    LOCALE_NAME nvarchar(255) NOT NULL,
    PHRASE ntext NULL
);

but if I change my database from MS SQL Server to Oracle and Oracle database cannot use IDENTITY(1, 1) to generate the primary key automatically. In Oracle, I will have some trouble due to insert table with NULL Primary Key.

How can I solve this problem?

David Osborne David Osborne · Accepted Answer · 2017-02-10T08:37:15

You could map the ATTRIBUTE_LOCALE table with a native generator rather than identity:

For cross-platform development, the native strategy will choose from the identity, sequence and hilo strategies, dependent upon the capabilities of the underlying database (ref).

Oracle doesn't support the notion of an identity column. If you're using SQL Server 2012+, you can use sequences rather than identities. This would be more consistent with Oracle and is, arguably, a better key generation strategy ORM-wise.

NHibernate dictionary mapping without primary key

2 Answers