vb.net - 显式在实体框架中显示多对多表

Question

我正在尝试学习实体框架以尝试从 Linq 转移到 SQL，并且我尝试在多对多递归表（树结构）上转换一些代码失败。我需要进行全表读取并在内存中准备树，因为使用大量查询在数据库中递归太慢了。

我有一个带有 Projects 表的数据库，以及另一个名为 ProjectsTree 的表。使用 Linq to SQL，我可以访问 ProjectsTree 表，但不能使用 Entity Framework。它以一种我似乎无法直接查询此表的方式将其置于关联中。

这是我尝试将 Linq 转换为 SQL 转换为 Entity Framework 之前的代码，它有效。也许我应该坚持使用Linq to SQL，而不是学习新的东西，如果没有办法做到这一点，我可能会倒退，或者让两者并存。

 Private Class cProjectTree2
      Public Project As PDMVault.Project
      Public ChildTree As List(Of cProjectTree2)

   End Class

   ''' <summary>
   ''' Gets the complete PDM project tree that can be added to a tree node.
   ''' Each node has the description in the node text field and the primary key in the tag.
   ''' </summary>
   ''' <returns></returns>
   Public Function GetPDMProjectTree() As TreeNode
      ' To generate the tree, first read the projects table with a complete table scan, then the ProjectTree with a complete table scan.
      ' Create a dictionary of objects of type cRecursiveProjectTree, but only the project is set on the first pass, with a reference to it based on its key.
      ' After the dictionary is built, then link up children to parent using the dictinary.
      ' Finally, use the created tree to create the node structure for the tree view.

      Dim Diag_Stopwatch As New Stopwatch

      Dim IDtoTree As New Generic.Dictionary(Of Long, cProjectTree2)

      Dim C = New PDMVault.DataContext1
      '  C.Log = Console.Out

      Dim Projects = C.Projects.ToList  ' Database list of trees.

''''''''''''''''''''''This is the line that fails.  ProjectTrees only shows up as an association, and I can't seem to get access to it for a full table scan 
          Dim Tree = C.ProjectTrees.ToList   ' Database hierarcy of the projects in the previous statement

'''''''''''''''''''''''''''''''''''''''''''''

      ' Create the dictionary with only the "Project" item set
      For Each P In Projects
         Dim ProjectTreeEntry As New cProjectTree2
         ProjectTreeEntry.ChildTree = New List(Of cProjectTree2)
         ProjectTreeEntry.Project = P
         IDtoTree.Add(P.ProjectID, ProjectTreeEntry)
      Next

      ' Now that the dictionary has been created, the children can be linked to the parent.
      For Each T In Tree
         Dim Parent As cProjectTree2 = Nothing
         Dim Child As cProjectTree2 = Nothing
         IDtoTree.TryGetValue(T.ProjectID, Parent)
         IDtoTree.TryGetValue(T.ChildProject, Child)
         Parent.ChildTree.Add(Child)
      Next

      ' The tree has been built, create the tree view from the tree (using a little recursion)


      Dim GetChildTrees As Func(Of cProjectTree2, TreeNode) =
         Function(P As cProjectTree2) As TreeNode
            Dim Result As New TreeNode
            For Each Child In P.ChildTree.OrderBy(Function(ProjectNode) ProjectNode.Project.Name)
               Dim N = GetChildTrees(Child)
               If N IsNot Nothing Then
                  Result.Nodes.Add(N)
               End If
            Next
            Result.Tag = P.Project.ProjectID
            Result.Text = P.Project.Name
            Return Result
         End Function

      Dim RootProject As cProjectTree2 = Nothing
      If IDtoTree.TryGetValue(1, RootProject) = True Then
         Dim N2 As TreeNode = GetChildTrees(RootProject)
         Return N2
      Else
         Return Nothing
      End If
   End Function

标签： vb.netentity-framework

Answer 1

我非常接近倒退并坚持使用 LINQ to SQL，但这是一个新项目，我想在进行大量代码投资之前学习 EF。通过对 Entities Framework 进行一些试验，以下内容可以很好地处理递归树，而无需访问 ProjectTrees 表。

 Public Function GetPDMProjectTree() As TreeNode
      Dim Diag_Stopwatch As New Stopwatch
      Diag_Stopwatch.Start()

      Dim C = New PDMVault.DataContext1
      C.Configuration.LazyLoadingEnabled = False ' Necessary for the materialization to work in the next line 
      Dim MaterializeDatabase = C.Projects.ToList

      C.Database.Log = Sub(Log) Debug.Print(Log) ' Verify that only two table scans occurs and that it's not hitting repeatedly

      Dim RootProject = (From P In C.Projects Where P.ProjectID = 1).SingleOrDefault

      If RootProject Is Nothing Then Return Nothing

      Dim GetTree As Func(Of PDMVault.Project, TreeNode) =
         Function(P As PDMVault.Project) As TreeNode
            Dim Result As New TreeNode
            For Each Child In P.Projects1.OrderBy(Function(ProjectNode) ProjectNode.Name)
               Result.Nodes.Add(GetTree(Child))
            Next
            Result.Tag = P.ProjectID
            Result.Text = P.Name
            Return Result
         End Function

      If RootProject Is Nothing Then Return Nothing
      Debug.Print($"Tree building time={Diag_Stopwatch.ElapsedMilliseconds / 1000:#0.00}")
      Return GetTree(RootProject)

   End Function

对于 SO 档案，这是以前的方法，它通过两次访问数据库（一次用于初始字典，一次用于根目录）并在我了解关闭延迟加载之前使用了外部字典，可能不是最佳的作为我的最终解决方案。

 Public Function GetPDMProjectTree2() As TreeNode

      Dim Diag_Stopwatch As New Stopwatch

      Dim C = New PDMVault.DataContext1

      C.Database.Log = Sub(Log) Debug.Print(Log) ' Verify that only one table scan occurs and that it isn't an N+1 problem.

      ' Force a complete table scan before starting the recursion below, which will come from cached content

      Dim ProjectTreeFromDatabase = (From P In C.Projects
                                     Select Project = P,
                                            Children = P.Projects1).ToDictionary(Function(F) F.Project.ProjectID)


      Dim GetTree As Func(Of PDMVault.Project, TreeNode) =
         Function(P As PDMVault.Project) As TreeNode
            Dim Result As New TreeNode
            For Each Child In ProjectTreeFromDatabase(P.ProjectID).Children.OrderBy(Function(ProjectNode) ProjectNode.Name)
               Dim N = GetTree(Child)
               If N IsNot Nothing Then
                  Result.Nodes.Add(N)
               End If
            Next
            Result.Tag = P.ProjectID
            Result.Text = P.Name
            Return Result
         End Function

      Dim RootProject = (From P In C.Projects Where P.ProjectID = 1).SingleOrDefault
      If RootProject Is Nothing Then Return Nothing
      Return GetTree(RootProject)

   End Function

这两种解决方案都可以防止重复访问数据库。