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

Did you also try `csr.SubjectID = s.ID` **and** `csr.CaseSubject = Nothing`? I should not make a difference to your code in my understanding but it's worth to test if still new CaseSubjects are created - which should be impossible. — Slauma, Jul 13 '11 at 18:07
A useful method to track the problem further down is `ctx.ObjectStateManager.GetObjectStateEntries(entityState)` where `entityState` can be `EntityState.Added`, `.Unchanged`, `.Modified`, etc. or an OR combination of those flags. You could check what you find after the foreach loop (there should be only your CaseType and the CaseSubjects you have found in the DB in `Unchanged` state) and after `AddToCases` (the Case, the CaseSubjectsRelationships and the CaseSubjects you have NOT found should be in `Added` state, the other objects should still be in state `Unchanged`). — Slauma, Jul 13 '11 at 18:47
Hi @Slauma, Regarding your first comment, Yes I did try that. It complained that the enumeration had been changed and could not continue. That surprised me because I wasn't changing the actual enumeration itself (I was enumerating CaseSubjectRelationships and changing the .CaseSubject property of it) - But that was the error nonetheless. — bgs264, Jul 13 '11 at 19:57
Hm, I believe that this can only mean that the setter for the `CaseSubject` property of your `CaseSubjectsRelationship` class does something more than just assigning the reference. Do you have fixup methods in your entity classes? And are you using lazy loading? Also what about the setter of `CaseType` in your `Case` class? — Slauma, Jul 13 '11 at 20:32
Hi @Slauma. Thank you for your help. I looked at `ctx.ObjectStateManager.GetObjectStateEntries` like you suggested after `AddToCases` and just before `SaveChanges()`. and what is interesting is that there are two subjects in the `Added` state and two subjects in the `Unchanged` state - Both Fred Flintstone and Barney Rubble are there twice, once in each state. But looking in debug, there are only two `CaseSubjectsRelationships` and each has one subject. So not sure how there are two at `added` and two at `Unchanged` ? — bgs264, Jul 14 '11 at 08:22
Did you check the property setters as described in my last comment? I believe there are some side effects going on. If the property setters would do nothing more than assigning the values your code is perfectly valid in my opinion and should work. — Slauma, Jul 14 '11 at 10:09
@Slauma I have edited my original question and included the property setters at the bottom of the post so you can see. They were just generated by the EF, I haven't changed the default generated code. Regarding fixup methods and lazy loading, I'm sorry I'm still new to EF. I haven't added these things, it's whatever the default would be when you generate the EDMX model based on the database. — bgs264, Jul 14 '11 at 12:24
I have temporarily worked around this by creating a brand new Case object, populating it based on the case object which is passed in via the service, then adding this brand new object via `AddToCases` and it's worked - it's not great but at least it's something that works... — bgs264, Jul 14 '11 at 12:26

score 3 · Answer 1 · edited May 23 '17 at 09:59

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...

Thanks - Your assist has helped me to get it to work. I would never have thought about what you said, so thanks, I have learned something more about Entity Framework! :) — bgs264, Sep 02 '11 at 14:44

score 1 · Accepted Answer · answered Sep 02 '11 at 14:43

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

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

2 Answers2