title: Using LINQ to insert data into a table that uses a sequence as primary key generator
body: <p>I have a table which generates its primary key from a sequence (that just counts up from 0):</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE [dbo].[testTable](
      [id] [int] NOT NULL,
      [a] [int] NOT NULL,
   CONSTRAINT [PK_testTable] PRIMARY KEY CLUSTERED ([id] ASC))
  
  ALTER TABLE [dbo].[tblTestTable] ADD  CONSTRAINT [DF_tblTestTable_id]  DEFAULT (NEXT VALUE FOR [seq_PK_tblTestTable]) FOR [id]
  </code></pre>
  
  <p>I've used Visual Studio's O/R Designer to create the mapping files for the table; the <code>id</code> field is defined as:</p>
  
  <pre class="lang-cs prettyprint-override"><code>[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_id", DbType="Int NOT NULL", IsPrimaryKey=true)]
  public int id {…}
  </code></pre>
  
  <p>and now I'm trying to insert data via LINQ.</p>
  
  <pre class="lang-cs prettyprint-override"><code>var testTableRecord = new testTable()
  {
      a = 1,
  };
  db.Connection.Open();
  db.testTables.InsertOnSubmit(testTableRecord);
  db.SubmitChanges();
  Console.WriteLine($"id: {testTableRecord.id}");
  </code></pre>
  
  <p>The problem I'm encountering is, that LINQ seems unable to handle the id generation via sequence as it sets the <code>id</code> implicitly to 0 when inserting.</p>
  
  <ul>
  <li>When I set the <code>id</code> to <code>CanBeNull</code>, the insert fails because it tries to insert NULL into a non-nullable field.</li>
  <li>When I set the <code>id</code> to <code>IsDbGenerated</code>, the insert works but it expects an <code>IDENTITY</code> field and tries to load the generated id with <code>SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]',N'@p0 int',@p0=1</code> and than sets the <code>id</code> in the object to null because <code>SCOPE_IDENTITY()</code> returns <code>null</code>…</li>
  <li>I've been thinking about just using <code>IsDbGenerated</code>, destroying the LINQ object and querying the DB for the <code>id</code>, but I don't have anything unique to search for.</li>
  </ul>
  
  <p>Unfortunately changing the id creation mechanism to IDENTITY is not an option.</p>
  
  <p>Do I have to <a href="https://stackoverflow.com/questions/27077461/how-to-get-next-value-of-sql-server-sequence-in-entity-framework">explicitly query the DB for the next sequence value</a> and set the id manually?</p>
  
  <p>Whats the best way to handle these inserts via LINQ?</p>
  
  <p>PS: I need the id because I have to insert more data that uses the id as FK.</p>