Can't use CommandBehavior.SequentialAccess because of Primary Keys

[panoskj]

I tried to use CommandBehavior.SequentialAccess for async operations, because the default behavior is significantly slower when there are varchar(max) columns (see dotnet/SqlClient#593 for details).

So I did this by adding the following interceptor:

public class EFInterceptor : DbCommandInterceptor
{
    public async override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
    {
        var behavior = System.Data.CommandBehavior.SequentialAccess;

        var reader = await command.ExecuteReaderAsync(behavior, cancellationToken).ConfigureAwait(false);

        return InterceptionResult<DbDataReader>.SuppressWithResult(reader);
    }
}

My entity looks like this:

public class Entity
{
    public int Id { get; set; } // this is the primary key
    public string Value { get; set; } // this is varchar(max) in database, about 15K characters long
}

And the code I run looks like this:

var list = dbContext.Entities.ToListAsync();

It turns out EF reads the first column twice, because it is the primary key. And this results in an exception inside SqlDataReader, because it doesn't allow reading the same column twice with SequentialAccess.

I know this because, when I added HasNoKey() for the entity, everything worked without errors, even matching the performance of the sync version.

It looks like a great opportunity for optimization and I don't understand why the "shaper" generated by EF cannot read data sequentially. Is it possible to fix this?

EF Core version: 5.0.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 5.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.8.6

[ajcvickers]

@panoskj SequentialAccess is tracked by issue #885. Please upvote that issue.

[ErikEJ]

@Wraith2 FYI