Migrate from NHibernate ids (hilo) to SQL auto ids in SQL Server

user_8291353 0
Viewed 151

Is there a way to migrate existing database with all tables and relations to use SQL Server auto ids instead of Nhibernate (hilo) ids?

We have a .NET application which uses NHibernate. But the problem is, we are running out of int.

I know that this requires tables recreation with new ones which have ids set as auto incremented. Is there a easy way to migrate. For example some sort of query which will replicate tables, keep relations, but now with SQL Server ids instead of hilo ids. Biggest problem of hilo, it's using shared ids, which makes situation worse.

For example, we have a database of 3 tables:

  • dbo.Users
  • dbo.RegistrationResults
  • dbo.UserNotes

Tables:

dbo.Users

  • Id int (Primary)
  • Email nvarchar(255)
  • RegistrationResultFk int (Foreign Key)

dbo.RegistrationResults

  • Id int (Primary)
  • ValidationOutcome nvarchar(255)

dbo.UserNotes

  • Id int (Primary)
  • Message nvarchar(255)
  • RegistrationResultFk int (Foreign Key)

And data populated like this:

dbo.Users

Id Email RegistrationResultFk
1 test@gmail.com 2
4 test2@gmail.com 5

dbo.RegistrationResults

Id ValidationOutcome
2 Awaiting confirmation
5 Confirmed

dbo.UserNotes

Id Message RegistrationResultFk
3 it's a test 2
6 it's a test 2 5

We want data after migration to look like:

dbo.Users

Id Email RegistrationResultFk
1 test@gmail.com 1
2 test2@gmail.com 2

dbo.RegistrationResults

Id ValidationOutcome
1 Awaiting confirmation
2 Confirmed

dbo.UserNotes

Id Message RegistrationResultFk
1 it's a test 1
2 it's a test 2 2
nhibernate sql-server .net c#
1 Answers

I suggest you, to minimize impact, use Sequences that are equivalent of autoincrement fields but are stored outside the table.

Below simple example for a table

Create the sequence

This code creates a sequence.
With SSMS you can also navigate to Database -> Programmability -> Sequence ==> right-click New Sequence.

Full Syntax.

CREATE SEQUENCE dbo.UserId 
    AS INT
    START WITH 1234 
    INCREMENT BY 1   
    ;

Read the syntax carefully to better set up your sequences, for example, you may want to set up the CACHE to increases performance minimizing the IOs required to generate sequence numbers

Get ID

For getting the ID you must issue a Raw Query with NHibernate.
NHibarnate Reference
NEXT VALUE

public int GetNextUserId(Session session)
{
    var query = session.CreateSQLQuery("SELECT NEXT VALUE FOR dbo.UserId");
    var result = query.UniqueResult();
    return Convert.ToInt32(result);
}
user_18911330
Related