Hi guys,

I've a question regarding fetching data I have this code:

            using (var adapter = ...)
            {
                var fields = new ResultsetFields(2);
                fields.DefineField(PropertyFields.Id, 0);
                fields.DefineField(ObjectFields.Name, 1);
                var bucket = new RelationPredicateBucket();
                bucket.Relations.Add(PropertyEntity.Relations.ObjectEntityUsingObjectId, JoinHint.Left);
                var datatable = new DataTable();
                adapter.FetchTypedView(fields, datatable, bucket, true);
            }

I have three entities 1. TypeEntity - base class ID: int Name: string Other fields ...

1. ObjectEntity - inherits from TypeEntity ID: int Properties: Collection of PropertyEntity Other fields ...

2. PropertyEntity ID: int Name: string Object: ObjectEntity - (nullable field) Other fields ...

As you can see, I'm trying to do a left join here, i.e. I'm selecting all properties and trying to get name of the object the property belongs to, if it is not null.

Llblgen generates this statement, which is not correct as for me:

SELECT [LPA_L3].[ID] AS [Id], [LPA_L1].[Name] 
FROM (( [net].[Object] [LPA_L2] 
   RIGHT JOIN [net].[Property] [LPA_L3]  ON  [LPA_L2].[ID]=[LPA_L3].[ObjectID]) 
   LEFT JOIN [net].[Type] [LPA_L1]  ON  [LPA_L1].[ID]=[LPA_L2].[ID]) 
WHERE ( ( ( [LPA_L2].[ID] IS NOT NULL)))

Am I doing anything wrong here?

I'm using llblgen 3.1.11.208 (file version of SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll) Inheritance is Target-per-Entity.

What is that query wrong?

RIGHT JOIN [net].[Property] [LPA_L3] ON [LPA_L2].[ID]=[LPA_L3].[ObjectID]

Above is exaclty as doing a LEFT JOIN between Property and Object. LLBLGen has a proccess of optimizing and reorder the joins, that's why you see a RIGHT instead of LEFT but the operation result is the same.

And the additional LEFT is there because the inheritance, but it's doing nothing wrong there. In my opinion that query should return the desired results. Isn't like that?

Wrong part is the where clause:

WHERE ( ( ( [LPA_L2].[ID] IS NOT NULL)))

Which basically is a filter for sub type. But it should probably be in the join condition not on the where clause

Mmm I see: the X<-Y, Z m:1 Y, left join paradox. This has been reported and fixed in April of this year for TypedLists, see the discussion: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=19586&StartAtMessage=0&#110426

If I'm not mistaken, your case is the same, the only difference is that you are using a DynamicList and not a TypedList, but at then end the fix should work in this case too. I see you are using a runtime library a little bit old. Please update to the latest version and try again. I will do a test as well to see if this is working.

Thanks, it solved the problem.

Anton