How LLBL selects which table is the FROM clause?

I am adding Relations to relation predicate bucket, and in FROM clause I am seeing not entity table name from which i Have started but maybe first end relation? which is rather strange IMHO

What's your code you're executing and what sql was produced?

                EntityFields2 extraFields = new EntityFields2((int) FieldIndex.AmmountOfFields);
                extraFields[(int) FieldIndex.SupplierNo] = EntityFieldFactory.Create(SupplierFieldIndex.SupplierNo);
                extraFields[(int) FieldIndex.LogisticArticleNo] = EntityFieldFactory.Create(LogisticArticleFieldIndex.LogisticArticleNo);
                extraFields[(int) FieldIndex.Description1] = EntityFieldFactory.Create(LogisticArticleFieldIndex.Description1);
                extraFields[(int) FieldIndex.Description2] = EntityFieldFactory.Create(LogisticArticleFieldIndex.Description2);
                extraFields[(int) FieldIndex.CurrencyCode] = EntityFieldFactory.Create(PurchaseOrderHeaderFieldIndex.CurrencyCode);
                extraFields[(int) FieldIndex.WholeSalePrice] = EntityFieldFactory.Create(PurchaseOrderItemFieldIndex.WholeSalePrice);
                extraFields[(int) FieldIndex.PreOrderItemNo] = EntityFieldFactory.Create(PreOrderItemFieldIndex.PreOrderItemNo);
                extraFields[(int) FieldIndex.TypeOfPreOrderItem] = EntityFieldFactory.Create(PreOrderItemFieldIndex.TypeOfPreOrderItem);
                extraFields[(int) FieldIndex.PurchaseOrderNo] = EntityFieldFactory.Create(PurchaseOrderHeaderFieldIndex.PurchaseOrderNo);
                extraFields[(int) FieldIndex.PurchaseOrderItemNo] = EntityFieldFactory.Create(PurchaseOrderItemFieldIndex.PurchaseOrderItemNo);
                IEntityField2 poItemQuantity = EntityFieldFactory.Create(PurchaseOrderItemFieldIndex.PurchaseOrderQuantity);
                poItemQuantity.Alias = FieldIndex.PurchaseOrderItemQuantity.ToString();
                extraFields[(int) FieldIndex.PurchaseOrderItemQuantity] = poItemQuantity;
                IEntityField2 poItemDeliveryDate = EntityFieldFactory.Create(PurchaseOrderItemFieldIndex.DeliveryDate);
                poItemDeliveryDate.Alias = FieldIndex.PurchaseOrderItemDeliveryDate.ToString();
                extraFields[(int) FieldIndex.PurchaseOrderItemDeliveryDate] = poItemDeliveryDate;

                bucket.Relations.Add(PurchaseOrderItemEntity.Relations.PurchaseOrderHeaderEntityUsingPurchaseOrderHeaderID);
                bucket.Relations.Add(PurchaseOrderHeaderEntity.Relations.SupplierEntityUsingSupplierID);
                bucket.Relations.Add(PurchaseOrderItemEntity.Relations.LogisticArticleEntityUsingLogisticArticleID);
                bucket.Relations.Add(PurchaseOrderItemEntity.Relations.PreOrderItemEntityUsingPreOrderItemID, JoinHint.Left);
                bucket.Relations.Add(PurchaseOrderItemEntity.Relations.PurchaseOrderItem2DeliveryItemEntityUsingPurchaseOrderItemID, JoinHint.Left);

bucket.PredicateExpression.AddWithAnd(new FieldCompareValuePredicate(EntityFieldFactory.Create(SupplierFieldIndex.CompanyNo), null, ComparisonOperator.Equal, companyNo));
bucket.PredicateExpression.AddWithAnd(new FieldCompareValuePredicate(EntityFieldFactory.Create(SupplierFieldIndex.SupplierNo), null, ComparisonOperator.Equal, supplierNo));
bucket.PredicateExpression.AddWithAnd(new FieldCompareNullPredicate(EntityFieldFactory.Create(PurchaseOrderItem2DeliveryItemFieldIndex.PurchaseOrderItem2DeliveryItemID), null));

Produces SQL that starts select FROM PurchaseOrderHeader I've started adding relations from PurchaseOrderItem so my logical guess is that PurchaseOrderItem is in FROM clause

For results it does not matter in this case, but I would appreciate knowing what I should expect in SQL produced.

exec sp_executesql N'SELECT [dbo].[PurchaseOrderItem].[PurchaseOrderItemNo] AS [PurchaseOrderItemNo],[dbo].[PurchaseOrderItem].[DeliveryDate] AS [PurchaseOrderItemDeliveryDate],[dbo].[PurchaseOrderItem].[PurchaseOrderQuantity] AS [PurchaseOrderItemQuantity],[dbo].[PreOrderItem].[PreOrderItemNo] AS [PreOrderItemNo],[dbo].[PreOrderItem].[TypeOfPreOrderItem] AS [TypeOfPreOrderItem],[dbo].[PurchaseOrderItem].[WholeSalePrice] AS [WholeSalePrice],[dbo].[PurchaseOrderHeader].[CurrencyCode] AS [CurrencyCode],[dbo].[Supplier].[SupplierNo] AS [SupplierNo],[dbo].[LogisticArticle].[LogisticArticleNo] AS [LogisticArticleNo],[dbo].[LogisticArticle].[Description1] AS [Description1],[dbo].[LogisticArticle].[Description2] AS [Description2],[dbo].[PurchaseOrderHeader].[PurchaseOrderNo] AS [PurchaseOrderNo] FROM ((((( [dbo].[PurchaseOrderHeader]  INNER JOIN [dbo].[PurchaseOrderItem]  ON  [dbo].[PurchaseOrderHeader].[PurchaseOrderHeaderID]=[dbo].[PurchaseOrderItem].[PurchaseOrderHeaderID]) INNER JOIN [dbo].[Supplier]  ON  [dbo].[Supplier].[SupplierID]=[dbo].[PurchaseOrderHeader].[SupplierID]) INNER JOIN [dbo].[LogisticArticle]  ON  [dbo].[LogisticArticle].[LogisticArticleID]=[dbo].[PurchaseOrderItem].[LogisticArticleID]) LEFT JOIN [dbo].[PreOrderItem]  ON  [dbo].[PreOrderItem].[PreOrderItemID]=[dbo].[PurchaseOrderItem].[PreOrderItemID]) LEFT JOIN [dbo].[PurchaseOrderItem2DeliveryItem]  ON  [dbo].[PurchaseOrderItem].[PurchaseOrderItemID]=[dbo].[PurchaseOrderItem2DeliveryItem].[PurchaseOrderItemID]) WHERE ( [dbo].[Supplier].[CompanyNo] = @CompanyNo1 And [dbo].[Supplier].[SupplierNo] = @SupplierNo2 And [dbo].[PurchaseOrderItem2DeliveryItem].[PurchaseOrderItem2DeliveryItemID] IS NULL)', N'@CompanyNo1 int,@SupplierNo2 int', @CompanyNo1 = 1, @SupplierNo2 = 7617

Ok, the first relation is always: PK side JOIN FK side after that, those elements are added which aren't in the pack yet.

So your first relation is: PurchaseOrderItemEntity - PurchaseOrderHeaderEntityUsingPurchaseOrderHeaderID Looking at it, PurchaseOrderHeader is the PK side, so this results in: PurchaseOrderHeader INNER JOIN PurchaseOrderItem ON PurchaseOrderHeader.PurchaseOrderHeaderID=PurchaseOrderItem.PurchaseOrderHeaderID

which is correct. PurchaseOrderItem is right there, in the first relation. Then you do: PurchaseOrderHeaderEntity - SupplierEntityUsingSupplierID, no join hint, thus an INNER join. PurchaseOrderHeader is already in the join list (first relation), so Supplier is added:

INNER JOIN Supplier ON Supplier.SupplierID=PurchaseOrderHeader.SupplierID You can't add: PurchaseOrderHeader INNER JOIN Supplier ON... as joins are made up like: table1 JOIN table2 JOIN table3 etc.

Next up is: PurchaseOrderItemEntity - LogisticArticleEntityUsingLogisticArticleID PurchaseOrderItem is already in the join list (first relation), so LogisticsArticle is added: INNER JOIN LogisticArticle ON LogisticArticle.LogisticArticleID=PurchaseOrderItem.LogisticArticleID

Then you have the relation: PurchaseOrderItemEntity LEFT JOIN PreOrderItemEntityUsingPreOrderItemID

Again, PurchaseOrderItem is already in the list so PreOrderItem has to be added. You've specified LEFT JOIN join hint, which means that the join should be towards PurchaseOrderItem. As that's already in the list we can just add: LEFT JOIN PreOrderItem ON PreOrderItem.PreOrderItemID=PurchaseOrderItem.PreOrderItemID

Last relation is: PurchaseOrderItemEntity LEFT JOIN PurchaseOrderItem2DeliveryItemEntityUsingPurchaseOrderItemID Again you specified a left join hint. Again PurchaseOrderItem is already in the list (first relation) so we can just add PurchaseOrderItem2DeliveryItem with LEFT JOIN: LEFT JOIN PurchaseOrderItem2DeliveryItem ON PurchaseOrderItem.PurchaseOrderItemID=PurchaseOrderItem2DeliveryItem.PurchaseOrderItemID

Which makes the following FROM clause:

FROM 
(
    (
        (
            (
                ( 
                    PurchaseOrderHeader INNER JOIN PurchaseOrderItem ON 
                    PurchaseOrderHeader.PurchaseOrderHeaderID=PurchaseOrderItem.PurchaseOrderHeaderID
                ) INNER JOIN Supplier ON 
                Supplier.SupplierID=PurchaseOrderHeader.SupplierID
            ) INNER JOIN LogisticArticle ON 
            LogisticArticle.LogisticArticleID=PurchaseOrderItem.LogisticArticleID
        ) LEFT JOIN PreOrderItem ON 
        PreOrderItem.PreOrderItemID=PurchaseOrderItem.PreOrderItemID
    ) LEFT JOIN PurchaseOrderItem2DeliveryItem ON
    PurchaseOrderItem.PurchaseOrderItemID=PurchaseOrderItem2DeliveryItem.PurchaseOrderItemID
) 

So, I don't see what else to expect, everything is there IMHO and in the right order. If you wanted a different order, please explain what SQL you did expect so I could probably suggest a few modifications to your code.

As I start select from PurchaseOrderItem and this is table I want to fetch columns from I would expect FROM to start from It. It would be more logical

FROM 
(
    (
        (
            (
                ( 
                    PurchaseOrderItem INNER JOIN PurchaseOrderHeader ON                 PurchaseOrderHeader.PurchaseOrderHeaderID=PurchaseOrderItem.PurchaseOrderHeaderID
                ) INNER JOIN Supplier ON 
                Supplier.SupplierID=PurchaseOrderHeader.SupplierID
            ) INNER JOIN LogisticArticle ON 
            LogisticArticle.LogisticArticleID=PurchaseOrderItem.LogisticArticleID
        ) LEFT JOIN PreOrderItem ON 
        PreOrderItem.PreOrderItemID=PurchaseOrderItem.PreOrderItemID
    ) LEFT JOIN PurchaseOrderItem2DeliveryItem ON
    PurchaseOrderItem.PurchaseOrderItemID=PurchaseOrderItem2DeliveryItem.PurchaseOrderItemID
) 

In case of inner joins it's the same, but if you consider Left and Right Joins then it would lead to different results.

Andrius wrote:

As I start select from PurchaseOrderItem and this is table I want to fetch columns from I would expect FROM to start from It. It would be more logical

It builds the from clause with PK side jointype FK side. It takes into account if the join is left/right that it should use the right type to join the data correctly.

So if you specify A LEFT JOIN B and A is the FK side it will become B RIGHT JOIN A because that's the same.

THe code is build this way to have a generic way of creating a from clause no matter what the type of the join is.