When querying an oracle view that has a CLOB which stores JSON using a connectstring with direct=true is much slower then without direct=true.
<PackageReference Include="Devart.Data.Oracle.EFCore" Version="10.1.134.7" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.5" />
var ctx = new AppDbContext();
using (var command = ctx.Database.GetOracleConnection().CreateCommand())
{
command.InitialLobFetchSize = -1;
command.CommandText = $"SELECT * FROM MYORAVIEW a WHERE JSON_EXISTS(FIELD1,'$.CODE?(@==\"MYTEXT\")')";
command.CommandType = CommandType.Text;
ctx.Database.OpenConnection();
using (var result = command.ExecuteReader())
{
var entities = new List<string>();
Stopwatch sw = new Stopwatch();
sw.Start();
while (result.Read())
{
entities.Add(result.GetString(0));
}
sw.Stop();
Console.WriteLine(sw.Elapsed);
}
ctx.Database.CloseConnection();
Console.ReadLine();
}
public partial class AppDbContext : DbContext
{
public AppDbContext()
{
}
public AppDbContext(DbContextOptions<AppDbContext> options)
: base(options)
{
}
public Task<IDbContextTransaction> BeginTransactionAsync(CancellationToken ct) { return Database.BeginTransactionAsync(ct); }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.UseOracle("OMITTED CONNECTIONSTRING");
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
OnModelCreatingPartial(modelBuilder);
}
partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}