LINQ Any() statement generates invalid SQL

[caleblloyd]

Steps to reproduce

Models:

public class Blog
{
    public int BlogId { get; set; }
    public string Title { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

The issue

LINQ queries using .Any() are causing an incorrect SQL EXISTS query to be executed:

db.Blogs.Include(m => m.Posts).OrderByDescending(m => m.Id).Skip(5).Take(10).Any()
//translates to SQL:
SELECT CASE WHEN EXISTS ( SELECT 1 FROM `Blogs` AS `m`) THEN TRUE ELSE FALSE END LIMIT 10 OFFSET 5

I'd expect a correct EXISTS statement to look more like the query in the COUNT statement:

db.Blogs.Include(m => m.Posts).OrderByDescending(m => m.Id).Skip(5).Take(10).Count()
//translates to SQL:
SELECT COUNT(*)
FROM (
    SELECT `m`.`Id`, `m`.`Title`
    FROM `Blogs` AS `m`
    ORDER BY `m`.`Id` DESC
    LIMIT 10 OFFSET 5
) AS `t`

Therefore the correct EXISTS statement should be something like:

SELECT CASE WHEN EXISTS (
    SELECT 1
    FROM `Blogs` AS `m`
    ORDER BY `m`.`Id` DESC
    LIMIT 10 OFFSET 5
) THEN TRUE ELSE FALSE END

Further technical details

EF Core version: 1.0.1
Operating system: Ubuntu 16.04

I am using the Pomelo.EntityFramework.MySql driver (I am a contributor to the project). I believe this issue is present in the Microsoft.EntityFrameworkCore.Relational package though and may affect all drivers.

[smitpatel]

Skip/Take should not be lifted from the subquery.

[caleblloyd]

@smitpatel neither should OrderBy. The whole query needs to remain in-tact

[smitpatel]

@caleblloyd - Would the result be any different in absence/presence of OrderBy? Exists would return true as long as there are 6 records in Blogs table. Ordering really doesn't matter.
Queries:

EXISTS (
    SELECT 1
    FROM `Blogs` AS `m`
    ORDER BY `m`.`Id` DESC
    LIMIT 10 OFFSET 5
)

EXISTS (
    SELECT 1
    FROM `Blogs` AS `m`
    LIMIT 10 OFFSET 5
)

[caleblloyd]

@smitpatel it doesn't matter in this case, but I imagine if this query were to end up nested into another query (but the ORDER BY stays on the outer query) it might. If this query will never be nested, then order by seems to not matter.

[caleblloyd]

@smitpatel additionally, if a caller calls .Any() to check existence of a big record set, then calls .ToArray() if it does exist, there's a chance that the DBMS may be able to use a cached query.

By removing the ORDER BY in the EXISTS, the DBMS has no hope of caching the query.

[maumar]

@caleblloyd we got a lot of feedback that people like smaller/more elegant queries. We should not be making them more complicated than necessary to satisfy fringe scenarios like accidentally being able to reuse same cache entry for two different queries.

[caleblloyd]

@maumar why not submit the query in the form that the caller requests? If they hand EF an ORDER BY in an EXISTS it's probably for a reason.

The magical query reduction is what is causing bugs like these in the first place.

[yukozh]

MyCAT(A distributed MySQL cluster middleware) does not return records in order, so I suppose this should be fixed.

[caleblloyd]

@rowanmiller can this get a higher priority? Incorrect generated SQL seems like it should be fixed sooner than later

If the issue is programmer burden, I'm happy to take a shot at a PR

[rowanmiller]

It's in the set of bugs allocated to be fixed in our next release (1.1 has pretty much locked now, so it will be 1.2).

@anpete could comment on how feasible this would be as a contribution

[anpete]

Sending back to triage to discuss where to put the fix.