LINQ Query with Any() Operator should maybe use EXISTS instead of TOP 1 (using SQL Server)

[sajid-sd]

Session.Query<TEntity>().Any() should generate SQL with EXIST statement
SELECT ( CASE WHEN EXISTS ( SELECT NULL FROM TABLE T ) THEN 1 ELSE 0END ) AS [Value]

Instead of fetching the full first row in memory with
SELECT TOP(1) COLUMN1...COLUMN(N) From TABLE T

[hazzik]

It does. What exact issue do you have? What it the query and what is the sql generated? What version of NHibernate?

[sajid-sd]

@hazzik following are the details
NHibernate 5.3.5
FluentNHibernate 3.10
.NET 6.0

UserClassMap

public class UserClassMap : ClassMap<UserDbo>
{
    public UserClassMap()
    {
	Table("User");
	Id(m => m.UserId).Column("UserId");
        Map(m => m.FirstName).Column("FirstName")
	Map(m => m.LastName).Column("LastName")
   }
}

The code snipped is as follows.

var session =  _sessionFactory.OpenSession();
bool doesAnyUserExist = session.Query<User>().Any();

SQL query

select TOP (1) environmen0_.Id as id1_52_, environmen0_.FirstName as firstname2_52_, environmen0_.LastName as lastname3_52_
from [User] environmen0_

[gliljas]

For "naked" Any:s it does that. Using EXISTS is not necessarily the solution, since some kind of value would have to be returned. In SQL it would perhaps have to be SELECT CASE WHEN EXISTS....

It is, as you say, unnecessary that all columns are fetched. I've fixed that in a project of mine, with an expression rewriter that prepends a "Select(x=>x.Id)" to naked Any:s. I guess it could even be "Select(x=>1)". Maybe such a rewrite could be suitable for the core.

[sajid-sd]

@gliljas thanks for the suggestion. Yes, with expression rewrite we can reduce the unnecessary columns. Though IMHO it still fetches some data to some extent "Select TOP (1) Id from Table T ".

@hazzik do you have a thing in your mind other than the @gliljas mentioned

[gliljas]

Though IMHO it still fetches some data to some extent

If the Any returns true, yes. Using an EXISTS would have to fetch/return data in both cases. For SQL Server, if the projected property Select(x=>x.Id) is the clustered key , no lookups will be needed. Unfortunately Select(x=>1) doesn't work, which could be considered a bug. I'll have a look at that.

[dplaskon]

Personally, I wish it just selected the id field for the entity, that would likely always be more performant. But I'd imagine there's reasons why it's not implemented as such.