title: Setting Foreign keys in Linq to SQL
body: <p>It's well known that you cannot set foreign key IDs directly in Linq to SQL if the entities have already been loaded.  You can however look up the entity by it's foreign key and then set the entity as the foreign entity using the entity relationship.  (I've taken out the enum here and used integer values for simplicity). i.e. If I have a loaded Appointment entity and an associated AppoinmentStatus Entity I can't do this:-</p>
  
  <pre><code>ExistingAppointment.AppointmentStatusID = 7
  </code></pre>
  
  <p>But I can do this:-</p>
  
  <pre><code>ExistingAppointment.AppointmentStatus = (From appstat In db.AppointmentStatus _
                                          Where appstat.StatusID = 7 _
                                          Select appstat).Single
  </code></pre>
  
  <p>I have this kind of thing littering my code and I'd like to refactor.  So...</p>
  
  <p>I could obviously use a helper method in a module like this:-</p>
  
  <pre><code>
  Module Helper
      Public Shared Function GetAppointmentStatus(ByVal AppStatusID As Integer) As AppointmentStatus
          GetAppointmentStatus = (From appstat In db.AppointmentStatus _
                                         Where appstat.AppointmentStatusID = AppStatus _
                                         Select appstat).Single
      End Function
  End Module
  </code></pre>
  
  <p>I could even make this into an extension method, like this.</p>
  
  <pre><code>
  Imports System.Runtime.CompilerServices
  Module Helper
  Extension()> _
      Public Shared Function GetAppointmentStatus(ByVal db as DataClassesDataContext, ByVal AppStatusID As Integer) As AppointmentStatus
          GetAppointmentStatus = (From appstat In db.AppointmentStatus _
                                         Where appstat.AppointmentStatusID = AppStatusID _
                                         Select appstat).Single
      End Function
  End Module
  </code></pre>
  
  <p>I could also put this in the Linq to SQL partial class, like this.</p>
  
  <pre><code>
  Partial Public Class DataClassesDataContext    
      Public Function GetAppointmentStatus(ByVal AppStatusID As Integer) As AppointmentStatus
          GetAppointmentStatus = (From appstat In Me.AppointmentStatus _
                                         Where appstat.AppointmentStatusID = AppStatusID _
                                         Select appstat).Single
      End Function
  End Class
  </code></pre>
  
  <p>Further I could put the code in the Linq to SQL Appointment Entity partial class like this:-</p>
  
  <pre><code>
  Partial Public Class Appointment    
      Public Function GetAppointmentStatus(ByVal db as DataClassesDataContext, ByVal AppStatusID As Integer) As AppointmentStatus
              GetAppointmentStatus = (From appstat In db.AppointmentStatus _
                                         Where appstat.AppointmentStatusID = AppStatusID _
                                         Select appstat).Single
      End Function
  End Class
  </code></pre>
  
  <p>Which should I do and why, or is there a better alternative?</p>