实体框架使用VB.Net Lambda进行多对多
我在Visual Studio 2010 Beta 2(.NET framework 4.0 Beta 2)中使用Entity Framework.我从我的数据库创建了一个实体框架.edmx模型,我有一些多对多的关系.
我的数据库模式的一个简单示例是 >角色(ID,名称,活动) 我现在正在编写自定义角色提供程序(继承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 我的问题几乎在这里描述: 我确信那里提供的解决方案效果很好,但是当我在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中. 更新: 通过使用多种方法我更接近: 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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |