Hi,
We downloaded the latest rc and have noticed an issue with joining fields on related tables. A table AccountRegister containing a nullable FK, InvestementVehiclePortfolioId creates a left join correctly to InvestementVehiclePortfolio, but a subsequent join from InvestementVehiclePortfolio to InvestmentVehicle on a FK InvestementVehicleId, that is not nullable, results in an undesirable inner join, and thus does not return the expected result set.
The following query shows this expression:
e.QueryableSource = from ar in this._linqMetaData.AccountRegister
select new
{
AccountRegisterId = ar.AccountRegisterId,
TransactionDate = ar.TransactionDate,
Amount = ar.Amount,
AccountRegisterTypeName = ar.AccountRegisterType.Name,
InvestmentVehicleName = ar.InvestmentVehiclePortfolio.InvestmentVehicle.Name,
Notes = ar.Notes,
FederalReferenceNumber = ar.FederalReferenceNumber,
TransmitDate = ar.DirectiveTransmittedDate,
EnteredBy = ar.CreateSecurityUser.Name,
EnteredDate = ar.CreateDate,
ApprovedBy = ar.ProofedBySecurityUser.Name
}
;
The profile:
SELECT DISTINCT TOP(@top0) COUNT(*) AS [LPAV_] FROM (
SELECT [LPA_L3].[account_register_id] AS [AccountRegisterId],
[LPA_L3].[transaction_date] AS [TransactionDate],
[LPA_L3].[amount] AS [Amount],
[LPA_L2].[name] AS [AccountRegisterTypeName],
[LPA_L5].[name] AS [InvestmentVehicleName],
[LPA_L3].[notes] AS [Notes],
[LPA_L3].[federal_reference_number] AS [FederalReferenceNumber],
[LPA_L3].[directive_transmitted_date] AS [TransmitDate],
[LPA_L6].[name] AS [EnteredBy],
[LPA_L3].[create_date] AS [EnteredDate],
[LPA_L7].[name] AS [ApprovedBy]
FROM ((((( [dbo].[account_register_type] [LPA_L2]
INNER JOIN [dbo].[account_register] [LPA_L3] ON [LPA_L2].[account_register_type_id]=[LPA_L3].[account_register_type_id])
LEFT JOIN [dbo].[investment_vehicle_portfolio] [LPA_L4] ON [LPA_L4].[investment_vehicle_portfolio_id]=[LPA_L3].[investment_vehicle_portfolio_id])
INNER JOIN [dbo].[investment_vehicle] [LPA_L5] ON [LPA_L5].[investment_vehicle_id]=[LPA_L4].[investment_vehicle_id])
INNER JOIN [dbo].[security_user] [LPA_L6] ON [LPA_L6].[user_id]=[LPA_L3].[create_user_id])
LEFT JOIN [dbo].[security_user] [LPA_L7] ON [LPA_L7].[user_id]=[LPA_L3].[proofed_by_user_id])) [LPA_L1]
From the above, the left join to InvestmentVehiclePortfolio is correct, but the following join to InvestmentVehicle appears not so.
To work around this, we wrote our own joins, as the following code show:
e.QueryableSource = from ar in this._linqMetaData.AccountRegister
join art in this._linqMetaData.AccountRegisterType on ar.AccountRegisterTypeId equals art.AccountRegisterTypeId
join ivp in this._linqMetaData.InvestmentVehiclePortfolio on ar.InvestmentVehiclePortfolioId
equals ivp.InvestmentVehiclePortfolioId into investmentVehiclePortfolios
from investmentVehiclePortfolio in investmentVehiclePortfolios.DefaultIfEmpty()
join iv in this._linqMetaData.InvestmentVehicle on investmentVehiclePortfolio.InvestmentVehicleId
equals iv.InvestmentVehicleId into investmentVehicles
from investmentVehicle in investmentVehicles.DefaultIfEmpty()
select new
{
AccountRegisterId = ar.AccountRegisterId,
TransactionDate = ar.TransactionDate,
Amount = ar.Amount,
AccountRegisterTypeName = art.Name,
InvestmentVehicleName = investmentVehicle.Name,
Notes = ar.Notes,
FederalReferenceNumber = ar.FederalReferenceNumber,
TransmitDate = ar.DirectiveTransmittedDate,
EnteredDate = ar.CreateDate,
}
;
In the codes, I saw this info...
Left/Inner join switch on relations resulting from related entity references.
The relations resulting from related entity references in the query (e.g. o.Customer), will be INNER joins or equi-joins, if the FK fields aren't nullable. If the FK fields (or one of them) is nullable, the relation is a LEFT join towards the FK side (so FK LEFT JOIN PK).
I am not sure if I am to interpret **(or one of them) ** to mean if there is one left join, all others to follow it should be left as well.
Is this a bug?
Thanks!
.
