Entity Framework: Insists on adding new entity in many-to-many instead of re-using existing FK

Question

I have got a many to many relationship, briefly
Cases -----< CaseSubjectRelationships >------ CaseSubjects

More fully: Cases(ID, CaseTypeID, .......)
CaseSubjects(ID, DisplayName, CRMSPIN)
CaseSubjectsRelationships(CaseID, SubjectID, PrimarySubject, RelationToCase, ...)

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)

An Entity Framework data model has been created - ASP.NET version 4.0

I have a WCF service with a method called CreateNewCase which accepts as its parameter a Case object (an entity created by the Entity Framework) - its job is to save the case into the database.

The WCF service is invoked by a third party tool. Here is the SOAP sent:

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
    <s:Body>
        <CreateNewCase xmlns="http://tempuri.org/">
            <c xmlns:a="http://schemas.datacontract.org/2004/07/CAMSModel">
                <a:CaseSubjectsRelationships>
                    <a:CaseSubjectsRelationship>
                        <a:CaseSubject>
                            <a:CRMSPIN>601</a:CRMSPIN>
                            <a:DisplayName>Fred Flintstone</a:DisplayName>
                        </a:CaseSubject>
                        <a:PrimarySubject>true</a:PrimarySubject>
                        <a:RelationToCase>Interested</a:RelationToCase>
                        <a:StartDate>2011-07-12T00:00:00</a:StartDate>
                    </a:CaseSubjectsRelationship>
                    <a:CaseSubjectsRelationship>
                        <a:CaseSubject>
                            <a:CRMSPIN>602</a:CRMSPIN>
                            <a:DisplayName>Barney Rubble</a:DisplayName>
                        </a:CaseSubject>
                        <a:RelationToCase>Observer</a:RelationToCase>
                        <a:StartDate>2011-07-12T00:00:00</a:StartDate>
                    </a:CaseSubjectsRelationship>
                </a:CaseSubjectsRelationships>
                <a:CaseType>
                    <a:Identifier>Change of Occupier</a:Identifier>
                </a:CaseType>
                <a:Description>Case description</a:Description>
                <a:Priority>5</a:Priority>
                <a:QueueIdentifier>Queue One</a:QueueIdentifier>
                <a:Title>Case title</a:Title>
            </c>
        </CreateNewCase>
    </s:Body>
</s:Envelope>

The WCF engine deserializes this into a Case entity for me correctly and when I look in the debugger everything is set up properly.

What I want to do, is only create a new CaseSubject if there is not already an entry in the database with that CRMSPIN specified (CRMSPIN is a reference number from a central customer database)

So, in the below example, I want to see if I already have an entry in CaseSubjects 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)

Here is the .NET code I have tried to do this.

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

As you can see, instead the For Each 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 csr.SubjectID = s.ID instead of setting the whole entity and also I have tried setting them both!).

However, even when putting a breakpoint on the ctx.SaveChanges() line and looking at how the subjects are set up and seeing in the debugger that it looks fine, it is always creating a new row in the CaseSubjects table.

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 .CaseType to the entity I found. When it saves, it works perfectly and as-expected and with no duplicated rows.

I'm just having trouble trying to apply the same theory to one side of a many-to-many relationship.

Here are some (hopefully relevant) extracts from the .edmx

<EntitySet Name="Cases" EntityType="CAMSModel.Store.Cases" store:Type="Tables" Schema="dbo" />
          <EntitySet Name="CaseSubjects" EntityType="CAMSModel.Store.CaseSubjects" store:Type="Tables" Schema="dbo" />
          <EntitySet Name="CaseSubjectsRelationships" EntityType="CAMSModel.Store.CaseSubjectsRelationships" store:Type="Tables" Schema="dbo" />



 <AssociationSet Name="FK_CaseSubjectsRelationships_Cases" Association="CAMSModel.Store.FK_CaseSubjectsRelationships_Cases">
            <End Role="Cases" EntitySet="Cases" />
            <End Role="CaseSubjectsRelationships" EntitySet="CaseSubjectsRelationships" />
          </AssociationSet>
          <AssociationSet Name="FK_CaseSubjectsRelationships_CaseSubjects" Association="CAMSModel.Store.FK_CaseSubjectsRelationships_CaseSubjects">
            <End Role="CaseSubjects" EntitySet="CaseSubjects" />
            <End Role="CaseSubjectsRelationships" EntitySet="CaseSubjectsRelationships" />
          </AssociationSet>

EDIT: The property setters for the CaseSubject property of the CaseSubjectsRelationships object:

/// <summary>
/// No Metadata Documentation available.
/// </summary>
<XmlIgnoreAttribute()>
<SoapIgnoreAttribute()>
<DataMemberAttribute()>
<EdmRelationshipNavigationPropertyAttribute("CAMSModel", "FK_CaseSubjectsRelationships_CaseSubjects", "CaseSubject")>
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

Answer 1

You didn't specify what context model are you working with, so I'll assume you're using the default (ie. you don't have some explicit .tt files to generate your entities).

So, basically, this is what I think is happening.
In your code, when you fetch something from context:

Dim ct = ctx.CaseTypes.SingleOrDefault(Function(x) x.Identifier.ToUpper = c.CaseType.Identifier.ToUpper)

this ct is in context. The method argument that you deserialized from service (the c) is not in context. You can regard the context as the "object tracking and fetching" entity, that makes sure that everything attached to it can know about any changes, if it's new, deleted etc.

So, when you get to the part:

 ' Set the case type based on that found in database: '
  c.CaseType = ct

at the moment you assign something that's attached to something not attached, the unattached object will get pulled into context as well - there can't be "partially" attached entities - if it's attached, everything it references has to be attached as well. So, this is the moment where the c gets "dragged" into the context (implicitly). When it enters the context, it will get marked as "new" since it doesn't know anything about it yet (it has no knowledge of it, no change tracking info...).

So, now that everything about that object c is in context, when you query the context for this:

 Dim s As CaseSubject = ctx.CaseSubjects.SingleOrDefault(Function(x) x.CRMSPIN = spin)

it will figure that indeed there is an object with that CRMSPIN and it's already attached - "hey, no need to go to database, I already have this!" (trying to be smart and avoid a db hit), and it will return your own object.

Finally, when you save everything, it will be saved, but your attached c and all of it's child objects that are marked as 'new' will be inserted instead of updated.

The easiest fix would be to first query everything you need from context, and only then start assigning it to properties of your object. Also, take a look at UpdateCurrentValues, it may also be helpful...

Answer 2

OK: So the resolution to this was a combination of what @veljkoz said in his answer (which was very useful to help me out to reach the final resolution, but on its own was not the full resolution)

By moving the For Each loop to the first thing done before anything else (As hinted by @veljkoz), that got rid of the Collection was modified, enumeration may not continue error I was getting when I set csr.CaseSubject = Nothing.

It also turned out to be important to not attach entities (e.g. not to set csr.CaseSubject to an entity but only to Nothing) but instead to use the .SubjectID property. A combination of all the above led me to the following code, which works perfectly and doesn't try to insert duplicate rows.

+1 to @veljkoz for the assist but also note that the resolution includes setting the entity reference to Nothing and using the ID property.

Full, working code:

 Public Function CreateNewCase(c As CAMSModel.Case) As String Implements ICamsService.CreateNewCase

    Using ctx As New CAMSEntities
        ' Subjects first, otherwise when you try to set csr.CaseSubject = Nothing you get an exception '
        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 = Nothing
                csr.SubjectID = s.ID
            End If
        Next

        ' 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

        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