Left join with custom filter

Posts   
 
    
cerberis
User
Posts: 92
Joined: 20-May-2011
# Posted on: 15-Apr-2016 15:00:44   

Hello,

I am investigating some issues in my application. Using LLBLGen 4.2.20160415 .NET 4.6.1

We have a code which automatically adds custom filters so that we would be able to successfully filter data by our tenants. Basically problem occurs with LLBLGen functionality, which reverses LEFT JOIN to RIGHT JOIN so that table with primary key always comes to FROM clause.

Bellow are 2 code examples explaining issue:

Correct one (returns 296 rows):


var collection = new EntityCollection<BuildingRoomEntity>();

var bucket = new RelationPredicateBucket();
var relation = bucket.Relations.Add(BuildingRoomEntity.Relations.ExaminationBatchEntityUsingIdBuildingRoom, JoinHint.Left);
relation.CustomFilter = new PredicateExpression(ExaminationBatchFields.OrgPath == "/1/7/");

bucket.PredicateExpression.Add(BuildingRoomFields.OrgPath == "/1/7/");

var adapter = new MyAdapter();
adapter.FetchEntityCollection(collection, bucket);

Console.WriteLine(collection.Count);

Wrong one (returns 10 rows):


var collection = new EntityCollection<BuildingRoomEntity>();

var bucket = new RelationPredicateBucket();
var relation = bucket.Relations.Add(ExaminationBatchEntity.Relations.BuildingRoomEntityUsingIdBuildingRoom, JoinHint.Left);
relation.CustomFilter = new PredicateExpression(ExaminationBatchFields.OrgPath == "/1/7/");

bucket.PredicateExpression.Add(BuildingRoomFields.OrgPath == "/1/7/");

var adapter = new MyAdapter();
adapter.FetchEntityCollection(collection, bucket);

Console.WriteLine(collection.Count);

The only difference is how relation is added. Basically what happens here: first code snippet generates LEFT JOIN with correct filter. Second code snippet generates RIGHT JOIN but custom filter now is in wrong place and WHERE and ON filters should be switched.

Seems like a bug of LLBLGen, as it has a feeling of writing SQL, however if I write in SQL:


LEFT JOIN [Examination].[ExaminationBatches]  ON  [dbo].[BuildingRooms].[IdBuildingRoom]=[Examination].[ExaminationBatches].[IdBuildingRoom] AND ( ( [Examination].[ExaminationBatches].[OrgPath] = @p1)

or


LEFT JOIN [Examination].[ExaminationBatches]  ON  [Examination].[ExaminationBatches].[IdBuildingRoom]=[dbo].[BuildingRooms].[IdBuildingRoom] AND ( ( [Examination].[ExaminationBatches].[OrgPath] = @p1)

In both case above I get the same result. However in LLBLGen if I mix relation order - I get different output. I am not very very sure, but I remember times when this order was not important.. maybe I am wrong as cannot prove this simple_smile

Any thoughts on this? Would you consider fixing this in LLBLGen or I should try to find workaround in my application?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 18-Apr-2016 01:51:41   

What is the generated SQL of the second snippet?

David Elizondo | LLBLGen Support Team
cerberis
User
Posts: 92
Joined: 20-May-2011
# Posted on: 18-Apr-2016 09:12:49   

exec sp_executesql N'SELECT DISTINCT [dbo].[BuildingRooms].* 
FROM ( [dbo].[BuildingRooms]  
RIGHT JOIN [Examination].[ExaminationBatches]  ON  [dbo].[BuildingRooms].[IdBuildingRoom]=[Examination].[ExaminationBatches].[IdBuildingRoom] AND ( ( [Examination].[ExaminationBatches].[OrgPath] = @p1))) 
WHERE ( [dbo].[BuildingRooms].[OrgPath] = @p2)'
,N'@p1 nvarchar(255),@p2 nvarchar(50)'
,@p1=N'/1/7/',@p2=N'/1/7/'

p.s. changed query field list with "[dbo].[BuildingRooms].*" manualy for to make query more clear.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 19-Apr-2016 10:13:19   

var relation = bucket.Relations.Add(BuildingRoomEntity.Relations.ExaminationBatchEntityUsingIdBuildingRoom, JoinHint.Left);

is a different join than

var relation = bucket.Relations.Add(ExaminationBatchEntity.Relations.BuildingRoomEntityUsingIdBuildingRoom, JoinHint.Left);

The first is BuildingRoom LEFT JOIN ExaminationBatch The second one is: ExaminationBatch LEFT JOIN BuildingRoom

So no wonder you get different results wink

The join hint is for the joins, not the order in the ON clause, as order of operands on an equal comparison is not important (A=B is equal to B=A)

Frans Bouma | Lead developer LLBLGen Pro
cerberis
User
Posts: 92
Joined: 20-May-2011
# Posted on: 19-Apr-2016 19:40:10   

Otis wrote:


var relation = bucket.Relations.Add(BuildingRoomEntity.Relations.ExaminationBatchEntityUsingIdBuildingRoom, JoinHint.Left);

is a different join than

var relation = bucket.Relations.Add(ExaminationBatchEntity.Relations.BuildingRoomEntityUsingIdBuildingRoom, JoinHint.Left);

The first is BuildingRoom LEFT JOIN ExaminationBatch The second one is: ExaminationBatch LEFT JOIN BuildingRoom

So no wonder you get different results wink

The join hint is for the joins, not the order in the ON clause, as order of operands on an equal comparison is not important (A=B is equal to B=A)

Well.. not really different, as I am selecting BuildingRoomEntity and I want to join ExaminationBatchEntity. For me as a user using framework it should be no difference if I use one or another join simple_smile If I set LeftJoin, I expect to get LeftJoin.

I am not 100% sure, but somehow I remember that in some older versions there was no difference. Anyway if that's expected behavior - we can close this thread.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 19-Apr-2016 20:25:43   

Well.. not really different, as I am selecting BuildingRoomEntity and I want to join ExaminationBatchEntity. For me as a user using framework it should be no difference if I use one or another join Regular Smiley If I set LeftJoin, I expect to get LeftJoin.

I'm afraid you are slightly wrong, as it's called LeftJoin and RightJoin, rather than just OuterJoin, so direction/order of entities in the relation does matter.

So in your case you need to use RightJoin instead of LeftJoin.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 20-Apr-2016 09:44:11   

cerberis wrote:

Otis wrote:


var relation = bucket.Relations.Add(BuildingRoomEntity.Relations.ExaminationBatchEntityUsingIdBuildingRoom, JoinHint.Left);

is a different join than

var relation = bucket.Relations.Add(ExaminationBatchEntity.Relations.BuildingRoomEntityUsingIdBuildingRoom, JoinHint.Left);

The first is BuildingRoom LEFT JOIN ExaminationBatch The second one is: ExaminationBatch LEFT JOIN BuildingRoom

So no wonder you get different results wink

The join hint is for the joins, not the order in the ON clause, as order of operands on an equal comparison is not important (A=B is equal to B=A)

Well.. not really different, as I am selecting BuildingRoomEntity and I want to join ExaminationBatchEntity. For me as a user using framework it should be no difference if I use one or another join simple_smile If I set LeftJoin, I expect to get LeftJoin.

Left join between Start entity and End entity of the relation you specified, that has been the case since the beginning.

I am not 100% sure, but somehow I remember that in some older versions there was no difference. Anyway if that's expected behavior - we can close this thread.

Order isn't important if you don't specify a left/right, so it defaults to inner join: A inner join B is the same as B inner join A. However A left join B is different from B left join A. A left join B is equal to B right join A.

Frans Bouma | Lead developer LLBLGen Pro