Entity Framework many-to-many using VB.Net Lambda
- by bgs264
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... :/
