ToLower produces highly inefficient SQL query that does not scale

[markti]

We have been using this for an internal AZ resiliency test and as an output of our testing and analysis we discovered that under load, this SQL query does not perform. You should avoid using .ToLower() in Entity Framework LINQ queries as it will manifest as the following:

SELECT TOP (2) [c].[Id], [c].[Email], [c].[Name], [c].[Phone] FROM [Customers] AS [c] WHERE LOWER([c].[Email]) = LOWER('foo@acme.com')

As you can see from the execution plan below there is a significant performance difference. We noticed this impact when we put the system under heavy load (~50k requests per hour) on an Azure SQL MI configured with Business Critical Standard-series (Gen 5) (128 GB, 8 vCores, Zone-redundant backup storage).

[KSchlobohm]

Thanks for the contribution @markti