Inefficient MySQL being generated

[larrydhunt]

I am using MySQL 5.0.51b-community-nt on Windows XP and Entity Developer for dotConnect 1.0.53.

The following two Linq statements are generating very different MySQL commands. The first statement is generating straight forward SQL. However, the second Linq command (which only has an additional Order By statement) is creating a query with a subquery. This query / subquery is very inefficient since the subquery is sorting all of the rows in the table before the where clause is applied.

Linq statement 1:
Dim query1 = From tbl_Order In tbl_Orders _
Where tbl_Order.JobPromiseDate.Value <= Today _
Select tbl_Order.OrderID, tbl_Order.JobPromiseDate, tbl_Order.JobPromiseTime
Dim anArray1 As Array = query1.ToArray

MySQL command 1:
SELECT t1.`OrderID`, t1.`JobPromiseDate`, t1.`JobPromiseTime`
FROM tbl_order t1
WHERE (t1.`JobPromiseDate`) <= '2009-01-23 00:00:00'

Linq statement 2:
Dim query2 = From tbl_Order In tbl_Orders _
Order By tbl_Order.JobPromiseDate, tbl_Order.JobPromiseTime _
Where tbl_Order.JobPromiseDate.Value <= Today _
Select tbl_Order.OrderID, tbl_Order.JobPromiseDate, tbl_Order.JobPromiseTime
Dim anArray2 As Array = query2.ToArray

MySQL command 2:
SELECT t1.`OrderID`, t1.`JobPromiseDate`, t1.`JobPromiseTime`
FROM (
SELECT t2.`OrderID`, t2.`JobPromiseDate`, t2.`JobPromiseTime`
FROM tbl_order t2
ORDER BY t2.`JobPromiseDate`, t2.`JobPromiseTime`
) t1
WHERE (t1.`JobPromiseDate`) <= '2009-01-23 00:00:00'

What is causing this problem? Is there a good work around (besides not using the Order By statement)?

Thanks,
Larry

[AndreyR]

Thank you for the report. We will investigate this issue.
I will let you know as soon as any results are available.

[AndreyR]

We have fixed this problem. Look forward to the next build.

[larrydhunt]

Great news about the fix. Thanks!

How often are new builds available? Once a week, month, quarter?

Larry

[AndreyR]

The build will be available in two weeks.