So after many hours of investigation, I've found a couple pieces of the puzzle that lead me to come up with this workaround.
- On a slightly different problem, I had to get NHibernate to use
Microsoft.Data.SqlClient (instead of System.Data.SqlClient), which is done like this (with FluentNhibernate)
- Poking around in the NHibernate source code, I found
DbCommandWrapper
- One SO post describes how setting
CommandBehavior.SequentialAccess speeds up the async read.
Between this and plugging in Microsoft.Data.SqlClient, I figured I'd extend MicrosoftDataSqlClientDriver, override the CreateCommand() method to return a wrapped DbCommand that manually sets CommandBehavior to SequentialAccess by overriding ExecuteDbDataReader(behavior). This didn't work; it throws an exception:
Attempt to read from column ordinal '21' is not valid. With CommandBehavior.SequentialAccess, you may only read from column ordinal '24' or greater.
So the data is being read out of order, causing issues.
- Another SO post describes EntityFramework Core experiences the same issue, and one of the answers caught my eye:.
A wrapper for DbDataReader that reads the columns in order and stores the data in a buffer.
So lets create a new SqlClient driver, wrap the created command so that we can wrap the reader, so that the reader can be executed with sequential access and read the data in order, and store it in buffer.
public class MicrosoftDataSqlClientSyncDriver : MicrosoftDataSqlClientDriver
{
public override DbCommand CreateCommand()
{
var cmd = base.CreateCommand();
return new DbCommandWrapper(cmd);
}
private class DbCommandWrapper : DbCommand
{
public readonly DbCommand Command;
public DbCommandWrapper(DbCommand cmd)
{
Command = cmd;
}
public override string CommandText { get => Command.CommandText; set => Command.CommandText = value; }
public override int CommandTimeout { get => Command.CommandTimeout; set => Command.CommandTimeout = value; }
public override CommandType CommandType { get => Command.CommandType; set => Command.CommandType = value; }
public override bool DesignTimeVisible { get => Command.DesignTimeVisible; set => Command.DesignTimeVisible = value; }
public override UpdateRowSource UpdatedRowSource { get => Command.UpdatedRowSource; set => Command.UpdatedRowSource = value; }
protected override DbConnection DbConnection { get => Command.Connection; set => Command.Connection = value; }
protected override DbParameterCollection DbParameterCollection => Command.Parameters;
protected override DbTransaction DbTransaction { get => Command.Transaction; set => Command.Transaction = value; }
public override void Cancel()
{
Command.Cancel();
}
public override int ExecuteNonQuery()
{
return Command.ExecuteNonQuery();
}
public override object ExecuteScalar()
{
return Command.ExecuteScalar();
}
public override void Prepare()
{
Command.Prepare();
}
protected override DbParameter CreateDbParameter()
{
return Command.CreateParameter();
}
protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
{
var reader = Command.ExecuteReader(CommandBehavior.SequentialAccess);
return new DbDataReaderWrapper(reader);
}
protected override void Dispose(bool disposing)
{
if (disposing)
Command.Dispose();
base.Dispose(disposing);
}
public override async ValueTask DisposeAsync()
{
await Command.DisposeAsync();
await base.DisposeAsync();
}
}
private class DbDataReaderWrapper : DbDataReader
{
readonly DbDataReader Reader;
private object[] Cache;
public DbDataReaderWrapper(DbDataReader reader)
{
Reader = reader;
Cache = new object[] { };
}
private T Get<T>(int ordinal)
{
if (Cache[ordinal] != DBNull.Value) return (T)Cache[ordinal];
return default(T);
}
public override object this[int ordinal] => Get<object>(ordinal);
public override object this[string name] => Get<object>(GetOrdinal(name));
public override int Depth => Reader.Depth;
public override int FieldCount => Reader.FieldCount;
public override bool HasRows => Reader.HasRows;
public override bool IsClosed => Reader.IsClosed;
public override int RecordsAffected => Reader.RecordsAffected;
public override int VisibleFieldCount => Reader.VisibleFieldCount;
public override bool GetBoolean(int ordinal) => Get<bool>(ordinal);
public override byte GetByte(int ordinal) => Get<byte>(ordinal);
public override long GetBytes(int ordinal, long dataOffset, byte[] buffer, int bufferOffset, int length) => throw new NotImplementedException();
public override char GetChar(int ordinal) => Get<char>(ordinal);
public override long GetChars(int ordinal, long dataOffset, char[] buffer, int bufferOffset, int length) => throw new NotImplementedException();
public override string GetDataTypeName(int ordinal) => Reader.GetDataTypeName(ordinal);
public override DateTime GetDateTime(int ordinal) => Get<DateTime>(ordinal);
public override decimal GetDecimal(int ordinal) => Get<decimal>(ordinal);
public override double GetDouble(int ordinal) => Get<double>(ordinal);
public override IEnumerator GetEnumerator() => Reader.GetEnumerator();
public override Type GetFieldType(int ordinal) => Reader.GetFieldType(ordinal);
public override float GetFloat(int ordinal) => Get<float>(ordinal);
public override Guid GetGuid(int ordinal) => Get<Guid>(ordinal);
public override short GetInt16(int ordinal) => Get<short>(ordinal);
public override int GetInt32(int ordinal) => Get<int>(ordinal);
public override long GetInt64(int ordinal) => Get<long>(ordinal);
public override string GetName(int ordinal) => Reader.GetName(ordinal);
public override int GetOrdinal(string name) => Reader.GetOrdinal(name);
public override string GetString(int ordinal) => Get<string>(ordinal);
public override object GetValue(int ordinal) => Get<object>(ordinal);
public override int GetValues(object[] values)
{
var min = Math.Min(Cache.Length, values.Length);
Array.Copy(Cache, values, min);
return min;
}
public override bool IsDBNull(int ordinal) => Convert.IsDBNull(Cache[ordinal]);
public override bool NextResult() => Reader.NextResult();
public override bool Read()
{
Array.Clear(Cache);
if (Reader.Read())
{
Cache = new object[Reader.FieldCount];
for (int i = 0; i < Reader.FieldCount; i++)
{
if (Reader.IsDBNull(i))
Cache[i] = DBNull.Value;
else
Cache[i] = Reader.GetValue(i);
}
return true;
}
return false;
}
public override void Close() => Reader.Close();
public override async Task CloseAsync() => await Reader.CloseAsync().ConfigureAwait(false);
public override DataTable GetSchemaTable() => Reader.GetSchemaTable();
public override async Task<DataTable> GetSchemaTableAsync(CancellationToken cancellationToken = default) => await Reader.GetSchemaTableAsync(cancellationToken).ConfigureAwait(false);
public override async Task<ReadOnlyCollection<DbColumn>> GetColumnSchemaAsync(CancellationToken cancellationToken = default) => await Reader.GetColumnSchemaAsync(cancellationToken).ConfigureAwait(false);
public override async Task<bool> NextResultAsync(CancellationToken cancellationToken) => await Reader.NextResultAsync(cancellationToken).ConfigureAwait(false);
public override async Task<bool> ReadAsync(CancellationToken cancellationToken)
{
Array.Clear(Cache);
if (await Reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
Cache = new object[FieldCount];
for (int i = 0; i < FieldCount; i++)
{
if (await Reader.IsDBNullAsync(i))
Cache[i] = DBNull.Value;
else
Cache[i] = Reader.GetValue(i);
}
return true;
}
return false;
}
protected override void Dispose(bool disposing)
{
Reader.Dispose();
base.Dispose(disposing);
}
public override async ValueTask DisposeAsync()
{
await Reader.DisposeAsync().ConfigureAwait(false);
await base.DisposeAsync();
}
}
}
So far, I've avoided the immense slowdown for the particular problem query, and I haven't run into any other issues. Hopefully this workaround is acceptable, at least until the .NET folks figure it all out on their end.
