Entity Framework - entity with multiple foreign keys to same table

  • Thread starter Thread starter CGatto
  • Start date Start date
C

CGatto

Hi All,

I am using EF (Framework 3.5 SP1) and have a simple two table demo set up:
Applicants
- applicant_id int
- applicant_pref_lang_coorepondence int (FK to CodeLanguages)
- applicant_pref_lang_exam int (FK CodeLanguages)
- applicant_pref_lang_interview int (FK CodeLanguages)

CodeLanguages
- code_lang_id int
- code_lang_desc

A CodeLanguage entry can have 0, 1, * Applicants
Each language ref in Applicants must have 1 (and only one) CodeLanguage ref.

Problem:
When I bring back an Applicant entity, if all three language references
(coorespondence, exam, and interview) are all the same, lets say 1000
(english), and then I modify one of them, for example to 1001 (french), then
all THREE will be changed to 1001 (french).

Here's the weird part: If all three references are different (lets say
coorespondence=english, exam=french, and interview=spanish) and I change one
of them - then it behaves as expected and only the one I changed is
affected - the others are remain in their original state.

I have spent most of today trying various things such as dropping and
recreating associations in the EDMX, recreating the EDMX datamodel - even
creating a new database. None of this worked - I'm beginning to thing the
issue is with EF and not my code.

Any ideas? Thanks.
 
CGatto said:
Hi All,

I am using EF (Framework 3.5 SP1) and have a simple two table demo set up:
Applicants
- applicant_id int
- applicant_pref_lang_coorepondence int (FK to CodeLanguages)
- applicant_pref_lang_exam int (FK CodeLanguages)
- applicant_pref_lang_interview int (FK CodeLanguages)

CodeLanguages
- code_lang_id int
- code_lang_desc

A CodeLanguage entry can have 0, 1, * Applicants
Each language ref in Applicants must have 1 (and only one) CodeLanguage
ref.

Problem:
When I bring back an Applicant entity, if all three language references
(coorespondence, exam, and interview) are all the same, lets say 1000
(english), and then I modify one of them, for example to 1001 (french),
then all THREE will be changed to 1001 (french).

Here's the weird part: If all three references are different (lets say
coorespondence=english, exam=french, and interview=spanish) and I change
one of them - then it behaves as expected and only the one I changed is
affected - the others are remain in their original state.

I have spent most of today trying various things such as dropping and
recreating associations in the EDMX, recreating the EDMX datamodel - even
creating a new database. None of this worked - I'm beginning to thing the
issue is with EF and not my code.

Any ideas? Thanks.
Click to expand...

An update on the final outcome of this issue. After some very quick and
helpful advice from the EF team at Microsoft it was determined that this is
expected behaviour from EF 3.5 SP1:

"When you query within the service layer for the Applicant where all
languages are the same you end up with two objects, one Applicant with all
three navigation properties pointing to the same CodeLanguage object.WCF
then re-creates this same graph on the client meaning that the three
breakpoints you set are indeed looking at the same property on the same
object"

Microsoft provided the basis for my ultimate solution which is this:

First: Create a Partial Class for the Applicants data object and create
three properties which reference the three language code_ids:

Partial Public Class Applicants

Private _intPrefCoorespLanguage As Integer = 0Private
_intPrefInterviewLanguage As Integer = 0Private _intPrefExamLanguage As
Integer = 0<System.Runtime.Serialization.DataMemberAttribute()> _Public
Property MyPrefCoorespLanguageCodeId() As Integer Get Return
(_intPrefCoorespLanguage) End Get Set(ByVal value As Integer)
_intPrefCoorespLanguage = value End SetEnd
Property<System.Runtime.Serialization.DataMemberAttribute()> _Public
Property MyPrefInterviewLanguageCodeId() As Integer Get Return
(_intPrefInterviewLanguage) End Get Set(ByVal value As Integer)
_intPrefInterviewLanguage = value End SetEnd
Property<System.Runtime.Serialization.DataMemberAttribute()> _Public
Property MyPrefExamLanguageCodeId() As Integer Get Return
(_intPrefExamLanguage) End Get Set(ByVal value As Integer)
_intPrefExamLanguage = value End SetEnd Property<OnSerializing()>
_Private Sub PopulateClientProperties(ByVal sc As StreamingContext)
Me.MyPrefCoorespLanguageCodeId = Me.PrefCoorespLanguage.code_lang_id
Me.MyPrefInterviewLanguageCodeId = Me.PrefInterviewLanguage.code_lang_id
Me.MyPrefExamLanguageCodeId = Me.PrefExamLanguage.code_lang_idEnd SubEnd
Class

Second: Recompile and refresh the client's service reference. Use the three
language code_id properties to bind to controls in xaml

Third: In the server-side update run the following to update the applciant
and its language foreign keys:

myContext = New HR2009Entities'Get original Applicant and feed in changes
from detatched updated Applicant objectDim OrigApp = (From a In
myContext.Applicants Where a.applicant_id =
pobjUpdatedApplicant.applicant_id Select a).First'Apply preferred language
foreign key refsOrigApp.PrefCoorespLanguageReference.EntityKey = _ New
EntityKey("HR2009Entities.CodeLanguages",
"code_lang_id",pobjUpdatedApplicant.MyPrefCoorespLanguageCodeId)OrigApp.PrefInterviewLanguageReference.EntityKey
= _ New EntityKey("HR2009Entities.CodeLanguages", "code_lang_id",
pobjUpdatedApplicant.MyPrefInterviewLanguageCodeId)OrigApplicant.PrefExamLanguageReference.EntityKey
= _ New EntityKey("HR2009Entities.CodeLanguages", "code_lang_id",
pobjUpdatedApplicant.MyPrefExamLanguageCodeId)'Apply Applicant table
native-field
changesmyContext.ApplyPropertyChanges(OrigApp.EntityKey.EntitySetName,
pobjUpdatedApplicant)'Save to
databasemyContext.SaveChanges()myContext.Dispose()
 
Back
Top