EF6 - not doing what I expected for .Any()

Question

I am stumped by EF 6 .... I have a web application which behaves very badly in terms of performance. While analysing, I found one of the culprits being a method of mine that checks whether a collection is empty (or not) on an EF6 entity.

Basically, I have:

public partial class BaseEntity
{
    public int BaseEntityId { get; set; }
    public string Name { get; set; }

    // a few more properties, of no concern here....

    // a lazily loaded collection of subitems        
    public virtual ICollection<Subitem> Subitems { get; set; }
}

public partial class Subitem
{
    public int SubitemId { get; set; }
    public int BaseEntityId { get; set; }
    public string Name { get; set; }

    // a few more properties, of no concern here....
}

In my app, I need to check whether or not a given instance of BaseEntity is "empty" - which is defined as having no subitems. So I added this method CheckIfEmpty to a second partial class file:

public partial class BaseEntity
{
    public bool IsEmpty 
    {
        return !Subitems.Any();
    }
}        

Now a single BaseEntity can have hundreds or thousands of subitems - so I wanted to use the most efficient way to check whether or not there were any subitems. My assumption was that calling .Any() on a collection that's not been loaded yet from the database would basically translate into a

IF EXISTS(SELECT * FROM dbo.Subitems) ......

SQL call - or something along those lines - just checking to see if any items existed - or not. I specifically picked .Any() over .Count > 0 because I know that checking for the count will need to enumerate the whole collection and thus is highly inefficient when I just want to know if (or not) any items exist.

I don't need to know how many exist, nor am I interested in their details - just a simple YES or NO to the is empty? question would suffice.

To my big astonishment (and bedazzlement), it turns out EF6 turns this simple .Any() call into a SELECT statement that loads the whole collection! - that's definitely NOT what I had bargained for......

So is there any easy way to simply check if a not-yet-loaded collection has any values - or not - WITHOUT loading the complete collection from the database??

Answer 1

By using an Eager loading approach and querying the DbSet, you get what you want:

context.Set<TEntity>().Any();

is translated into:

SELECT CASE WHEN EXISTS ( SELECT 1 FROM [TEntity] AS [m]) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END