Any Vs counter 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 the given employee identifier; exist in the table or not. I do not want the rows from the table to be true or false. I am using Entity Framework, not LINQ to Objects.

I read about People and Count and could not decide; which should i use? As shown above in my code, should I use the 1st or 2nd operator?

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

I researched a bit more and tried to get the time using the 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(); } 

When running on code, it shows that Count is faster. I am embarrassed.

Thoughts, please?

Answer 1

Short answer: stick with Any ().

You can safely ignore the message you are referring to, because it is not specific to the Entity Framework. When using LINQ over simple collections, yes, there may be other considerations. But when using LINQ to query a database, you want to avoid reusing additional database records unnecessarily.

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

And essentially, if your table is small or if the column you are looking for is correctly indexed and returns very few records, you can expect performance to be pretty similar to Any () and Count ().

But let's say you have a large table and your ExternalId column is not indexed, then you will inevitably notice that Any () is significantly superior to Count ().

Point: the best scenario (if your data model is optimized), both options can work in a similar way. In the worst case, Any () will completely outperform Count ().

There is never a scenario in which Count () will far outperform Any () if EF does not present a serious SQL generation error. Therefore, to be safe, I recommend you stick with Any ().

Answer 2

In this case

 .Any(p => p.ExternalId == "123"); .Count(p => p.ExternalId == "123") > 0; 

Anyway will work better anyway.

Personally, I would advise you to try efprof from hibernatingrhinos to get sql and timings done.

Answer 3

Count is already calculated, so you can get it faster.

Any () rewinds the entire list each time it is called.