Execution Timeout Expired on Azure SQL Query with .NET 8

[chticer]

File a bug

Remember:

• Please check that the documentation does not explain the behavior you are seeing.
• Please search in both open and closed issues to check that your bug has not already been filed.

Include your code

This issue is related to the version of the project target framework and the location of the SQL server instance.

List<CurrentGameTVF> currentGameTVFResults = _IGDBAPIAppContext.CurrentGameTVF.FromSqlRaw("SELECT * FROM currentgame_tvf()").Where(idigdbgame => IGDBGameIDs.Contains(idigdbgame.IDIGDBGame)).ToList();

_IGDBAPIAppContext is configured to connect to a SQL server instance hosted on Azure.

When the query above executes on .NET 7 (Microsoft.EntityFrameworkCore 7.0.18), the query is able to execute successfully and within a few seconds. However, when the exact same query above executes on .NET 8 (Microsoft.EntityFrameworkCore 8.0.4), the query is not able to execute successfully and times out after 5 minutes.

I do have queries in other parts of the program that connects to a local SQL server instance database context and those queries are able to execute successfully in .NET 8. It just seems that the issue lies if the SQL server instance is hosted on Azure with .NET 8.

Include stack traces

An exception occurred while iterating over the results of a query for context type 'musicstreamingplayer.DatabaseModels.igdbapi.IGDBAPIAppContext'.
Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 ---> System.ComponentModel.Win32Exception (258): The wait operation timed out.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at Microsoft.Data.SqlClient.SqlDataReader.Read()
   at Microsoft.EntityFrameworkCore.Storage.RelationalDataReader.Read()
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
ClientConnectionId:d2f1ae4f-531d-47a7-90f3-97eca52f1501
Error Number:-2,State:0,Class:11

Include provider and version information

EF Core version: 8.0.4
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8
Operating system: Windows 11 23H2 22631.3447
IDE: Visual Studio 2022 17.9.6

[roji]

@chticer the timeout above has nothing really to do with EF; EF simply executes the raw SQL you gave it via SqlClient, which is responsible for contacting SQL Server etc. Timeouts are generally a result of a network issue or an environmental configuration problem, or could possibly be the result of SQL that's too heavy and takes too long to provide a response.

I suggest testing the same SQL from outside EF, and checking whether the problem is intemittent or reproducible. If you're convinced there's a non-environmental/network driver issue, you may want to take it up on the SqlClient repo.

[chticer]

Thanks, I found out this issue was already reported by other people here: dotnet/SqlClient#2400

[stevendarby]

Given the query has a Contains, my first thought was #32394

[stevendarby]

@chticer a workaround for the issue I mentioned is to tell EF your database has a compatibility level of 120, e.g.:

.UseSqlServer(@"<CONNECTION STRING>", o => o.UseCompatibilityLevel(120));

If doing this makes the query perform well again then we'll know it's that. Otherwise, maybe it's something else, but it didn't look exactly like that SqlClient issue to me.

[chticer]

I did test out the workaround and it is executing the query successfully. I do agree that this issue is related to #32394.

[roji]

@chticer the issue you first mentioned - dotnet/SqlClient#2400 - doesn't seem to be related to #32394. Are you saying that specifying .UseCompatibilityLevel(120) made the error go away? If so, this would be new to me - we haven't seen that particular query form affected so seriously by the changes covered by #32394. Can you please confirm, and if possible, post a minimal repro for that?

[ErikEJ]

Looks like there is both Open_json and a table valued function involved.

[chticer]

@chticer the issue you first mentioned - dotnet/SqlClient#2400 - doesn't seem to be related to #32394. Are you saying that specifying .UseCompatibilityLevel(120) made the error go away? If so, this would be new to me - we haven't seen that particular query form affected so seriously by the changes covered by #32394. Can you please confirm, and if possible, post a minimal repro for that?

Yes, I am confirming that using .UseCompatibilityLevel(120) fixed the error. Here's the code:

Global.cs

public class Global
{
    public static string GetIGDBAPIAppContextSQLConnectionString()
    {
        return new SqlConnectionStringBuilder
        {
            DataSource = "tcp:notificationprojects-secretply.database.windows.net,1433",
            InitialCatalog = "igdbapi",
            Authentication = SqlAuthenticationMethod.ActiveDirectoryDefault,
            CommandTimeout = (int) TimeSpan.FromMinutes(5).TotalSeconds
        }.ConnectionString;
    }
}

Program.cs

string _IGDBAPIAppContextSQLConnectionString = Global.GetIGDBAPIAppContextSQLConnectionString();

builder.Services.AddDbContext<IGDBAPIAppContext>(options => options.UseSqlServer(new SqlConnection(_IGDBAPIAppContextSQLConnectionString)));

/*
The above line does not work in .NET 8 but does work in .NET 7. The following line does work in .NET 8.

builder.Services.AddDbContext<IGDBAPIAppContext>(options => options.UseSqlServer(new SqlConnection(_IGDBAPIAppContextSQLConnectionString), o => o.UseCompatibilityLevel(120)));
*/

CurrentGameTVF.cs

public partial class CurrentGameTVF
{
    public long ID { get; set; }
    public long IDIGDBGame { get; set; }
    public string Name { get; set; }
    public long UpdatedAt { get; set; }
    public bool Active { get; set; }
    public DateTime TimestampAdded { get; set; }
}

Index.cshtml.cs

public class IndexModel : PageModel
{
    private readonly IGDBAPIAppContext _IGDBAPIAppContext;

    public IndexModel(IGDBAPIAppContext IGDBAPIAppContext)
    {
        _IGDBAPIAppContext = IGDBAPIAppContext;
    }

    public void OnGetStartup()
    {
        List<long> IGDBGameIDs = new List<long> { 1, 2, 3 };

        List<CurrentGameTVF> currentGameTVFResults = _IGDBAPIAppContext.CurrentGameTVF.FromSqlRaw("SELECT * FROM currentgame_tvf()").Where(idigdbgame => IGDBGameIDs.Contains(idigdbgame.IDIGDBGame)).ToList();
    }
}

[roji]

@chticer thanks, but is there a chance you can put together something I can actually run, and which shows the problem? The above uses a TVF which you don't supply here (currentgame_tvf()) - can you at the very least post that (and ideally also the schema of your table or the data that's in it)?

If I can see UseCompatibilityLevel(120) making a substantial positive difference for your specific scenario, that may play a part in a decision to move away from it in EF Core 9.

The above line does not work in .NET 8 but does work in .NET 7. The following line does work in .NET 8.

There shouldn't be a need for this in .NET 7, since the changes in question (switch to OPENJSON as the translation for Contains) were only introduced in .NET 8.

[chticer]

Here is the currentgames_tvf() query.

ALTER FUNCTION [dbo].[currentgame_tvf]()
RETURNS TABLE
AS RETURN
SELECT
	games_row_number.id,
	games_row_number.id_igdb_game,
	games_row_number.name,
	games_row_number.updated_at,
	games_row_number.active,
	games_row_number.timestamp_added
FROM
(
	SELECT
		id,
		id_igdb_game,
		name,
		updated_at,
		active,
		timestamp_added,
		ROW_NUMBER() OVER
		(
			PARTITION BY
				id_igdb_game
			ORDER BY
				updated_at DESC,
				timestamp_added DESC,
				id DESC
		) row_number
	FROM
		games
) games_row_number
WHERE
	games_row_number.row_number = 1;

Here is the games table schema:

This app is not deployed on Azure, as it's a personal project that is meant to run on my computer only, and it's not worth the effort for me to put something together. I would have to expose my Azure authentication information or create an App Service for anyone to run this.

[ErikEJ]

You need to share a text based CREATE TABLE statement.

[roji]

@chticer imagine that you need to recreate the test case on your end (this is what I need to do) - a screenshot doesn't really help there. Nobody is asking you to share Azure authentication information - just the information needed to reproduce the problem. Ideally, you'd submit a fully runnable console program, along with a SQL script which creates the table and seeds it with the appropriate data. Otherwise you're leaving us to guess at your schema and data.