Entity query generates the wrong SQL

[gr33d]

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)

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
};

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):

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

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

[AndreyR]

I've tried to reproduce the problem using the simplified join and failed. Could you please post here or send us (support * devart * com) the DDL script of the tables?