Entity query generates the wrong SQL

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
Posts: 11
Joined: Tue 05 Oct 2010 21:46

Entity query generates the wrong SQL

Post by 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
    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

@gp1 AS `C1`
#end order table
`Extent2`.`OrderID` AS `OrderID1`, 
`Extent2`.`ProfileID` AS `ProfileID1`, 
#end payment table
`Extent3`.`OrderID` AS `OrderID2`, 
`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

Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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?

Post Reply