Hello,

I have the following data model (see the atachment); I need to get to all records in PortfolioPermission_SecurityGroup for user with loign name ‘john’ and Company ID = 61. This is my code:

                qg = qg.WithPath(new PathEdge<SecurityGroupEntity>(PortfolioPermissionSecurityGroupEntity.PrefetchPathSecurityGroup,
                    new PathEdge<SecurityUserSecurityGroupEntity>(SecurityGroupEntity.PrefetchPathSecurityUserSecurityGroups,
                        new PathEdge<SecurityUserEntity>(SecurityUserSecurityGroupEntity.PrefetchPathSecurityUser))));

                qg = qg.Where(d => d.CompanyId == 61);
                qg = qg.Where(d => d.SecurityGroup.SecurityUserSecurityGroups.Any(q => q.SecurityUser.LoginName == "john"));

The generated SQL gives an error (I have replaced @LoginName2 and @CompanyId1 with actual values for convenience):

SELECT DISTINCT 
    [LPLA_1].[SecurityGroup_ID] AS [SecurityGroupId], 
    [LPLA_1].[Company_ID] AS [CompanyId], 
    [LPLA_1].[SecurityRights_ID] AS [SecurityRightId] 
FROM [dbo].[PortfolioPermission_SecurityGroup] [LPLA_1]  
    WHERE ( ( ( ( ( [LPLA_1].[Company_ID] = 61)) AND  
    EXISTS (SELECT [LPA_L2].[SecurityGroup_ID] AS [SecurityGroupId] 
            FROM ( [dbo].[SecurityUser] [LPA_L1]  INNER JOIN 
                    [dbo].[SecurityUser_SecurityGroup] [LPA_L2]  ON  [LPA_L1].[SecurityUser_ID]=[LPA_L2].[SecurityUser_ID]) 
                        WHERE ( [LPLA_2].[SecurityGroup_ID] = [LPA_L2].[SecurityGroup_ID] AND 
                              ( [LPA_L1].[SecurityUser_LoginName] = 'john'))))))

The error is:

The multi-part identifier "LPLA_2.SecurityGroup_ID" could not be bound.

If I replace the LPLA_2 with LPLA_1 (as follow) the code works;

SELECT DISTINCT 
    [LPLA_1].[SecurityGroup_ID] AS [SecurityGroupId], 
    [LPLA_1].[Company_ID] AS [CompanyId], 
    [LPLA_1].[SecurityRights_ID] AS [SecurityRightId] 
FROM [dbo].[PortfolioPermission_SecurityGroup] [LPLA_1]  
    WHERE ( ( ( ( ( [LPLA_1].[Company_ID] = 61)) AND  
    EXISTS (SELECT [LPA_L2].[SecurityGroup_ID] AS [SecurityGroupId] 
            FROM ( [dbo].[SecurityUser] [LPA_L1]  INNER JOIN 
                    [dbo].[SecurityUser_SecurityGroup] [LPA_L2]  ON  [LPA_L1].[SecurityUser_ID]=[LPA_L2].[SecurityUser_ID]) 
                        WHERE ( 
/* -> */ [LPLA_1].[SecurityGroup_ID]  /* <- */  = [LPA_L2].[SecurityGroup_ID] AND 
                            ( [LPA_L1].[SecurityUser_LoginName] = 'john'))))))

Now the question: How can I work wround this and can I expect this to be fixed in the next version.

Thanks a lot Kiril

Which build of the runtime are you using? (see guidelines in this forum)? Your problem looks like a bug which was fixed on the 24th of June.

I Also think 'Any' isn't useful, you want to filter on two fields, so a simple where would do. Also, keep in mind that using extension methods is ok, but don't bury the WithPath calls deep inside a query as they could be wrapped inside another query this way and be ignored.

Thanks for the really quick answer!

I am using LLBLGen 2.6; The runtime version is: SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll - 2.6.08.0612 SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll - 2.6.08.0616 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll - 2.6.08.0619

I would love to use any other construction including simple were clause but I couldn’t figure it out how to do it. If u have any ideas how to ‘re-phrase’ it… please

Kiril wrote:

Thanks for the really quick answer!

I am using LLBLGen 2.6; The runtime version is: SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll - 2.6.08.0612 SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll - 2.6.08.0616 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll - 2.6.08.0619

Please download the latest build (2.6.08.0624 == june 24th). It should fix your problem with the alias/table specification error in the query.

I would love to use any other construction including simple were clause but I couldn’t figure it out how to do it. If u have any ideas how to ‘re-phrase’ it… please

with a contains query, but indeed Any should also work. so you first specify a linq query which performs a select of loginnames and securitygroupid's and then do a Contains on that by passing in the security group id of 'd', but it will likely result in the same SQL as the Any method.

Please let me know if the latest build from the customer area doesn't fix your problem. Be sure you're using the latest build in your application if it still occurs. (And I then also need to know the whole query)