Hi,
managed to change our solution from sql server to oracle and can now read oracle database table
Using https://docs.microsoft.com/en-us/aspnet/core/tutorials/razor-pages/?view=aspnetcore-2.2
when select delete or edit details get command ORA-00933: SQL command not properly ended
Any thoughts on how to resolve? looks like ef core generating FETCH FIRST 1 ROWS ONLY on sql for some reason - how can this be stopped?
SELECT 'm'.'MULT_MULTIPLE_CODE', 'm'.'MULT_ANMW_MULTIPLE_CODE', 'm'.'MULT_AUTH_REQUIRED', 'm'.'MULT_JMW_MULTIPLE_TYPE_CODE', 'm'.'MULT_LINK_MULTIPLE_CODE', 'm'.'MULT_NAME', 'm'.'MULT_PROM_AUTH_REQUIRED', 'm'.'MULT_SELECTED_MULTIPLE'
FROM 'MULTIPLE' 'm'
WHERE 'm'.'MULT_MULTIPLE_CODE' = :id_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)
Have got the Create working.
It looks like the Edit and Delete have the
FETCH FIRST 1 ROWS ONLY extra bit added which only available from 12g onwards by look of it.
Can the sql not be generated without FETCH FIRST 1 ROWS ONLY as this looks superfluous to me as the equality predicate in our case is enough as it is a primary key.?
Thanks
I got error when try to create ADO.NET Entity Data Model from existing Oracle 11g database
Server Explorer Image.
My connection string worked on server explorer but when I try to create ADO.NET Entity Data Model. I downloaded ODAC 12c Release 4 and Oracle Developer Tools for Visual Studio (12.1.0.2.4) (32 bit) and Oracle.ManagedDataAccess( 64bit) from nuget.
I am using Visual Studio 2015, Entity Framework 6, Oracle 11g, Windown 10 pro 64 bit
I added some config to marc_s
web.config but it doesn't work.
595k135135 gold badges11391139 silver badges12801280 bronze badges
nguyenba nguyen nguyennguyenba nguyen nguyen
1 Answer
Found The way to get it work, first you need to install entityframework 6 from nuget and then change the web.config file :
But you need to add Oracle.ManagedDataAccess.EntityFramework.DLL , get it from
ODAC 12c Release 4 and Oracle Developer Tools for Visual Studio (12.1.0.2.4) odac 12 RC4 FOR VISUAL 2015, EF 6 my location for that file is D:appclientUnknowproduct12.1.0client_1odp.netmanagedcommonEF6 where D is harddisk and Unknow is your pc name.In the end it is install ODAC 12c r4,EF6, change config file like the code and add Oracle.ManagedDataAccess.EntityFramework.DLL to reference so you can use DB first with Oracle db like a boss :D
nguyenba nguyen nguyennguyenba nguyen nguyen
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Not the answer you're looking for? Browse other questions tagged c#.netoracleentity-frameworkef-database-first or ask your own question.-->OverviewPurpose
This tutorial covers how to use Entity Framework 5 or 6, Language Integrated Query (LINQ), and generate Data Definition Language (DDL) scripts using Model-First for Oracle database.
Time to Complete
Approximately 30 mins.
Introduction
Microsoft Entity Framework abstracts the relational, logical database schema and presents a conceptual schema to the .NET application. It provides object-relational mapping for .NET developers.
LINQ is a .NET data querying language which can query a multitude of data sources, one of which are entities via LINQ to Entities.
Model-First allows the conceptual model to be created first by the developer. Next, Visual Studio can create Oracle DDL scripts to generate the relational database model based on the conceptual model.
In this tutorial, you will learn how to use Oracle Database and these three technologies with Microsoft Visual Studio and Oracle Data Provider for .NET (ODP.NET). You will start by creating an Entity Data Model (EDM) from an existing Oracle Database schema using Entity Data Model Wizard. Then, you will query the EDM using three different methods:
Next, you will call a stored procedure added to the EDM via a function import. The stored procedure will modify the database data and return the results with an implicit resultset. To return the resultset, you will run a Oracle wizard that provides the resultset parameter information in a .NET config file. You will then see how to run a stored procedure returning a scalar parameter value. Then, you will see how to insert and delete data to the database without using a stored procedure.
Lastly, you will use Model-First with Oracle database. In a Model-First scenario, a developer first creates an Entity Framework object-relational data model. From that data model, the developer can automatically generate an Oracle relational database model in the form of DDL scripts.
Prerequisites
Before starting this tutorial, you should:
Note: If you have installed ODAC 12c Release 3 configured on a machine-wide level, you will see an error when trying to generate database scripts using Entity Framework Model-First. To resolve this issue, use an ODAC version later than this release or reinstall ODAC 12c Release 3 with the 'Configure ODP.NET at a machine-wide level' box UNchecked.
Creating a new Project in Visual Studio
Creating an Oracle Connection
Before creating the Entity Data Model, you need an Oracle database connection that uses ODP.NET. To create an Oracle Connection:
Creating an Entity Data Model using the Entity Data Model Wizard
You will now create an Entity Data Model based on HR's DEPARTMENTS and EMPLOYEES table and the three new stored procedures that you just added. To do so, you will add an EDM to our project. The EDM will be automatically generated using the Entity Data Model Wizard.
Note: If you wish to use the Entity Framework 6 version, perform the following steps first:
Entity Framework Data Retrieval
There are a number of ways to query the EDM, which then retrieves data from the database. This section will demonstrate three common EDM querying methods: LINQ, LINQ with lambda expressions, and Entity SQL.
Executing a LINQ query
To execute a LINQ query against an Oracle database, you will create the code to execute a LINQ query and return the results to the console window.
Executing a LINQ query using a lambda expression and Entity Framework stored procedure mapping
LINQ queries can include lambda expressions. Lambdas are used as LINQ arguments to standard query operator methods.
Updates, inserts, and deletes can be executed on an entity by mapping Oracle stored procedures to these operations in the EDM.
In the below mentioned steps, you will execute a LINQ query with a lambda expression against the EMPLOYEE entity, then map a stored procedure to execute an update against all rows selected. You will use one of the stored procedures you imported earlier into the EDM.
Executing an Entity SQL query
In this section, you will query the same rows as in the previous section to show that the stored procedure successfully updated the selected rows. You will use another query method available in Entity Framework called Entity SQL.
Function Imports and Retrieving Implicit Resultsets | Inserting and Updating Data Directly
Entity Framework allows developers to define their own .NET methods to execute database stored procedures. This is done by creating function imports in the conceptual model that map to stored procedures. In this section, you will define your own method that is mapped to an Oracle stored procedure, which performs an update. The stored procedure will also return the modified data using an implicit resultset. The resultset metadata is defined by the developer in a .NET config file so that Entity Framework can properly retrieve the data. Oracle has introduced a wizard to automatically generate the resultset metadata.
This section shows you how to modify Oracle database data programmatically in Entity Framework. You will perform an insert and a delete on the DEPARTMENTS table.
Retrieving Output Parameters from Stored Procedures
This section demonstrates how to retrieve output parameters from stored procedures within Entity Framework. The output parameter needs only to be declared explicitly in .NET and map to an output parameter in the stored procedure. This is different from older Entity Framework versions when a function import was required for stored procedure output parameters.
Insert and Delete Data Using LINQ
This section demonstrates how LINQ can be used to insert and delete data programmatically.
Model-First
In this section, you will add a new property to the EMPLOYEE entity. To reflect the new property in the Oracle database schema as a column, Visual Studio will generate Oracle DDL scripts from the new EDM. These scripts can then be run against the Oracle database to update the relational model.
SummaryDevart Entity Framework Oracle Connection String
In this tutorial, you have learned how to:
Resources
Credits
To navigate this Oracle by Example tutorial, note the following:
To navigate to a particular section in this tutorial, select the topic from the list.
Before You BeginPurpose
This tutorial shows how to use Entity Framework 6, Language Integrated Query (LINQ), and generate Data Definition Language (DDL) scripts using Model-First for Oracle database.
Time to Complete
Approximately 60 minutes.
BackgroundMicrosoft Entity Framework abstracts the relational, logical database schema and presents a conceptual schema to the .NET application. It provides object-relational mapping for .NET developers.Entity Framework Oracle ConnectionLINQ is a .NET data querying language which can query a multitude of data sources, one of which is entities via LINQ to Entities.Model-First allows the conceptual model to be created first by the developer. Next, Visual Studio can create Oracle DDL scripts to generate the relational database model based on the conceptual model. In this tutorial, you will learn how to use Oracle Database and these three technologies with Microsoft Visual Studio and Oracle Data Provider for .NET (ODP.NET). You will start by creating an Entity Data Model (EDM) from an existing Oracle Database schema using Entity Data Model Wizard. Then, you will query the EDM using three different methods:
Lastly, you will use Model-First with Oracle database. In a Model-First scenario, a developer first creates an Entity Framework object-relational data model. From that data model, the developer can automatically generate an Oracle relational database model in the form of DDL scripts. What Do You Need?
Entity Framework Oracle Connection String SchemaCreating a new Project in Visual Studio
Create a new .NET console application to run Entity Framework and LINQ application.
Creating an Oracle Connection
For creating the Entity Data Model (EDM), an Oracle database connection that uses ODP.NET is required. To create an Oracle Connection perform the below steps.
Creating stored procedures
You will now create the stored procedures that will be used later in this lab to perform updates and data retrieval. The source code of three stored procedures , fINCREASE_SALARY_BY_10, fUPDATE_AND_RETURN_SALARY and OUTPARAM is provided to you in the files.zip file.
Creating an Entity Data Model using the Entity Data Model Wizard
You will now create an Entity Data Model (EDM) based on DEPARTMENTS table and EMPLOYEES table and the three new stored procedures that you just added in the HR schema. To do so, you will add an EDM to our project. The EDM will be automatically generated using the Entity Data Model Wizard.
Entity Framework Data Retrieval
There are a number of ways to query the EDM, which then retrieves data from the database. This section will demonstrate three common EDM querying methods: LINQ, LINQ with lambda expressions, and Entity SQL. Source code is provided in the Programcs.txt in the files.zip file.
Executing a LINQ query
To execute a LINQ query against an Oracle database, you will create the code to execute a LINQ query and return the results to the console window.
Executing a LINQ query using a lambda expression and Entity Framework stored procedure mapping
LINQ queries can include lambda expressions. Lambdas are used as LINQ arguments to standard query operator methods.
Updates, inserts, and deletes can be executed on an entity by mapping Oracle stored procedures to these operations in the EDM. In the below mentioned steps, you will execute a LINQ query with a lambda expression against the EMPLOYEE entity, and then map a stored procedure to execute an update against all rows selected. You will use one of the stored procedures you imported earlier into the EDM. First, you will create a stored procedure mapping for updating the data. When .NET tries to update the data, the mapped stored procedure will execute for every row selected by LINQ.
Executing an Entity SQL query
In this section, you will query the same rows as in the previous section to show that the stored procedure successfully updated the selected rows. You will use another query method available in Entity Framework called Entity SQL.
Function Imports and Retrieving Implicit Resultsets | Inserting and Updating Data Directly
Entity Framework allows developers to define their own .NET methods to execute database stored procedures. This is done by creating function imports in the conceptual model that map to stored procedures. In this section, you will define your own method that is mapped to an Oracle stored procedure, which performs an update. The stored procedure will also return the modified data using an implicit resultset. The resultset metadata is defined by the developer in a .NET config file so that Entity Framework can properly retrieve the data. Oracle has introduced a wizard to automatically generate the resultset metadata.
This section shows you how to modify Oracle database data programmatically in Entity Framework. You will perform an insert and a delete on the DEPARTMENTS table. You will use Visual Studio's Function Import tool to map the stored procedure to a user-defined .NET method. Since the stored procedure returns an implicit resultset, you need to use a wizard to generate the resultset metadata in the .NET config file before using the tool.
Retrieving Output Parameters from Stored Procedures
This section demonstrates how to retrieve output parameters from stored procedures within Entity Framework. The output parameter needs only to be declared explicitly in .NET and map to an output parameter in the stored procedure. This is different from older Entity Framework versions when a function import was required for stored procedure output parameters.
Insert and Delete Data Using LINQ
This section demonstrates how LINQ can be used to insert and delete data programmatically.
Model-First
In this section, you will add a new property to the EMPLOYEE entity. To reflect the new property in the Oracle database schema as a column, Visual Studio will generate Oracle DDL scripts from the new EDM. These scripts can then be run against the Oracle database to update the relational model.
Want to Learn More?Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |