EF6 CodeFirst - Property in Class (Table) with Navigation to itself

Question

Hy Community

Sorry for my bad english, my mainlanguage is german.

Im using EF 6 in Codefirst. I have a Class like this.

Public Class User

     <Key> _
     Public Property ID() As Integer
     <Required> _
     <StringLength(50)>
     Public Property Username() As String
     <DefaultValue(True)> _
     Public Property IsActive() As Boolean
...
...
...

     Public Property DefaultRepresentationID() As System.Nullable(Of Integer)

     <ForeignKey("DefaultRepresentationID")> _
     Public Overridable Property DefaultRepresentation() As User

End class

This is working fine, in the DB i have i column named 'DefaultRepresentationID'

But if i put another property (of User) in the class like this:

Public Class User

     <Key> _
     Public Property ID() As Integer
     <Required> _
     <StringLength(50)>
     Public Property Username() As String
     <DefaultValue(True)> _
     Public Property IsActive() As Boolean
...
...
...

     Public Property DefaultRepresentationID() As System.Nullable(Of Integer)

     <ForeignKey("DefaultRepresentationID")> _
     Public Overridable Property DefaultRepresentation() As User

     Public Property BossID() As System.Nullable(Of Integer)

     <ForeignKey("BossID")> _
     Public Overridable Property Boss() As User


End Class

Then i get the following errormessage from ef.

Unable to determine the principal end of an association between the types 'VKModel.User' and 'VKModel.User'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

But i'am using DataAnnotation for setting the foreignkey. Has anybody a tipp for me how i can solve this?

Very thanks in advance
Sascha

Answer 1

Hi, Sascha,

if two or more foreign key properties "point" to the same "foreign class" by means of a corresponding navigation property, EF is not able to disambiguate these.

Data annotations will not help in this case. You will need to utilize the EF Fluent API in order to give EF explicit information regarding both relations pointing to the same User class.

You add Fluent API code to your version of the DbContext class by overriding the OnModelCreating() function and adding Fluent API code to it.

Here's my sample code (I have added all three files of my sample to give a thorough example. Please find the Fluent API code in the bottom file (MyContext.vb)):

Module1.vb

Imports System.Collections.Generic
Imports System.Data.Entity
Imports System.Linq

Module Module1
	Sub Main()
		Database.SetInitializer(Of MyContext)(New DropCreateDatabaseAlways(Of MyContext))

		Using ctx As New MyContext
			Dim users As IEnumerable(Of User)

			users = From user In ctx.Users Select user
		End Using
	End Sub

End Module

User.vb

Imports System.ComponentModel.DataAnnotations

Public Class User
	Public Property Id() As Integer?
	<Required>
	Public Property Username() As String

	Public Property RepresentativeId() As Integer?
	Public Overridable Property Representative() As User

	Public Property BossId() As Integer?
	Public Overridable Property Boss() As User
End Class

MyContext.vb

Imports System.Data.Entity

Public Class MyContext
	Inherits DbContext

	Public Property Users As DbSet(Of User)



	Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
		modelBuilder.Entity(Of User).HasOptional(Function(u) u.Representative).WithMany().HasForeignKey(Function(u) u.RepresentativeId)
		modelBuilder.Entity(Of User).HasOptional(Function(u) u.Boss).WithMany().HasForeignKey(Function(u) u.BossId)
	End Sub
End Class

HTH,

Axel

---

Still people out there alive using the keyboard?

Working with SQL Server/Visual Studio/Office/Windows and their poor keyboard support they seem extinct...

Answer 2

Wow, many, many thanks for the reply. You saved my Weekend!

Until today i never used the FluetAPI. You are my hero. Many thanks for the very good and detailed answere.

Is there a way to "Extrakt" the code that th EF generate on Startup for 'OnModelCreating', to see how it works for the complete model and do it manually for the complete model?

Have a nice day
Sascha

Answer 3

I'm very glad that my suggestion helped you on solving your problem.

Unfortunately, there is no way to get Fluent API code generated.

Usually, EF does a very good job in discovering the necessary database mapping automatically and in-memory. Only in rare cases the automatic mapping algorithm comes to its limits. In those cases you need to apply a few manual hints for the mapping algorithm so it can see where you want to go.

You probably wonder why such a simple thing as two properties pointing to the same class will cause a problem for the mapping algorithm. This is caused by the fact that this algorithm was originally created for the Database First/Model First version of EF. Internally, in order to create a relation mapping, it wants to create a two-way binding. So it's trying to create some kind of a "back-pointer" from the class to the two foreign key properties. But it lacks the ability to derive a matching back-pointer just from the foreign-key information. It simply can't decide which one to take from the two foreign keys, because both match the necessary preconditions. So that's what you've got to provide manually. Perhaps Microsoft is going to fix this quirk some time in the future.

Unfortunately, MSDN library lacks being a good source of information regarding thorough information on EF. But there's a small book available from O'Reilly that has become a standard information source by now. It contains all the necessary information on EF, Code First, Data Annotations and the Fluent API: "Programming Entity Framework - Code First". It's four years old by now, but until today it's the latest thorough souce of information there is.

Cheers,

Axel

PS: If you find my answer helpful, would you want to mark it as the answer to your question? ;)

---

Still people out there alive using the keyboard?

Working with SQL Server/Visual Studio/Office/Windows and their poor keyboard support they seem extinct...

Answer 4

Many thanks again for your reply. Now i can understand why i must work with Fluent API for this problem.

Thanks for the booktip but my mainlanguage is german and i cant enought english to read a book about EF in english.

It is very hard to find a good book in german about EF but i often look webcasts.

Again many thanks. Your answers was very helpful.

cheers
Sascha