NHibernate -Temporarily turn off identity

Question

I'm running an NHIbernate solution using SQL CE. I am mapping one the fields in a table as below. However, in order to run some data imports I need to be able to temporarily turn off identity so I can import the data with its existing keys, then turn identity back on once the import has finished.

I've tried running a SQL query directly from the solution like this:

session.CreateSQLQuery(@"SET IDENTITY_INSERT [Article] ON");

but this seems to have no effect.

Is there any way to temporarily turn this on and off?

Property(x => x.ArticleId, m =>
{
    m.NotNullable(true);
    m.UniqueKey("UQ_Article_ArticleId");
    m.Column(cm => cm.SqlType("INT IDENTITY"));
    m.Generated(PropertyGeneration.Insert);
    m.Insert(true);
    m.Update(false);
});

score 0 · Answer 1 · answered Oct 10 '12 at 12:11

0

SQL should be a way to go, but I believe you have reversed the meaning of IDENTITY_INSERT.

To allow insert to identity column, you need to turn it to ON.

session.CreateSQLQuery(@"SET IDENTITY_INSERT [Article] ON");

And after you have done your bulk inserted, turn it back to OFF.

session.CreateSQLQuery(@"SET IDENTITY_INSERT [Article] OFF");

More details on MSDN.

answered Oct 10 '12 at 12:11

Miroslav Popovic

12,100
2
35
47

Yes, I've tried that but doesn't work. My example above was just showing how I'm executing the SQL. In practice I've been doing the way you mentioned. "On" first then "Off" afterwards. – user874615 Oct 10 '12 at 12:32

score 0 · Accepted Answer · answered Oct 11 '12 at 17:31

It was something fairly simple in the end.

The sql line should have been this:

session.CreateSQLQuery(@"SET IDENTITY_INSERT [Article] ON").ExecuteUpdate();

and it needed to be inside a transaction. However, you can only do this on one table per transaction so didn't really help me that much.

NHibernate -Temporarily turn off identity

2 Answers2