如何调用Oracle函数，返回值使用LINQ to Entities？

本文介绍了如何调用Oracle函数，返回值使用LINQ to Entities？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我正在从一个访问Oracle 11g数据库的数据的应用程序。我使用的是EF4，使用LINQ访问数据。 我遇到了一个场景，我需要调用一个存储在一个包中的函数。此函数也具有返回值。我已经将该函数添加到实体数据模型，但无法执行添加功能导入。因此我无法使用LINQ访问它。 我如何调用这个函数也可以得到它的返回值？

我稍后再问这个问题，但还没有任何答案。无论如何，我正在更新一些细节，以便其他人了解问题，并指导我正确的方向。我试图实现这个问题，但是有一个例外。

我已将以下内容添加到我的实体数据模型的designer.cs文件中：

[EdmFunction（TestModel.Store，TestFunction）] public int TestFunction（decimal ALNR，decimal ATID，decimal AUKENR） { throw new ApplicationException （）; }

以下是edmx文件的一小部分：

< edmx：Edmx Version =2.0xmlns：edmx =schemas.microsoft/ado/2008/10/edmx > <！ - EF运行时内容 - > < edmx：运行时> <！ - SSDL内容 - > < edmx：StorageModels> < Schema Namespace =TestModel.StoreAlias =SelfProvider =Oracle.DataAccess.ClientProviderManifestToken =11gxmlns：store =schemas.microsoft/ado/ 2007/12 / edm / EntityStoreSchemaGeneratorxmlns =schemas.microsoft/ado/2009/02/edm/ssdl> < Function Name =TestFunctionReturnType =numberAggregate =falseBuiltIn =falseNiladicFunction =falseIsComposable =trueParameterTypeSemantics =AllowImplicitConversionStoreFunctionName =PKG_TURNUS.SUMUKE = SYSTEM > < Parameter Name =ATIDType =numberMode =In/> <参数名称=ALNRType =numberMode =In/> <参数名称=AUKENRType =numberMode =In/> < / Function>

这是我如何调用这个功能：

var selectQuery = from _ in _context.Table1 在T.columnA上的_context.Table2中加入A等于A.columnB 在_context.Table3中加入TU T.columnC等于TU.columnD 其中T.columnD == 5&& T.columnE == someVariable select new someType { propertyA = A.columnG， propertyB = _context.TestFunction（T.columnE，A.columnF，TU.columnH） };

但是当我执行以下任何操作之一：

的ObservableCollection< SOMETYPE> weekTotaldata = new ObservableCollection< someType>（selectQuery）; //运行时抛出异常

或

foreach（selectQuery中的var ttu）//在运行时抛出异常 { double testval = ttu.propertyB; }

我收到在该函数ApplicationException中抛出的异常。当这个函数被调用到其他任何地方的异常L2E查询时，不应该抛出这个异常？

我做错了什么？如何从Linq-To-Entities调用oracle函数？请注意，我尝试调用的函数不是内置的oracle函数，而是用户定义的函数。

解决方案

根据我在使用Oracle的EF4工作，功能导入在这里似乎不起作用。几个月前我面对同样的问题，尝试了许多方法来导入功能，但没有任何运气。但是在搜索过程中，我发现OTN上有一条链接，指出（Oracle Stored Functions不支持）。 EF4不给我们调用oracle功能的选项。即使使用存储过程，您将需要选择返回引用光标的存储过程。支持的存储过程包括没有返回值但可能有OUT或IN OUT参数的过程和包方法。

这是链接

但是您正在使用Sql server一些如何在EF4中完成用户定义的函数导入。以下是一些可能会帮助您的链接：

Link1

Link2

I'm working on an application which accesses data from an Oracle 11g database. I'm using EF4 and data is accessed using LINQ. I have come across a scenario where I need to call a function stored in a package. This function also has a return value. I have added this function to the entity data model but am unable to perform "Add Function Import" on it. Hence I'm unable to access it using LINQ. How can I call this function and also get it's return value?

I asked this question a while back, but have not got any answer for it yet. Anyways, I'm updating it with some details so that it becomes convenient for others to understand the issue and guide me in the right direction. I have tried to implement the solution proposed in this question, but am getting an exception.

I've added the following to the designer.cs file of my entity data model:

[EdmFunction("TestModel.Store", "TestFunction")] public int TestFunction(decimal ALNR, decimal ATID, decimal AUKENR) { throw new ApplicationException(); }

Following is a small portion of the edmx file:

<edmx:Edmx Version="2.0" xmlns:edmx="schemas.microsoft/ado/2008/10/edmx"> <!-- EF Runtime content --> <edmx:Runtime> <!-- SSDL content --> <edmx:StorageModels> <Schema Namespace="TestModel.Store" Alias="Self" Provider="Oracle.DataAccess.Client" ProviderManifestToken="11g" xmlns:store="schemas.microsoft/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="schemas.microsoft/ado/2009/02/edm/ssdl"> <Function Name="TestFunction" ReturnType="number" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" StoreFunctionName="PKG_TURNUS.SUMUKE" Schema="SYSTEM"> <Parameter Name="ATID" Type="number" Mode="In" /> <Parameter Name="ALNR" Type="number" Mode="In" /> <Parameter Name="AUKENR" Type="number" Mode="In" /> </Function>

Here is how I am calling this function:

var selectQuery = from T in _context.Table1 join A in _context.Table2 on T.columnA equals A.columnB join TU in _context.Table3 on T.columnC equals TU.columnD where T.columnD == 5 && T.columnE == someVariable select new someType { propertyA = A.columnG, propertyB = _context.TestFunction(T.columnE, A.columnF, TU.columnH) };

But when I do any of the following:

ObservableCollection<someType> weekTotaldata = new ObservableCollection<someType>(selectQuery); //Exception thrown at runtime

or

foreach (var ttu in selectQuery) //Exception thrown at runtime { double testval = ttu.propertyB; }

I get the exception that is thrown in that function "ApplicationException". Shouldn't this exception be thrown when this function is called anywhere else exception the L2E query?

What am I doing wrong? How can I call an oracle function from Linq-To-Entities? Please note that the function that I am trying to call is not a built-in oracle function, but a user-defined function.

解决方案

As far as i have worked on EF4 using Oracle , Function importing doesn't seems to work here. I faced the same problem few months back and tried many ways to import a function but without any luck. But during searching I found a link on OTN which states that (Oracle Stored Functions are not supported). EF4 doesn't give us the option to call oracle function yet. Even using Stored Procedures, you will need to select stored procedures that return a ref cursor. Supported Stored procedures include procedures and package methods that do not have a return value, but may have OUT or IN OUT parameters.

Here is the link

But if you are using Sql server some how you can accomplish the User Defined Function import in EF4. Here are some links that might helps you:

Link1

Link2