Asp.Net Core Api project Oracle.EntityFrameworkCore error? — oracle-tech

    Forum Stats

  • 3,715,654 Users
  • 2,242,820 Discussions
  • 7,845,479 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Asp.Net Core Api project Oracle.EntityFrameworkCore error?

user8203173
user8203173 Member Posts: 2
edited August 2019 in ODP.NET

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

lsiOracle01.PNG

However, I got error when display single region:

lsiOracle02.PNG

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)

Best Answer

Answers

Sign In or Register to comment.