Any Vs Count in Entity Framework

Question

bool isEmployeeFound;
DbContext DatabaseContext = new DbContext(DatabaseConnectionString);
using (DatabaseContext )
{
    isEmployeeFound= DatabaseContext .Persons.Any(p => p.ExternalId == "123"); -- 1st statement
    isEmployeeFound= DatabaseContext .Persons.Count(p => p.ExternalId == "123") > 0; --2nd statement
}

My requirement is to check only If a given employee Id; exist in a table or not. I do not want the rows from table just a true or false. I am using Entity Framework not LINQ to Objects.

I have been reading about Any and Count and kind of not able to decide; which one should I use? As shown above in my code should I use 1st or 2nd statement?

I read that using Any(It converts to Exists in SQL) is faster because as soon it satisfied the condition it stops iterating and returns the result whereas Count()(It converts to select Count(*) in SQL) iterates all and then return the result. However this post Which method performs better: .Any() vs .Count() > 0? says Count() is optimized for Linq to objects and it performs better than Any.

I did explore some more and tried getting times using code snippet below

using (var _dbContext = new DbContext())
{
    string caregiverId = "2301001";
    string clientPhoneNumber = "9795397674";

    Stopwatch stopwatch = new Stopwatch();
    stopwatch.Start();

    bool iscareGiverFoundWithAny = _dbContext.Persons.Any(p => p.ExternalId == caregiverId);
    bool isClientPhoneNumberFoundWithAny = _dbContext.PhoneNumbers.Any(ph => ph.Number == clientPhoneNumber);

    var testResult1 = stopwatch.Elapsed;
    stopwatch.Restart();

    bool iscareGiverFoundWithCountExt = _dbContext.Persons.Count(p => p.ExternalId == caregiverId) > 0;
    bool isClientPhoneNumberFoundWithCountExt = _dbContext.PhoneNumbers.Count(ph => ph.Number == clientPhoneNumber) > 0;

    var testResult2 = stopwatch.Elapsed;
    stopwatch.Stop();

    Console.WriteLine("Any " + testResult2.TotalSeconds); -- value for this line is coming as 0.0276239
    Console.WriteLine("Count Ext. " + testResult3.TotalSeconds); -- value for this line is coming as 0.0054292
    Console.ReadLine();
}

On running above code it shows Count is faster. I am confused.

Thoughts please?

Answer 1

The short answer: stick with Any().

You can safely ignore the post you are referencing, because it isn't specific to Entity Framework. When using LINQ on top of simple collections, then yes, there may be other considerations. But when using LINQ to query a database, then you want to avoid iterating through extra database records unnecessarily.

In this case, you say that Count() proved to be faster than Any(). But the difference you experienced is so minimal, at least in database performance terms, that you could say that you got equivalent performance in this case.

And in effect, if your table is small, or if the column you are searching on is properly indexed and returns very few records, then you can expect the performance to be fairly similar with both Any() and Count().

But let's say you have a big table, and your ExternalId column is not indexed, then you would inevitably notice Any() greatly outperform Count().

The point: Best case scenario (if your data model is optimized), both options may perform similarly. Worst case, Any() will absolutely outperform Count().

There is never a scenario where Count() will vastly outperform Any(), unless EF introduces a serious SQL generation bug. So to be safe, I recommend you stick with Any().