F# SQL Type Provider Identity Insert

Question

I'm currently using an F# sql type provider to migrate some data between one database to another.

I need to be able to insert identity information (numeric ID column).

The schema for the table has the Id column as being identity.

I've run a script to set identity_insert to ON so that should all be fine.

The issue is that the type-provider-generated insert doesn't actually bother writing the ID value as part of the statement.

Is there a way of forcing it to do so?

Answer 1

Here's what I did to meet a deadline. It's nowhere near ideal but does insert the record with the 'correct' identity.

mySequence |> Seq.iter (fun x -> 
  db.DataContext.ExecuteCommand(
    "SET IDENTITY_INSERT [dbo].mytable ON 
     insert into mytable (id, name) values({0}, {1}) 
     SET IDENTITY_INSERT [dbo].mytable OFF", x.ID, x.Name) |> ignore)

Edit

Turns out DataContext.ExecuteCommand() will fail if one of the parameters is null because it can't infer the type and assumes Object

I ended up switching to Seekwell (nuget package I wrote) which actually looks a bit cleaner too, given you don't have to deal with parameter ordering.

let command = new Seekwell.Command(destination)
mySequence |> Seq.iter (fun x -> 
  command.ExecuteScalar(
    "SET IDENTITY_INSERT mydb.dbo.mytable ON 
     insert into mydb.dbo.mytable (id, name) values(@ID, @Name) 
     SET IDENTITY_INSERT mydb.dbo.mytable OFF", x) |> ignore)