Asp.Net Core Api project Oracle.EntityFrameworkCore error?

user8203173 · August 2019

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'.