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??

Calling `Subitems` loads all items even before `.Any` is reached, this is how lazy loading is implemented. The first time you access `Subitems` it is loaded. I try never to use lazy loading, but it does complicate my code: I tend to close the context as soon as possible, and open another another one for the `db.Subitem.Any(si=>si. BaseEntityId == ...)` — Kobi, Apr 24 '17 at 09:19
`SubItems` is an `ICollection`. It might work as you expect if it was an `IQueryable` (I'm not sure though, and I don't know if lazy loading can work with `IQueryable`) — Jakub Lortz, Apr 24 '17 at 09:19
By using an Eager loading approach and querying the DbSet, you get what you want: context.Set().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 — alessalessio, Apr 24 '17 at 09:22
The docs on msdn have some solution: https://msdn.microsoft.com/en-us/library/jj574232(v=vs.113).aspx#Anchor_3 — Jakub Lortz, Apr 24 '17 at 09:24
@alessalessio: thanks - never thought of "reversing" the checking this way - but you're 100% right - with this, EF behaves as I had expected and does a simply `IF EXISTS()` check and performs extremely well - thank you! — marc_s, Apr 24 '17 at 09:32
@JakubLortz: thanks for that link - great way to do it, too - and it performs really well, thank you! — marc_s, Apr 24 '17 at 09:33

score 5 · Accepted Answer · answered Apr 24 '17 at 09:33

5

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

answered Apr 24 '17 at 09:33

alessalessio

1,224
1
15
28

Thanks again - great answer. I've modified it slightly to use `context.Subitems.Any(si => si.BaseEntityId == 42);` and this also works very nicely. Great approach - thanks! – marc_s Apr 24 '17 at 09:52

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

1 Answers1