Mapping entities for Sql Server Identity and Oracle Sequence with NHibernate

Question

I have a application that uses C# and NHibernate and it will support SQL Server 2008, SQL Server 2012 and Oracle. I have been using fluent nhibernate to map my entities and I have some questions about how should I map the ID. Oracle supports only the Sequence, SQL Server 2008 only identity and Sql Server 2012 both. I would like to map in Sql Server (2008 and 2012) with Identity and Oracle with Sequence on the same code.

How should I map the ID to work for all databases?

It does not matter if I will have some IF's statement on my fluent mapping code. Looks my codes for mapping:

For SQL:

Id(x => x.Id).GeneratedBy.Native();

For Oracle:

Id(x => x.Id).GeneratedBy.Sequence("SQ_Customer");

PS: I do not want any workaround to achieve it. I want a NHibernate/Fluent-NHibernate solution to map it.

Vitality

Do you have to wait for database to give you the id, or can you generate your own using nhibernates HILO generator? – Clapperclaw

I was think using a Guid but unfortunately I can not change my database :( – Vitality

Answer 1

Well, "Native" chooses Identity, sequence or HILO automatically depending on the used DB.

Using this xml mapping it should use identity or the sequence sq_customer if its Oracle:

  <generator class="native" >
    <param name="sequence">sq_customer</param>
  </generator>

This post describes things further.

Flyer

It's the correct answer. Translated to Fluent: Id(x => x.Id).GeneratedBy.Native("SQ_Customer"); – Zinn

Id(x => x.Id).GeneratedBy.Increment() works without needing a sequence. – Contagious

I was searching 2 days for solution in Java. No answers like that for Java. No info in doc. This "trick" helped me very much, worked perfectly for MS SQL Identity and Oracle SEQUENCE-TRIGGER. Thank you very much. – Ontology

great I was of assistance, vote up the answer if you don't mind :) – Flyer