Bad Performance Queries in MySql Entity Framework

[traufeisen]

Hi,
I´m currently testing the Entity Framework with dotConnect 5.40.37.0.
Running a simple Limit-Query against a Table, results in a Query like this:

Code: Select all

foreach(Account account in entities.Accounts.OrderBy("it.AccountId").Skip(2).Take(3))
{
	Console.WriteLine(account.AccountName);
}


SELECT
Project1.C1 AS C1,
Project1.AccountId AS AccountId,
Project1.AccountName AS AccountName
FROM ( SELECT
        Extent1.AccountId AS AccountId,
        Extent1.AccountName AS AccountName,
        1 AS C1
        FROM Test.Account AS Extent1
)  AS Project1
ORDER BY Project1.AccountId ASC
limit 2,3

The Table is queried with a non limiting Subquery, which results in a full table scan.
Is there a way to optimize this?
Regards

[AndreyR]

I have just tried a simple table select and the generated SQL was the correct one.
Then I wrote DefiningQuery for this table and have obtained the query similar to the one you have posted.
There is no possibility to optimize the query if you use DefiningQuery.
If it is not the case, please send me (support * devart * com) the script of your DB object and the generated model,
we will investigate the issue.