Empty enumerables mistakenly returned during database instability

[ajcvickers]

Originally filed as dotnet/ef6#1421 by @Zero3

Transferred here because this looks like an underlying issue with SqlClient and/or SQL Server returning empty results under load rather than failing.

---

Background

One of our servers running Microsoft SQL Server started suffering from a disk slowness issue caused by a SAN / hardware problem. The application that uses the database uses Entity Framework 6.2, and processed around 35 business transactions per second at the time. We thus observed a huge number of these exceptions thrown by Entity Framework, as expected:

• Win32Exception ("The wait operation timed out") wrapped in SqlException
• Win32Exception ("The wait operation timed out") wrapped in SqlException wrapped in UpdateException wrapped in DbUpdateException
• Win32Exception ("The wait operation timed out") wrapped in SqlException wrapped in CommitFailedException
• InvalidOperationException ("Invalid operation. The connection is closed.") wrapped in EntityCommandExecutionException

Problem

In some cases, however, Entity Framework did not throw exceptions as expected, but instead returned empty enumerables from queries that definitely should not have returned empty enumerables. This led to wrong business code being executed at various places in the system!

Here are a few examples of queries that started returning empty enumerables:

context.DeliveryMethods
	.Where(i => !i.DateDeleted.HasValue)
	.SingleOrDefault(i => i.Identifier == identifier);

context.Zones
	.Where(zone => !zone.DateDeleted.HasValue)
	.Where(zone => !onlyActiveZones || zone.Active)
	.Where(zone => zoneNumbers.Contains(zone.ZoneId))
	.ToDictionary(zone => zone.ZoneId, zone => zone);

Related notes:

• These entities represent static business data which could not possibly have been changed at the time.

• As soon as the underlying disk slowness issue was resolved, these queries started returning data as usual.

• Running DBCC CHECKDB on the database returns no errors:

  CHECKDB found 0 allocation errors and 0 consistency errors in database ''.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Further technical details

EF version: 6.2
Database Provider: EntityFramework.SqlServer
Microsoft SQL Server: Microsoft SQL Server 2016 (RTM-CU9-GDR) (KB4058559) - 13.0.2218.0 (X64) Dec 27 2017 17:10:01 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
Operating system: Windows Server 2016
IDE: Visual Studio 2019 16.x

[yukiwongky]

@ajcvickers To help us reproduce the issue, can you let us know what are the underlying SqlClient APIs being called from EntityFramework with the examples provided by the user?

[ajcvickers]

@yukiwongky Normal query APIs. For example, ExecuteReader.

[Zero3]

Thanks for redirecting the issue to the right place @ajcvickers.

On a related note, I peeked around the existing issues for this project, and noticed that this one sounds quite similar to my issue: #176

[yukiwongky]

@Zero3 Can you try this package out? Microsoft.Data.SqlClient.1.1.0-dev.zip
It contains the fix in PR #299.

[Zero3]

@yukiwongky thanks for fixing this! I'm afraid that my answer will be pretty much what @dmytro-gokun wrote at #176 (comment). I have no reliable way to test this, and I don't want to deploy a development build of this library to our production system, as it is a business critical application where we need to minimize risk.

[Zero3]

@yukiwongky @cheenamalhotra Thanks again for fixing this.

How would one obtain the fix when using Entity Framework 6 on .NET Framework? I am using the default System.Data.SqlClient, but this fix was (only?) applied to Microsoft.Data.SqlClient. I'm a bit confused about the System.Data.SqlClient / Microsoft.Data.SqlClient situation, so apologies in advance if this is a stupid question.

[cheenamalhotra]

cc @ajcvickers (for Entity Framework 6 on .NET Framework)

[ajcvickers]

@Zero3 We are considering supporting Microsoft.Data.SqlClient on EF6--see dotnet/ef6#823