Entity query generates the wrong SQL

Entity query generates the wrong SQL

Postby gr33d » 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)
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
gr33d
 
Posts: 11
Joined: Tue 05 Oct 2010 21:46

Postby AndreyR » Tue 16 Nov 2010 18:01

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?
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for MySQL