Editor .NET - MJoin + LeftJoin
Editor .NET - MJoin + LeftJoin
washuit-iamm
Posts: 121Questions: 51Answers: 2
in Editor
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
Still struggling with this. Maybe this is a bug?