EF6 - do not do what I expected. Any()

Question

I am stumped by EF 6 .... I have a web application that behaves very poorly in terms of performance. In the analysis, I found that one of the culprits is my method, which checks whether the collection is empty (or not) for the EF6 object.

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 application, I need to check if this BaseEntity instance BaseEntity "empty", which is defined as having no subitems. Therefore, I added this CheckIfEmpty method to the second file of the partial class:

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

Now, one BaseEntity can have hundreds or thousands of sub-elements, so I wanted to use the most efficient way to check if there were any sub-elements. My suggestion was that calling .Any() on a collection not yet loaded from the database basically translates to

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

An SQL call - or something like that - just checking to see if any elements exist - or not. I specifically chose .Any() over .Count > 0 , because I know that the check for counting will have to list the entire collection and is therefore very inefficient when I just want to know if any elements exist (or not).

I do not need to know how many of them exist, and I am not interested in their details - is it enough to simply answer YES or NO to the question is empty? .

To my great amazement (and breaking), it turns out that EF6 turns this simple .Any() call into a SELECT that loads the whole collection! - this is definitely NOT what I was counting on ......

So, is there an easy way to just check if a collection that has not yet been loaded has any values ​​- or not - WITHOUT loading the entire collection from the database?

Answer 1

Using the Eager boot approach and querying DbSet, you get what you want:

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

converted to:

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