title: Entity Framework: Insists on adding new entity in many-to-many instead of re-using existing FK
body: <p>I have got a many to many relationship, briefly<br>
  <code>Cases -----&lt; CaseSubjectRelationships &gt;------ CaseSubjects</code></p>
  
  <p>More fully:
  Cases(<strong>ID</strong>, CaseTypeID, .......)<br>
  CaseSubjects(<strong>ID</strong>, DisplayName, CRMSPIN)<br>
  CaseSubjectsRelationships(<strong>CaseID</strong>, <strong>SubjectID</strong>, PrimarySubject, RelationToCase, ...)  </p>
  
  <p>In my many-to-many link table are additional properties relating to the subject's association with the specific case - such as, start date, end date, free-text relationship to case (observer, creator, etc)</p>
  
  <p>An Entity Framework data model has been created - ASP.NET version 4.0</p>
  
  <p>I have a WCF service with a method called <code>CreateNewCase</code> which accepts as its parameter a <code>Case</code> object (an entity created by the Entity Framework) - its job is to save the case into the database.</p>
  
  <p>The WCF service is invoked by a third party tool. Here is the SOAP sent:</p>
  
  <pre><code>&lt;s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"&gt;
      &lt;s:Body&gt;
          &lt;CreateNewCase xmlns="http://tempuri.org/"&gt;
              &lt;c xmlns:a="http://schemas.datacontract.org/2004/07/CAMSModel"&gt;
                  &lt;a:CaseSubjectsRelationships&gt;
                      &lt;a:CaseSubjectsRelationship&gt;
                          &lt;a:CaseSubject&gt;
                              &lt;a:CRMSPIN&gt;601&lt;/a:CRMSPIN&gt;
                              &lt;a:DisplayName&gt;Fred Flintstone&lt;/a:DisplayName&gt;
                          &lt;/a:CaseSubject&gt;
                          &lt;a:PrimarySubject&gt;true&lt;/a:PrimarySubject&gt;
                          &lt;a:RelationToCase&gt;Interested&lt;/a:RelationToCase&gt;
                          &lt;a:StartDate&gt;2011-07-12T00:00:00&lt;/a:StartDate&gt;
                      &lt;/a:CaseSubjectsRelationship&gt;
                      &lt;a:CaseSubjectsRelationship&gt;
                          &lt;a:CaseSubject&gt;
                              &lt;a:CRMSPIN&gt;602&lt;/a:CRMSPIN&gt;
                              &lt;a:DisplayName&gt;Barney Rubble&lt;/a:DisplayName&gt;
                          &lt;/a:CaseSubject&gt;
                          &lt;a:RelationToCase&gt;Observer&lt;/a:RelationToCase&gt;
                          &lt;a:StartDate&gt;2011-07-12T00:00:00&lt;/a:StartDate&gt;
                      &lt;/a:CaseSubjectsRelationship&gt;
                  &lt;/a:CaseSubjectsRelationships&gt;
                  &lt;a:CaseType&gt;
                      &lt;a:Identifier&gt;Change of Occupier&lt;/a:Identifier&gt;
                  &lt;/a:CaseType&gt;
                  &lt;a:Description&gt;Case description&lt;/a:Description&gt;
                  &lt;a:Priority&gt;5&lt;/a:Priority&gt;
                  &lt;a:QueueIdentifier&gt;Queue One&lt;/a:QueueIdentifier&gt;
                  &lt;a:Title&gt;Case title&lt;/a:Title&gt;
              &lt;/c&gt;
          &lt;/CreateNewCase&gt;
      &lt;/s:Body&gt;
  &lt;/s:Envelope&gt;
  </code></pre>
  
  <p>The WCF engine deserializes this into a Case entity for me correctly and when I look in the debugger everything is set up properly.</p>
  
  <p>What I want to do, is only create a new <code>CaseSubject</code> if there is not already an entry in the database with that <code>CRMSPIN</code> specified (CRMSPIN is a reference number from a central customer database)</p>
  
  <p>So, in the below example, I want to see if I already have an entry in <code>CaseSubjects</code> for somebody with CRMSPIN 601 and if I do, I don't want to create another (duplicate) entry but instead make the new case link to the existing subject (although a new row will need, obviously, need creating in  CaseSubjectsRelationships with the specific 'additional' information such as relationship etc)</p>
  
  <p>Here is the .NET code I have tried to do this.</p>
  
  <pre><code>Public Class CamsService
      Implements ICamsService
  
      Public Function CreateNewCase(c As CAMSModel.Case) As String Implements ICamsService.CreateNewCase
  
          Using ctx As New CAMSEntities
              ' Find the case type '
              Dim ct = ctx.CaseTypes.SingleOrDefault(Function(x) x.Identifier.ToUpper = c.CaseType.Identifier.ToUpper)
  
              ' Give an error if no such case type '
              If ct Is Nothing Then
                  Throw New CaseTypeInvalidException(String.Format("The case type {0} is not valid.", c.CaseType.Identifier.ToString))
              End If
  
              ' Set the case type based on that found in database: '
              c.CaseType = ct
  
              For Each csr In c.CaseSubjectsRelationships
                  Dim spin As String = csr.CaseSubject.CRMSPIN
                  Dim s As CaseSubject = ctx.CaseSubjects.SingleOrDefault(Function(x) x.CRMSPIN = spin)
  
                  If Not s Is Nothing Then
                      ' The subject has been found based on CRMSPIN so set the subject in the relationship '
                      csr.CaseSubject = s
                  End If
              Next
  
              c.CreationChannel = "Web service"
              c.CreationDate = Now.Date
  
              ' Save it '
              ctx.AddToCases(c)
              ctx.SaveChanges()
          End Using
  
          ' Return the case reference '
          Return c.ID.ToString
      End Function
  End Class
  </code></pre>
  
  <p>As you can see, instead the <code>For Each</code> loop, I try to get a subject based on the CRMSPIN and if I get something, then I update the "CaseSubject" entity. (I have also tried <code>csr.SubjectID = s.ID</code> instead of setting the whole entity and also I have tried setting them both!).</p>
  
  <p>However, even when putting a breakpoint on the <code>ctx.SaveChanges()</code> line and looking at how the subjects are set up and seeing in the debugger that it looks fine, it is <em>always</em> creating a new row in the CaseSubjects table.</p>
  
  <p>I can see in principle this should work - you'll see I've done exactly the same thing for Case Type - I have picked the identifier sent in the XML, found the entity with that identifier via the context, then changed the case's <code>.CaseType</code> to the entity I found. When it saves, it works perfectly and as-expected and with no duplicated rows.</p>
  
  <p>I'm just having trouble trying to apply the same theory to one side of a many-to-many relationship.</p>
  
  <p>Here are some (hopefully relevant) extracts from the .edmx</p>
  
  <pre><code>&lt;EntitySet Name="Cases" EntityType="CAMSModel.Store.Cases" store:Type="Tables" Schema="dbo" /&gt;
            &lt;EntitySet Name="CaseSubjects" EntityType="CAMSModel.Store.CaseSubjects" store:Type="Tables" Schema="dbo" /&gt;
            &lt;EntitySet Name="CaseSubjectsRelationships" EntityType="CAMSModel.Store.CaseSubjectsRelationships" store:Type="Tables" Schema="dbo" /&gt;
  
  
  
   &lt;AssociationSet Name="FK_CaseSubjectsRelationships_Cases" Association="CAMSModel.Store.FK_CaseSubjectsRelationships_Cases"&gt;
              &lt;End Role="Cases" EntitySet="Cases" /&gt;
              &lt;End Role="CaseSubjectsRelationships" EntitySet="CaseSubjectsRelationships" /&gt;
            &lt;/AssociationSet&gt;
            &lt;AssociationSet Name="FK_CaseSubjectsRelationships_CaseSubjects" Association="CAMSModel.Store.FK_CaseSubjectsRelationships_CaseSubjects"&gt;
              &lt;End Role="CaseSubjects" EntitySet="CaseSubjects" /&gt;
              &lt;End Role="CaseSubjectsRelationships" EntitySet="CaseSubjectsRelationships" /&gt;
            &lt;/AssociationSet&gt;
  </code></pre>
  
  <p>EDIT: The property setters for the <code>CaseSubject</code> property of the <code>CaseSubjectsRelationships</code> object:</p>
  
  <pre><code>/// &lt;summary&gt;
  /// No Metadata Documentation available.
  /// &lt;/summary&gt;
  &lt;XmlIgnoreAttribute()&gt;
  &lt;SoapIgnoreAttribute()&gt;
  &lt;DataMemberAttribute()&gt;
  &lt;EdmRelationshipNavigationPropertyAttribute("CAMSModel", "FK_CaseSubjectsRelationships_CaseSubjects", "CaseSubject")&gt;
  Public Property CaseSubject() As CaseSubject
      Get
          Return CType(Me, IEntityWithRelationships).RelationshipManager.GetRelatedReference(Of CaseSubject)("CAMSModel.FK_CaseSubjectsRelationships_CaseSubjects", "CaseSubject").Value
      End Get
      Set
          CType(Me, IEntityWithRelationships).RelationshipManager.GetRelatedReference(Of CaseSubject)("CAMSModel.FK_CaseSubjectsRelationships_CaseSubjects", "CaseSubject").Value = value
      End Set
  End Property
  </code></pre>