Entity Framework many-to-many using VB.Net Lambda

Question

Hello, I'm a newbie to StackOverflow so please be kind ;)

I'm using Entity Framework in Visual Studio 2010 Beta 2 (.NET framework 4.0 Beta 2). I have created an entity framework .edmx model from my database and I have a handful of many-to-many relationships.

A trivial example of my database schema is

Roles (ID, Name, Active)
Members (ID, DateOfBirth, DateCreated)
RoleMembership(RoleID, MemberID)

I am now writing the custom role provider (Inheriting System.Configuration.Provider.RoleProvider) and have come to write the implementation of IsUserInRole(username, roleName).

The LINQ-to-Entity queries which I wrote, when SQL-Profiled, all produced CROSS JOIN statements when what I want is for them to INNER JOIN.

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

My problem is almost exactly described here: http://stackoverflow.com/questions/553918/entity-framework-and-many-to-many-queries-unusable

I'm sure that the solution presented there works well, but whilst I studied Java at uni and I can mostly understand C# I cannot understand this Lambda syntax provided and I need to get a similar example in VB. I've looked around the web for the best part of half a day but I'm not closer to my answer.

So please can somebody advise how, in VB, I can construct a LINQ statement which would do this equivalent in 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

I would use this query to see if Member 101 is in Role 3. (I appreciate I probably don't need the join to the Members table in SQL but I imagine in LINQ I'd need to bring in the Member object?)

UPDATE:

I'm a bit closer by using multiple methods:

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 Profiler shows this:

SQL:BatchStarting

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

RPC:Completed

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]

I'm not certain why it is using sp_execsql for the inner join statement and why it's still running a select to select ALL members though.

Thanks.

UPDATE 2

I've written it by turning the above "multiple methods" into lambda expressions then all into one query, like this:

    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

But it doesn't seem to work: "Doctor" is not a role that exists, I just put it in there for testing purposes, yet "allIDs" still gets set to "100;"

The SQL in SQL Profiler this time looks like this:

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)

For brevity I turned the list of all the columns from the Members table into *

As you can see it's just ignoring the "Role" query... :/

Answer 1

If you are not using associations (Mappings or Foreign Keys) in your schema, you can use this 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

And if you are using associations its this way:

    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

Answer 2

This is how I solved it using Lambda expressions:

    Public Overrides Function IsUserInRole(ByVal username As String, ByVal roleName As String) As Boolean
        Dim retVal As Boolean

        Using db As New CBLEntities
            Dim theRole = db.Roles.Where(Function(x) x.Name = roleName)
            retVal = theRole.Count(Function(r As Role) r.Members.Where(Function(m As Member) m.ID = username).Count > 0) > 0
        End Using

        Return retVal
    End Function