title: Dapper insert Foreign Key Id with where clause
body: <p>I have a couple models: User and ShiftWork:</p>
  
  <pre><code>public class User
  {
      public long Id { get; set; }
      public string Username { get; set; }
      public string SSO { get; set; }
      public ShiftWork ShiftWork { get; set; }
  }
  
  public class ShiftWork
  {
      public long Id { get; set; }
      public string ShiftName { get; set; }
  }
  </code></pre>
  
  <p>I also have classes for selecting and inserting data. I have been able to find solutions for everything I have wanted to do so far, but I cannot seem to do the insert into User that I am trying to do.</p>
  
  <p>I want to be able to insert <code>ShiftWorkId</code> into the User table, but I need to get the Id from the <code>ShiftName</code> that is in a Winform text box.</p>
  
  <p>So far my efforts have basically looked like this:</p>
  
  <pre><code>public void CreateUser(User user)
  {
      using (IDbConnection cnn = SimpleDBConnection())
      {
          string sql = "INSERT INTO USER (Username, SSO, ShiftWorkId) Values (@Username, @SSO, (select @Id from ShiftWork Where ShiftName in (select ShiftName from ShiftWork)))";
          cnn.Execute(sql, new { user.Username, user.SSO, user.ShiftWork.Id });
      }
  }
  </code></pre>
  
  <p>I have also tried:</p>
  
  <pre><code>string sql = "INSERT INTO USER (Username, SSO, ShiftWorkId) Values (@Username, @SSO, (select @Id from ShiftWork Where ShiftName  = @ShiftName))";
  </code></pre>
  
  <p>However, I can't seem to figure out how I should add the parameters for ShiftName.</p>
  
  <p>The textbox  has a custom autocomplete source that is populated from the database:</p>
  
  <pre><code>public List&lt;ShiftWork&gt; GetShifts()
  {
      using (IDbConnection cnn = SimpleDBConnection())
      {
          string sql = "SELECT * FROM ShiftWork";
          var output = cnn.Query&lt;ShiftWork&gt;(sql);
          return output.ToList();
      }
  }
  </code></pre>
  
  <p>Which in the code behind looks like:</p>
  
  <pre><code>AutoCompleteStringCollection ac = new AutoCompleteStringCollection();
  foreach (var s in _shiftWorkRepository.GetShifts())
  {
      ac.Add(s.ShiftName);
  }
  
  tbShift.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
  tbShift.AutoCompleteSource = AutoCompleteSource.CustomSource;
  tbShift.AutoCompleteCustomSource = ac;
  </code></pre>
  
  <p>So far, everything I have tried regarding assigning the text from the text box to the user.shiftworkId is giving me errors.
  All help is greatly appreciated</p>