Is there any difference in performance between these two LINQ to SQL queries?

c# entity-framework-core linq

Question

I've been recently looking into my web app performance and found some LINQ queries that I'm not sure if changing them would improve performance.

Basically the current code looks like:

var result = _carsRepository.GetAll()
                 .Where(x => x.Name == input.Name)
                 .FirstOrDefault();
if (result != null)
{
    throw new Exception("test");
}

I'm thinking of changing it to:

    var result = _carsRepository.GetAll()
                     .Where(x => x.Name == input.Name)
                     .Any();
if (result)
{
    throw new Exception("test");
}

In my understanding the first query would return an actual entity, which I don't need because I only want to know whether the record with the same name already exists in the database. The second query returns just a bool.

I'd be thankful for any comments.

EDIT: I could be running the query on EF db context, so please ignore that. The current repository is a generic Abp.Domian.Repository. GetAll() returns IQueryable

1
0
9/27/2018 8:43:44 AM

Accepted Answer

I'm not sure what GetAll() does. If it moves all items from your database to your local memory, then I wouldn't bother: try to improve that statement. Why fetch all items if you only need the first one.

If GetAll() returns an IQueryable<...>, then there is a slight difference:

FirstOrDefault() will change the Expression in the query, such that the SQL statement will be Select top 1 ... from.

After changing the expression it will ask the Provider of the IQqueryable to execute the Expression, the complete result of the SQL statement will b transferred to local memory, which in this case will be one item.

Any() will almost do the same, except that the SQL will be: Select top 1 1 from ...

It is easy to see that a Select top 1 1 will at utmost transfer one integer, while Select top 1 will transfer all selected columns.

Hence, if you only want to check if there are any elements, Any() is more efficient then FirstOrDefault

3
9/27/2018 1:09:44 PM

Popular Answer

There could be a slight difference because of

.FirstOrDefault() - Reads all the columns

.Any() - Just check if there are entries

Difference will be mostly based on data size and SQL structure, indexes and all. Recommend to benchmark them by testing



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow