My Query (fe is my Entities)
Code: Select all
var list = from p in fe.Profiles
join o in fe.Orders on p.ProfileID equals o.ProfileID
join py in fe.Payments on o.OrderID equals py.OrderID
join c in fe.CallHistories on o.OrderID equals c.OrderID
join s in fe.Staffs on c.StaffIDAdvisor equals s.StaffID
where p.ProfileID == ProfileID
select new
{
c.CallID,
s.Name,
c.CallStartTime,
c.Duration,
creditcard = py.CCNumber
};
Code: Select all
SELECT
`Filter1`.`ProfileID`,
`Filter1`.`CallID`,
`Extent4`.`Name`,
`Filter1`.`CallStartTime`,
`Filter1`.`Duration`,
`Filter1`.`CCNumber`,
@gp1 AS `C1`
FROM (SELECT
`Extent1`.`*`,
#end order table
`Extent2`.`CCNumber`,
`Extent2`.`OrderID` AS `OrderID1`,
`Extent2`.`ProfileID` AS `ProfileID1`,
`Extent1`.`StaffIDAdvisor`,
#end payment table
`Extent3`.`Duration`,
`Extent3`.`OrderID` AS `OrderID2`,
`Extent3`.`CallID`,
`Extent3`.`ProfileID` AS `ProfileID2`,
`Extent3`.`StaffIDAdvisor` AS `StaffIDAdvisor1`,
#end callhistory table
FROM `Order` AS `Extent1` INNER JOIN `Payment` AS `Extent2` ON `Extent1`.`OrderID` = `Extent2`.`OrderID` INNER JOIN `CallHistory` AS `Extent3` ON `Extent1`.`OrderID` = `Extent3`.`OrderID`
WHERE `Extent1`.`ProfileID` IS NOT NULL) AS `Filter1` INNER JOIN `Staff` AS `Extent4` ON `Filter1`.`StaffIDAdvisor` = `Extent4`.`StaffID`
WHERE `Filter1`.`ProfileID` = 1
My query relates CallHistory.StaffIDAdvisor to Staff.StaffID and not Order.StaffIDAdvisor. So, is there a reason StaffIDAdvisor is used instead of StaffIDAdvisor1?
Yes, the query executes and returns expected records if I manually add a "1" to StaffIDAdvisor and run the query directly against MySQL