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
Any thoughts on this? Would you consider fixing this in LLBLGen or I should try to find workaround in my application?