实体框架使用VB.Net Lambda进行多对多

我在Visual Studio 2010 Beta 2(.NET framework 4.0 Beta 2)中使用Entity Framework.我从我的数据库创建了一个实体框架.edmx模型,我有一些多对多的关系.

我的数据库模式的一个简单示例是

>角色(ID,名称,活动)
>会员(ID,DateOfBirth,DateCreated)
> RoleMembership(RoleID,MemberID)

我现在正在编写自定义角色提供程序(继承System.Configuration.Provider.RoleProvider)并且已经编写了IsUserInRole(username,roleName)的实现.

我写的LINQ-to-Entity查询,当SQL-Profiled时,所有生成CROSS JOIN语句的时候我想要的是INNER JOIN.

Dim query = From m In dc.Members
                    From r In dc.Roles
                    Where m.ID = 100 And r.Name = "Member"
                    Select m

我的问题几乎在这里描述：
Entity framework and many to many queries unusable?

我确信那里提供的解决方案效果很好,但是当我在uni学习Java并且我可以理解C#时,我无法理解提供的Lambda语法,我需要在VB中获得类似的示例.我在网上浏览了半天的最佳时间,但我并没有接近我的答案.

那么请有人建议如何在VB中构建一个LINQ语句,它将在SQL中执行此操作：

SELECT rm.RoleID
FROM RoleMembership rm 
  INNER JOIN Roles r ON r.ID = rm.RoleID
  INNER JOIN Members m ON m.ID = rm.MemberID
WHERE r.Name = 'Member' AND m.ID = 101

我会使用此查询来查看成员101是否在角色3中.
(我很欣赏我可能不需要在SQL中连接到Members表但我想在LINQ中我需要引入Member对象吗？)

更新：

通过使用多种方法我更接近：

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim count As Integer

    Using dc As New CBLModel.CBLEntities

        Dim persons = dc.Members.Where(AddressOf myTest)

        count = persons.Count

    End Using

    System.Diagnostics.Debugger.Break()
End Sub

Function myTest(ByVal m As Member) As Boolean
    Return m.ID = "100" AndAlso m.Roles.Select(AddressOf myRoleTest).Count > 0
End Function

Function myRoleTest(ByVal r As Role) As Boolean
    Return r.Name = "Member"
End Function

SQL事件探查器显示：

SQL：BatchStarting

SELECT 
[Extent1].[ID] AS [ID], 
... (all columns from Members snipped for brevity) ...
FROM [dbo].[Members] AS [Extent1]

RPC：已完成

exec sp_executesql N'SELECT 
[Extent2].[ID] AS [ID], 
[Extent2].[Name] AS [Name], 
[Extent2].[Active] AS [Active]
FROM  [dbo].[RoleMembership] AS [Extent1]
INNER JOIN [dbo].[Roles] AS [Extent2] ON [Extent1].[RoleID] = [Extent2].[ID]
WHERE [Extent1].[MemberID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=100

SQL：BatchCompleted

SELECT 
[Extent1].[ID] AS [ID], 
... (all columns from Members snipped for brevity) ...
FROM [dbo].[Members] AS [Extent1]

我不确定为什么它使用sp_execsql作为内部连接语句以及为什么它仍然运行select以选择所有成员.

谢谢.

更新2

我通过将上面的“多个方法”转换为lambda表达式然后将其全部转换为一个查询来编写它,如下所示：

Dim allIDs As String = String.Empty

    Using dc As New CBLModel.CBLEntities

        For Each retM In dc.Members.Where(Function(m As Member) m.ID = 100 AndAlso m.Roles.Select(Function(r As Role) r.Name = "Doctor").Count > 0)
            allIDs &= retM.ID.ToString & ";"
        Next

    End Using

但它似乎不起作用：“Doctor”不是一个存在的角色,我只是把它放在那里用于测试目的,但“allIDs”仍然设置为“100;”

SQL Profiler中的SQL这次看起来像这样：

SELECT 
[Project1].*
FROM ( SELECT 
    [Extent1].*, 
    (SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[RoleMembership] AS [Extent2]
        WHERE [Extent1].[ID] = [Extent2].[MemberID]) AS [C1]
    FROM [dbo].[Members] AS [Extent1]
)  AS [Project1]
WHERE (100 = [Project1].[ID]) AND ([Project1].[C1] > 0)

为简洁起见,我将Members表中所有列的列表转换为*

正如您所看到的,它只是忽略了“角色”查询.

如果您未在架构中使用关联(映射或外键),则可以使用此sintax：

Dim query = From rm As RoleMembership _
            In RoleMemberships _
            Join m As Member In Members On m.ID Equals rm.MemberID _
            Join r As Role In Roles On r.ID Equals rm.RoleID _
            Where r.Name = "Member" _
                And m.ID = 100 _
            Select rm

如果您以这种方式使用关联：

Dim query2 = From r As Role _
             In Roles _
             Where r.Name = "Member" _
                 And r.Members.Any(Function(m As Member) m.ID = 100) _
             Select r