Understanding Performance Implications of First vs Single in EF Core with .NET 8 in SQL Server

This post explores the impact of choosing between First and Single methods in EF Core on performance when expecting a single result, emphasizing practical guidance for developers.

Understanding First and Single

To grasp their impact on performance, let's clarify the roles of First and Single in EF Core:

• First fetches the first element. It raises an exception if the sequence is empty.
• Single asserts that a sequence must contain precisely one element. It throws an exception if the sequence is empty or if more than one element is found.

These methods are foundational for querying data, with distinct behaviors that influence how we interact with database records.

Execution Plan

Both First and Single generate similar SQL queries under the hood with the difference in the number of records returned by the top expression.

First

context.MyEntities.First(x => x.ExternalId == externalId);

SELECT TOP(1) [m].[Id], [m].[ExternalId]
FROM [MyEntities] AS [m]
WHERE [m].[ExternalId] = @__externalId_0

Single

context.MyEntities.Single(x => x.ExternalId == externalId);

SELECT TOP(2) [m].[Id], [m].[ExternalId]
FROM [MyEntities] AS [m]
WHERE [m].[ExternalId] = @__externalId_0

Because the top operation is executed on the result of the where clause, the performance should be the same when expecting a single record.

Client-side Overhead

Suprisingly, EF Core uses System.Linq.Enumerable.Single with SingleQueryingEnumerable<T> for both First and Single operations when the results are read from the database.

public static TSource Single<TSource>(this IEnumerable<TSource> source)
{
    TSource? single = source.TryGetSingle(out bool found);
    if (!found)
    {
        ThrowHelper.ThrowNoElementsException();
    }

    return single!;
}

private static TSource? TryGetSingle<TSource>(this IEnumerable<TSource> source, out bool found)
{
    if (source is null)
    {
        ThrowHelper.ThrowArgumentNullException(ExceptionArgument.source);
    }

    if (source is IList<TSource> list)
    {
        switch (list.Count)
        {
            case 0:
                found = false;
                return default;
            case 1:
                found = true;
                return list[0];
        }
    }
    else
    {
        using (IEnumerator<TSource> e = source.GetEnumerator())
        {
            if (!e.MoveNext())
            {
                found = false;
                return default;
            }

            TSource result = e.Current;
            if (!e.MoveNext())
            {
                found = true;
                return result;
            }
        }
    }

    found = false;
    ThrowHelper.ThrowMoreThanOneElementException();
    return default;
}

This extension always verify for more than one element in the list but because EF Core generates a TOP(1) with IQueryable<TSource>.First, the MoveNext returns false and can't throw an exception in this case.

What are the performance implications of choosing one method over another?

To provide a clearer understanding, I've conducted a benchmark analysis comparing the methods within a real-world context. This involved the instantiation of a DbContext to maintain a clean and isolated state, explicitly avoiding the use of caching mechanisms.

[Benchmark]
public async Task<MyEntity> SingleByGuid()
{
    await using var context = CreateContext();
    return await context.MyEntities.SingleAsync(e => e.ExternalId == _randomExternalId);
}

[Benchmark(Baseline = true)]
public async Task<MyEntity> FirstByGuid()
{
    await using var context = CreateContext();
    return await context.MyEntities.FirstAsync(e => e.ExternalId == _randomExternalId);
}

The outcomes consistently show that the First method is slightly faster, indicating there may be a small overhead that has gone unnoticed in my analysis.

Conclusion

In practice, the performance difference between using First and Single when querying a column expected to be unique in SQL Server with EF Core is minimal. The choice between the two should be guided more by the semantics of your application's requirements rather than performance concerns. Single is more semantically appropriate for enforcing uniqueness, while First could be slightly faster in scenarios where every microsecond counts.