Is it possible to do identity insert/update in SubSonic 2.0

Question

Unfortunately I'm stuck using this basically undocumented/obsolete library (SubSonic 2.0). I'm trying to do a simple identity insert but I can't seem to get it to work.

Here's the psuedo-code version of what I'm trying to do:

SubSonicExampleObject o = new Select()
                          .From(SubSonicExampleObject.Schema)
                          .Where(SubSonicExampleObject.IdColumn)
                          .IsEqualTo(124124)
                          .ExecuteSingle<SubSonicExampleObject>();

if(o == null)
{
    o = new SubSonicExampleObject();
    o.Id = 124124;
    o.Save();
}

I've also tried adding this code above and below this statement, to no success:

new InlineQuery()
    .Execute("SET IDENTITY_INSERT " + SubSonicExampleObject.Schema + " ON");

    [CODE ABOVE]

new InlineQuery()
    .Execute("SET IDENTITY_INSERT " + SubSonicExampleObject.Schema + " OFF");

Regardless of what I do, SubSonic seems to be ignoring the ID value, and setting it based on the next identity value.

If anyone knows how to do this, I'd be super duper happy!

Why do you need to insert that specific ID when using an identity column? Could you not write a SQL script in insert this (missing but expected?) record if this is an edge case and then let it use the next identity value for subsequent inserts? — BateTech, Feb 01 '14 at 01:56
Hey BateTech, in truth this is for an import application, and there are hundreds of objects inserted this way in a loop. I ended up doing a large InlineQuery, which includes both the insert statement and toggling the IDENTITY_INSERT (for some reason, running it as three separate inline queries similar to my second example, doesn't work?). I might post that as the answer if I can't find a cleaner solution :/ — Paul Zaczkowski, Feb 01 '14 at 19:06

Is it possible to do identity insert/update in SubSonic 2.0

0 Answers0