I am using Entity Framework in Visual Studio 2010 Beta 2 (.NET framework 4.0 Beta 2). I created the framework.edmx model entity from my database, and I have several many-to-many relationships.
Trivial example of my database schema
- Roles (ID, Name, Asset)
- Members (ID, DateOfBirth, DateCreated)
- RoleMembership (RoleID, MemberID)
Now I am writing a custom role provider (Inheriting System.Configuration.Provider.RoleProvider) and come to write an implementation of IsUserInRole (username, roleName).
The LINQ-to-Entity queries that I wrote when SQL-Profiled all came up with CROSS JOIN statements when I want it is an INNER JOIN for them.
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: An entity and many of the many queries are unsuitable?
I'm sure the solution presented works well, but so far I have been learning Java in uni, and I can basically understand C #. I cannot understand this Lambda syntax, and I need to get a similar example in VB. I scanned the web for most of the afternoon, but I'm no closer to my answer.
So, please, can someone advise how in VB I can build a LINQ statement that would make 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 find out if member 101 is in role 3. (I understand that I probably don't need a connection to the Members table in SQL, but I assume that I will need to enter a Member object in LINQ?)
UPDATE:
I am a little closer using several 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 sure why he uses sp_execsql for the inner join operator and why he is still making a selection to select ALL elements though.
Thanks.
UPDATE 2
I wrote this by turning the above “multiple methods” into lambda expressions, and then all into one query, for example:
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 this does not seem to work: the “Doctor” is not the role that exists, I just put it there for testing purposes, but “allIDs” still gets the value “100;”
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 have listed all the columns from the Members table in *
As you can see, this simply ignores the Role request.