我正在开发一个从 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.0" xmlns:edmx="http://schemas.microsoft.com/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="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/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>
以下是我调用此函数的方式：
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)
};
但是当我执行以下任何操作时：
ObservableCollection<someType> weekTotaldata = new ObservableCollection<someType>(selectQuery); //Exception thrown at runtime
或者
foreach (var ttu in selectQuery) //Exception thrown at runtime
{
double testval = ttu.propertyB;
}
我得到该函数中引发的异常“ApplicationException”。这不应该是
当在其他地方调用此函数时会抛出异常 L2E 查询异常吗？
我做错了什么？如何从 Linq-To-Entities 调用 oracle 函数？请注意，我尝试调用的函数不是内置的 oracle 函数，而是用户定义的函数。
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="http://schemas.microsoft.com/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="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/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.
发布评论
评论（1）
据我使用 Oracle 处理 EF4 的工作，函数导入似乎在这里不起作用。几个月前我遇到了同样的问题，并尝试了多种方法来导入函数，但没有任何运气。但在搜索过程中，我在 OTN 上发现了一个链接，其中指出（不支持 Oracle 存储函数）。 EF4 还没有给我们调用 oracle 函数的选项。即使使用存储过程，您也需要选择返回引用游标的存储过程。支持的存储过程包括没有返回值但可能有 OUT 或 IN OUT 参数的过程和包方法。
这是链接
但是如果您使用的是 Sql 服务器了解如何在 EF4 中完成用户定义函数导入。以下是一些可能对您有帮助的链接：
Link1
Link2
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