How to work with foreign keys when using Entity Framework code first

Question

I create a Menu class that has a self-relation.

Public Class Menu
    Sub New()
        Me.lstmenu = New List(Of Menu)
    End Sub
    Property MenuID As Integer
    Property Title As String
    Property Description As String
    Property URL As String
    Property lstmenu As ICollection(Of Menu)
End Class

Entity Framework code-first creates a table from this class with a self relation.

I want to write a query with Linq to return me all menus that their foreign key are null but all I have is a lstmenu that hold a list of menus. And I can't find any FK column to use in my query.

Please help me how to write a query to find all records that their foreign key are null.

Thanks to @marc_c for edit

Kashisaz98
– Kashisaz98

2016-01-04 19:48:35 +00:00
Commented Jan 4, 2016 at 19:48 — Kashisaz98
– Kashisaz98, Commented Jan 4, 2016 at 19:48

ocuenca · Accepted Answer · 2016-01-04 17:30:43Z

1

One thing you can do is declare the FK property in your Menu entity as nullable integer and the navigation property that represent the other end of your relationship. The following solution is using Data Annotations:

Public Class Menu
    Sub New()
        Me.lstmenu = New List(Of Menu)
    End Sub
    Property MenuID As Integer

    <ForeignKey("ParentMenu")>
    Property ParentMenuID As Integer? 'Change the FK name and nav. property name at your convinience
    Property ParentMenu As Menu

    Property Title As String
    Property Description As String
    Property URL As String
    Property lstmenu As ICollection(Of Menu)
End Class

If you want to configure your relationship using Fluent Api to avoid using attributes in your model, you can also do the same overriding OnModelCreating method of your context:

Protected Overrides Sub OnModelCreating(ByVal modelBuilder As DbModelBuilder)

    modelBuilder.Entity(Of Menu)().
    HasOptional(Function(t) t.ParentMenu).
    WithMany(Function(t) t.lstmenu).
    HasForeignKey(Function(t) t.ParentMenuId)

End Sub

Now you can verify in your queries if a menu have associated a parent menu checking if the FK property is null or not

answered Jan 4, 2016 at 17:30

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kashisaz98 Over a year ago

Thanks a lot @octavioccl your first solution works perfect. I do this: <code> 'Public Class Menu Sub New() Me.lstmenu = New List(Of Menu) End Sub Property MenuID As Integer <ForeignKey("lstmenu")> Property ParentMenuID As Integer? Property Title As String Property Description As String Property URL As String Property lstmenu As ICollection(Of Menu) End Class' </code> and it works well. thanks again

Collectives™ on Stack Overflow

How to work with foreign keys when using Entity Framework code first

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related