latest llblgen, .net 2.0 adapter

I want to recreate this query with llblgen SELECT P1.* FROM [HumanResources].[EmployeeNested] AS P1 inner join [HumanResources].[EmployeeNested] AS P2 on P1.lft > P2.lft AND P1.lft < P2.rgt and P2.[EmployeeId] =3;

This is my attempt

        EntityCollection<EmployeeNestedEntity> emps = new EntityCollection<EmployeeNestedEntity>();

        IPredicateExpression customFilter = new PredicateExpression();
        customFilter.Add(EmployeeNestedFields.Lft.SetObjectAlias("P1") > EmployeeNestedFields.Lft.SetObjectAlias("P2"));
        customFilter.Add(EmployeeNestedFields.Lft.SetObjectAlias("P1") < EmployeeNestedFields.Rgt.SetObjectAlias("P2"));
        customFilter.Add(EmployeeNestedFields.EmployeeId.SetObjectAlias("P2") == 3);

        IRelationPredicateBucket bucket = new RelationPredicateBucket();
        EntityRelation relation = new EntityRelation(EmployeeNestedFields.EmployeeId, EmployeeNestedFields.EmployeeId, RelationType.OneToOne);
        IEntityRelation rel = bucket.Relations.Add(relation, "P1", "P2", JoinHint.Inner);
        rel.CustomFilter=customFilter;
        rel.CustomFilterReplacesOnClause=true;
        DataAccessAdapter da = new DataAccessAdapter();
        da.FetchEntityCollection(emps, bucket);

But it results in:

SELECT DISTINCT [AdventureWorks].[HumanResources].[EmployeeNested].[EmployeeId], [AdventureWorks].[HumanResources].[EmployeeNested].[lft] AS [Lft], [AdventureWorks].[HumanResources].[EmployeeNested].[rgt] AS [Rgt] FROM ( [AdventureWorks].[HumanResources].[EmployeeNested] [LPA_P1] INNER JOIN [AdventureWorks].[HumanResources].[EmployeeNested] [LPA_P2] ON ( [LPA_P2].[lft] > [LPA_P1].[lft] AND [LPA_P2].[lft] < [LPA_P1].[rgt] AND [LPA_P1].[EmployeeId] = @EmployeeId1))

two things wrong: - there is no alias for the fields in the select statement [AdventureWorks].[HumanResources].[EmployeeNested].[EmployeeId] should be [LPA_P1].[EmployeeId] -[LPA_P1].[EmployeeId] = @EmployeeId1) [LPA_P1] but I specified customFilter.Add(EmployeeNestedFields.EmployeeId.SetObjectAlias("P2") == 3); P2 not P1.

Hello,

for the alias, you can see LPA_P2 is corresponding to "P1" and LPA_P1 is corresponding to "P2" so the query is right for "[LPA_P1].[EmployeeId] = @EmployeeId1". The alias name in the query string is not the alias name you defined in your code, it's generated on execution time. For example if I define as alias in a relation "MySuperalias1", the used alias for the query will be LPA_M1. You can also defined only the end relation alias so you don't need to defined "P1" for example. Is your request sends you what you want?

Ok so the alias is correct but the query isn't I wanted the sql mentioned on top of my post but the sql created is not valid

It throws an exception.

Hello,

Could you post the exception?

Is the exception is on field alias for the select statement?

A first chance exception of type 'System.InvalidOperationException' occurred in System.dll Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT DISTINCT [AdventureWorks].[HumanResources].[EmployeeNested].[EmployeeId], [AdventureWorks].[HumanResources].[EmployeeNested].[lft] AS [Lft], [AdventureWorks].[HumanResources].[EmployeeNested].[rgt] AS [Rgt] FROM ( [AdventureWorks].[HumanResources].[EmployeeNested] [LPA_P1] INNER JOIN [AdventureWorks].[HumanResources].[EmployeeNested] [LPA_P2] ON ( [LPA_P2].[lft] > [LPA_P1].[lft] AND [LPA_P2].[lft] < [LPA_P1].[rgt] AND [LPA_P1].[EmployeeId] = @EmployeeId1)) Parameter: @EmployeeId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3.

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll 'emp.GetSubOrinates(2)' threw an exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' base {SD.LLBLGen.Pro.ORMSupportClasses.ORMException}: {"An exception was caught during the execution of a retrieval query: The multi-part identifier \"AdventureWorks.HumanResources.EmployeeNested.EmployeeId\" could not be bound.\r\nThe multi-part identifier \"AdventureWorks.HumanResources.EmployeeNested.lft\" could not be bound.\r\nThe multi-part identifier \"AdventureWorks.HumanResources.EmployeeNested.rgt\" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."} Parameters: {System.Data.SqlClient.SqlParameterCollection} QueryExecuted: "\r\n\tQuery: SELECT DISTINCT [AdventureWorks].[HumanResources].[EmployeeNested].[EmployeeId], [AdventureWorks].[HumanResources].[EmployeeNested].[lft] AS [Lft], [AdventureWorks].[HumanResources].[EmployeeNested].[rgt] AS [Rgt] FROM ( [AdventureWorks].[HumanResources].[EmployeeNested] [LPA_P1] INNER JOIN [AdventureWorks].[HumanResources].[EmployeeNested] [LPA_P2] ON ( [LPA_P2].[lft] > [LPA_P1].[lft] AND [LPA_P2].[lft] < [LPA_P1].[rgt] AND [LPA_P1].[EmployeeId] = @EmployeeId1))\r\n\tParameter: @EmployeeId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3.\r\n"

When runnin the created query in MsSqlManagment studio: SELECT DISTINCT [AdventureWorks].[HumanResources].[EmployeeNested].[EmployeeId], [AdventureWorks].[HumanResources].[EmployeeNested].[lft] AS [Lft], [AdventureWorks].[HumanResources].[EmployeeNested].[rgt] AS [Rgt] FROM ( [AdventureWorks].[HumanResources].[EmployeeNested] [LPA_P1] INNER JOIN [AdventureWorks].[HumanResources].[EmployeeNested] [LPA_P2] ON ( [LPA_P2].[lft] > [LPA_P1].[lft] AND [LPA_P2].[lft] < [LPA_P1].[rgt] AND [LPA_P1].[EmployeeId] = 3))

I get Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "AdventureWorks.HumanResources.EmployeeNested.EmployeeId" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "AdventureWorks.HumanResources.EmployeeNested.lft" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "AdventureWorks.HumanResources.EmployeeNested.rgt" could not be bound.

Replacing AdventureWorks.HumanResources.EmployeeNested with [LPA_P2] SELECT DISTINCT [LPA_P2].[EmployeeId], [LPA_P2].[lft] AS [Lft],[LPA_P2].[rgt] AS [Rgt] FROM ( [AdventureWorks].[HumanResources].[EmployeeNested] [LPA_P1] INNER JOIN [AdventureWorks].[HumanResources].[EmployeeNested] [LPA_P2] ON ( [LPA_P2].[lft] > [LPA_P1].[lft] AND [LPA_P2].[lft] < [LPA_P1].[rgt] AND [LPA_P1].[EmployeeId] = 3))

Gets me the exact query I was trying to create.

The table in question is:

CREATE TABLE [HumanResources].[EmployeeNested]( [EmployeeId] [int] NOT NULL, [lft] [int] NULL, [rgt] [int] NULL, CONSTRAINT [PK_EmployeeNested] PRIMARY KEY CLUSTERED ( [EmployeeId] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

Try not to use an alias for the first entity, you don't have to.