We use LLBLGen Pro 3.5 in MS VS 2010 and as database SQL Server 2008 R2.
Code snap from our project:
Dim filter AS RelationPredicateBucket = Nothing
Dim entityCollection As IEntityCollection2 = Nothing
Dim sorter As New SortExpression
Dim prefetchPath As New PrefetchPath2(CInt(EntityType))
entityCollection = New EntityCollection(Of ControlModulePinsEntity)
prefetchPath.Add(ControlModulePinsEntity.PrefetchPathControlModuleTypePins)
prefetchPath.Add(ControlModulePinsEntity.PrefetchPathControlModules)
Using adapter As New DataAccessAdapter
adapter.FetchEntityCollection(entityCollection, Filter, 0, sorter, prefetchPath)
End Using
I'm wondering why:
- LLBLGen generates SQL statments with subselect
- The SQL statment is executed twice (see below SQL snaps from profiler)
SELECT DISTINCT [Tables].[ControlModulePins].[ID], [Tables].[ControlModulePins].[CMID], [Tables].[ControlModulePins].[CMTypePinID]
FROM (( [Tables].[ControlModules] [LPA_C1]
INNER JOIN [Tables].[vw_AllControlModulePins] [LPA_A2]
ON [LPA_C1].[ID]=[LPA_A2].[CMID])
INNER JOIN [Tables].[ControlModulePins]
ON [Tables].[ControlModulePins].[ID]=[LPA_A2].[PinID])
WHERE ( [LPA_A2].[CMID] IN (SELECT [Tables].[ControlModules].[ID] FROM [Tables].[ControlModules] ))
SELECT DISTINCT [LPA_C1].[ID] AS [ID0], [Tables].[ControlModulePins].[ID] AS [ID1]
FROM (( [Tables].[ControlModules] [LPA_C1]
INNER JOIN [Tables].[vw_AllControlModulePins] [LPA_A2]
ON [LPA_C1].[ID]=[LPA_A2].[CMID])
INNER JOIN [Tables].[ControlModulePins]
ON [Tables].[ControlModulePins].[ID]=[LPA_A2].[PinID])
WHERE ( ( [LPA_A2].[CMID] IN (SELECT [Tables].[ControlModules].[ID] FROM [Tables].[ControlModules] )))