I am using ASP.NET CORE 2.2 to test the api with the oracle 11.2 HR schema
I using API to get the Region list to display
However, I got error when display single region:
It is very strange error. How can the SQL command not properly ended.
my Region Controller is as following:
namespace Lsi.Api.Controllers
{
\[Route("api/region")\]
\[ApiController\]
public class RegionController : ControllerBase
{
private readonly OracleDbContext \_context;
public RegionController (OracleDbContext context)
{
\_context = context;
}
// GET: api/Region // **This method works fine**
\[HttpGet\]
public async Task\<ActionResult\<IEnumerable\<Region>>> GetRegions()
{
return await \_context.Regions.ToListAsync();
}
// GET: api/Region/1 // **This method does not work**
\[HttpGet("{id}", Name = "GetRegion")\]
public async Task\<ActionResult\<Region>> GetRegion(int id)
{
var region = await \_context.Regions.FindAsync(id);
if (region == null)
{
return NotFound();
}
return region;
}
}
The region Entity:
namespace Lsi.Entity
{
\[Table("REGIONS")\]
public class Region
{
\[Key, Column("REGION\_ID")\]
public int Id { get; set; }
\[Column("REGION\_NAME"), MaxLength(25)\]
public string RegionName { get; set;}
}
}
The DBContext:
public class OracleDbContext : DbContext
{
public OracleDbContext(DbContextOptions\<OracleDbContext> options) : base(options) { }
public DbSet\<Region> Regions { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasDefaultSchema("HR");
modelBuilder.Entity\<Region>().ToTable("REGIONS")
.HasKey(r => r.Id).HasName("REGION\_ID");
base.OnModelCreating(modelBuilder);
}
}
In the start up
I have
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext\<OracleDbContext>(options =>
{
options.UseOracle(Configuration.GetConnectionString("DefaultConnection"));
});
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version\_2\_2);
}
Where is my error? the query in the bold seem work fine whing set the value to 1 when copy and run in DBeaver which connected to Oracle Database.
Is this bug come from Oracle.EntityFrameworkCore?
The error message is as following:
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 2.2.6-servicing-10079 initialized 'OracleDbContext' using provider 'Oracle.EntityFrameworkCore' with options: None
fail: Microsoft.EntityFrameworkCore.Database.Command[0]
2019-08-23 00:26:22.926388 ThreadID:27 (ERROR) OracleRelationalCommand.ExecuteAsync() : Oracle.ManagedDataAccess.Client.OracleException (0x80004005): **ORA-00933: SQL command not properly ended**
at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64\[\] scnForExecution, Int64\[\]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable\`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary\`2 parameterValues, CancellationToken cancellationToken)
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (5ms) \[Parameters=\[:get\_Item\_0='?' (DbType = Int32)\], CommandType='Text', CommandTimeout='0'\]
**SELECT "e"."REGION\_ID", "e"."REGION\_NAME"**
**FROM "HR"."REGIONS" "e"**
**WHERE "e"."REGION\_ID" = :get\_Item\_0**
**FETCH FIRST 1 ROWS ONLY**
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00933: SQL command not properly ended
at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
fail: Microsoft.EntityFrameworkCore.Query[10100]
An exception occurred while iterating over the results of a query for context type 'Lsi.Data.OracleDbContext'.
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00933: SQL command not properly ended
at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64\[\] scnForExecution, Int64\[\]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable\`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary\`2 parameterValues, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable\`1.AsyncEnumerator.BufferlessMoveNext(DbContext \_, Boolean buffer, CancellationToken cancellationToken)
at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.ExecuteAsync\[TState,TResult\](TState state, Func\`4 operation, Func\`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable\`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
at System.Linq.AsyncEnumerable.FirstOrDefault\_\[TSource\](IAsyncEnumerable\`1 source, CancellationToken cancellationToken) in D:\\a\\1\\s\\Ix.NET\\Source\\System.Interactive.Async\\First.cs:line 144
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.TaskResultAsyncEnumerable\`1.Enumerator.MoveNext(CancellationToken cancellationToken)
at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator\`2.MoveNextCore(CancellationToken cancellationToken) in D:\\a\\1\\s\\Ix.NET\\Source\\System.Interactive.Async\\Select.cs:line 106
at System.Linq.AsyncEnumerable.AsyncIterator\`1.MoveNext(CancellationToken cancellationToken) in D:\\a\\1\\s\\Ix.NET\\Source\\System.Interactive.Async\\AsyncIterator.cs:line 98
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor\`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00933: SQL command not properly ended
at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)