Entity query generates the wrong SQL
Posted: Fri 12 Nov 2010 16:09
Not sure if this is a bug, or something I screwed up in the query (or schema for that matter). My query uses 4 joins, and my trace string looks wrong. I was able to correct it by manually adding a "1" to the field in question, and it returned as expected.
My Query (fe is my Entities)
And my trace string looks like this (I've omitted several irrelevant fields, but all fields were selected from order, payment and callhistory table. Fields were renamed automatically):
Notice the `Filter1`.`StaffIDAdvisor` = ... is not StaffIDAdvisor1, but only StaffIDAdvisor (without the "1" appended)--why is this generated this way?
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
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` = 1My 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