locked
Inserting a many-to-many relationship RRS feed

  • Question

  • Hi,

    I'm having trouble inserting a many-to-many relationship into my SQL Server 2008 Express database through EF4.
    The database model is quite straightforward containing the tables:

    Employee
    -- EmployeeID [GUID] [PK]
    -- ...


    Task
    -- TaskID [GUID] [PK]
    -- ...


    A junction table:

    EmployeeTaskJuntion
    -- EmployeeID [GUID] [PK]
    -- TaskID [GUID] [PK]
    {no other fields}


    Using VS2010 RC, I created an EDMX file and the entities seems fine (the junction table is left out):

    ||Employee|| * ----- * ||Task||


    Also, I created Self-Tracking Entities and your basic Client - WCF Service solution.
    I use the following code to create a new Employee with an existing Task

    Using SC = New CompanyServiceReference.CompanyServiceClient
    
        Dim TaskID As New Guid("38178c84-08e2-417f-b8b8-e6f92efc24a9")
    
        Dim ExistingTask As Task = SC.GetTask(TaskID)
    
        If Not IsNothing(ExistingTask) Then
    
            NewEmployee.Tasks.Add(ExistingTask)
    
        End If                    
    
        SC.InsertEmployee(NewEmployee)
    
    End Using
    


    And the InsertEmployee method at the server:


        Public Sub InsertEmployee(ByVal NewEmployee As STECompanyTypes.Employee) Implements ICompanyService.InsertEmployee
    
            Try
    
                Using CompanyEnts = New CompanyEntities
    
                    CompanyEnts.Employees.AddObject(NewEmployee)
    
                    CompanyEnts.SaveChanges()
    
                End Using
    
            Catch ex As Exception
    
    
    
            End Try
    
        End Sub
    
    


    The Task is retrieved but then I get the exception:
    "DirectCast(DirectCast(Exc,System.Data.UpdateException).InnerException,System.Data.SqlClient.SqlException).Message

    Violation of PRIMARY KEY constraint 'PK_Task'. Cannot insert duplicate key in object 'dbo.Task'.
    The statement has been terminated."

    1-to-many relationships seem to be beworking fine, only the many-to-many relationship is giving me trouble.
    Any idea what I'm doing wrong?

     

    Thursday, February 25, 2010 2:36 PM

Answers

  • Found the solution.

    At the server component I needed to replace

    CompanyEnts.Employees.AddObject(NewEmployee)

    by

    CompanyEnts.Employees.ApplyChanges(NewEmployee)

    in the InsertEmployee() method and then it worked.

    I would have thought the AddObject() method to be the logical approach in the case of insertion, but ApplyChanges() seems to be the end-all/be-all solution.

    • Marked as answer by Henk S Friday, February 26, 2010 11:05 AM
    Friday, February 26, 2010 11:04 AM