Reading binary data asynchronously hangs execution

[mdzieg]

Repro code can be found here: https://github.com/mdzieg/LBOTest/tree/main

Initialize database using SQL from readme. Update connection string in the code if needed.
Code inserts data file and then tries to load it.

When reading entity with binary data it hangs when using async method:

var att = await c.AttachmentData.FindAsync(1);

but works okay when using sync method:

var att = c.AttachmentData.Find(1);

This issue has its roots most likely in SqlClient library, although it was claimed fixed: #600

Include provider and version information

EF Core version: 8
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system: WIN 11
IDE: N/A

[ErikEJ]

@mdzieg Did you try 5.2 preview 4?

[mdzieg]

@ErikEJ added <PackageReference Include="Microsoft.Data.SqlClient" Version="5.2.0-preview4.23342.2" /> and tested, but no luck. It still hangs.

[ErikEJ]

How do you load the 66 MB file?

[mdzieg]

How do you load the 66 MB file?

This POC uses my app database and I have data already in the table. I did not add data loading code to the POC, but can do so.

edit: @ErikEJ updated POC code, now it loads data file;

[ErikEJ]

Thanks, duplicate of #593

[mdzieg]

Thanks, duplicate of #593

@ErikEJ It looks like a long issue originating in mid 2020. Is this something that is being fixed at the moment? You mentioned there "I have confirmed that the major improvement will be in 5.2 preview 4 and later" but using that lib version did not help in my case.

[ErikEJ]

Your use case is very large binary objects, and the improvements are better for smaller binary objects.

Read the issue to understand the challenges involved, I do not think this is a high priority at the moment.

IMHO saving 66 MB binary objects in a database is an anti pattern. Use a file system for that.

[mdzieg]

Your use case is very large binary objects, and the improvements are better for smaller binary objects.

Read the issue to understand the challenges involved, I do not think this is a high priority at the moment.

IMHO saving 66 MB binary objects in a database is an anti pattern. Use a file system for that.

Seems it will not be fixed any time soon then.

Saving large binary objects to the database in most cases is an anti-pattern, I agree.

I did not expect such a basic operation to hang our application. Now we are aware of the problem and will avoid async code with LBO.

[Wraith2]

Can you reproduce it without all the entity framework stuff? If you can get me a sql client only repro I'll take a look and see if there's something other than the known issues occurring here.

[ErikEJ]

@Wraith2 I took the repro code and added this after the call to CreateData:

using var connection = new SqlConnection(connectionString);
connection.Open();
using (var command = connection.CreateCommand())
{
    command.CommandText = "SELECT TOP(1) Id, Data FROM nattachment.attachment";
    using var reader = command.ExecuteReader();
    while (reader.Read())
    {
        var key = reader.GetFieldValue<int>(0);
        var data = reader.GetFieldValue<byte[]>(1);
        Console.WriteLine($"Key: {key}, Data: {data.Length}");
    }
}

using (var command = connection.CreateCommand())
{
    command.CommandText = "SELECT TOP(1) Id, Data FROM nattachment.attachment";
    using var reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        var key = await reader.GetFieldValueAsync<int>(0);
        var data = await reader.GetFieldValueAsync<byte[]>(1);
        Console.WriteLine($"Key: {key}, Data: {data.Length}");
    }
}

[kf-gonzalez]

Closing as it is a duplicate of #593