Editor .NET - MJoin + LeftJoin

Editor .NET - MJoin + LeftJoin

washuit-iammwashuit-iamm Posts: 121Questions: 51Answers: 2

Is there an example showing how to LeftJoin out to another table inside an MJoin?

In my code I am trying the following:

.MJoin(new MJoin("InventoryServerWarranty")
    .Set(false)
    .Link("InventoryServer.Id", "InventoryServerInventoryServerWarrantyLine.InventoryServerId")
    .Link("InventoryServerWarranty.Id", "InventoryServerInventoryServerWarrantyLine.InventoryServerWarrantyId")
    .Order("InventoryServerWarranty.CertificateNumber")
    .Model<Warranty>()
    .LeftJoin("InventoryServerWarrantyProvider", "InventoryServerWarrantyProvider.Id", "=", "InventoryServerWarranty.InventoryServerWarrantyProviderId")
)

I get a SQL error with MSSQL The multi-part identifier "InventoryServerWarranty.InventoryServerWarrantyProviderId" could not be bound.

The SQL generated is:

SELECT
    DISTINCT [InventoryServer].[Id] as 'dteditor_pkey',
    [InventoryServerWarranty].[CertificateNumber] as 'CertificateNumber',
    [InventoryServerWarranty].[RegistrationID] as 'RegistrationID',
    [InventoryServerWarranty].[Notes] as 'Notes',
    [InventoryServerWarranty].[DeliveryDate] as 'DeliveryDate',
    [InventoryServerWarranty].[ExpirationDate] as 'ExpirationDate',
    [InventoryServerWarranty].[InventoryServerWarrantyProviderId] as 'InventoryServerWarrantyProviderId',
    [InventoryServerWarranty].[RetiredIncidentNumber] as 'RetiredIncidentNumber',
    [InventoryServerWarranty].[RetiredOn] as 'RetiredOn'
FROM
    [InventoryServer]
    LEFT JOIN [InventoryServerWarrantyProvider] ON [InventoryServerWarrantyProvider].[Id] = [InventoryServerWarranty].[InventoryServerWarrantyProviderId]
    JOIN [InventoryServerInventoryServerWarrantyLine] ON [InventoryServer].[Id] = [InventoryServerInventoryServerWarrantyLine].[InventoryServerId]
    JOIN [InventoryServerWarranty] ON [InventoryServerWarranty].[Id] = [InventoryServerInventoryServerWarrantyLine].[InventoryServerWarrantyId]

ORDER BY
    [InventoryServerWarranty].[CertificateNumber]

I can manually fix this by pulling that left join down to be the last join. IE:

SELECT
    DISTINCT [InventoryServer].[Id] as 'dteditor_pkey',
    [InventoryServerWarranty].[CertificateNumber] as 'CertificateNumber',
    [InventoryServerWarranty].[RegistrationID] as 'RegistrationID',
    [InventoryServerWarranty].[Notes] as 'Notes',
    [InventoryServerWarranty].[DeliveryDate] as 'DeliveryDate',
    [InventoryServerWarranty].[ExpirationDate] as 'ExpirationDate',
    [InventoryServerWarranty].[InventoryServerWarrantyProviderId] as 'InventoryServerWarrantyProviderId',
    [InventoryServerWarranty].[RetiredIncidentNumber] as 'RetiredIncidentNumber',
    [InventoryServerWarranty].[RetiredOn] as 'RetiredOn'
FROM
    [InventoryServer]
    JOIN [InventoryServerInventoryServerWarrantyLine] ON [InventoryServer].[Id] = [InventoryServerInventoryServerWarrantyLine].[InventoryServerId]
    JOIN [InventoryServerWarranty] ON [InventoryServerWarranty].[Id] = [InventoryServerInventoryServerWarrantyLine].[InventoryServerWarrantyId]
    LEFT JOIN [InventoryServerWarrantyProvider] ON [InventoryServerWarrantyProvider].[Id] = [InventoryServerWarranty].[InventoryServerWarrantyProviderId]

ORDER BY
    [InventoryServerWarranty].[CertificateNumber]

I have no way to do this in the Editor fluent code though unless I am missing something.

Answers

  • washuit-iammwashuit-iamm Posts: 121Questions: 51Answers: 2

    Still struggling with this. Maybe this is a bug?

Sign In or Register to comment.