如何从Entity Framework 4中的LINQ表达式中执行Oracle函数？ [英] How can I execute an Oracle function from within a LINQ expression in Entity Framework 4?
问题描述
我需要能够将数据库函数的结果作为标量返回属性的匿名类型。
我的Oracle架构有两个表，PARENT和CHILD，以及一个函数FNC_ADD。我已经使用Visual Studio ADO.NET实体数据模型模板创建了一个实体模型，包括两个表和函数。
我的.edmx文件的StorageModels部分看起来像这个：
<！ - SSDL内容 - >
< edmx：StorageModels>
< Schema Namespace =LINQtest2Model.StoreAlias =SelfProvider =Devart.Data.OracleProviderManifestToken =ORAxmlns：store =http://schemas.microsoft.com/ado/ 2007/12 / edm / EntityStoreSchemaGeneratorxmlns =http://schemas.microsoft.com/ado/2009/02/edm/ssdl>
< EntityContainer Name =LINQtest2ModelStoreContainer>
< EntitySet Name =CHILDEntityType =LINQtest2Model.Store.CHILDstore：Type =TablesSchema =LINQ_TEST/>
< EntitySet Name =PARENTEntityType =LINQtest2Model.Store.PARENTstore：Type =TablesSchema =LINQ_TEST/>
< AssociationSet Name =LINQ_TEST_FK_PARENTAssociation =LINQtest2Model.Store.LINQ_TEST_FK_PARENT>
< End Role =PARENTEntitySet =PARENT/>
< End Role =CHILDEntitySet =CHILD/>
< / AssociationSet>
< / EntityContainer>
< EntityType Name =CHILD>
< Key>
< PropertyRef Name =CHILD_ID/>
< / Key>
< Property Name =CHILD_IDType =decimalNullable =false/>
< Property Name =PARENT_IDType =decimalNullable =false/>
< Property Name =F_NAMEType =VARCHAR2MaxLength =20/>
< Property Name =L_NAMEType =VARCHAR2MaxLength =50/>
< Property Name =CREATE_DATEType =DATENullable =false/>
< / EntityType>
< EntityType Name =PARENT>
< Key>
< PropertyRef Name =PARENT_ID/>
< / Key>
< Property Name =PARENT_IDType =decimalNullable =false/>
< Property Name =F_NAMEType =VARCHAR2MaxLength =20/>
< Property Name =L_NAMEType =VARCHAR2MaxLength =50/>
< Property Name =CREATE_DATEType =DATENullable =false/>
< / EntityType>
<关联名称=LINQ_TEST_FK_PARENT>
< End Role =PARENTType =LINQtest2Model.Store.PARENTMultiplicity =1>
< OnDelete Action =Cascade/>
< / End>
< End Role =CHILDType =LINQtest2Model.Store.CHILDMultiplicity =*/>
<参考证明>
< Principal Role =PARENT>
< PropertyRef Name =PARENT_ID/>
< / Principal>
<从属角色=CHILD>
< PropertyRef Name =PARENT_ID/>
< / Dependent>
< / ReferentialConstraint>
< / Association>
< Function Name =FNC_ADDReturnType =decimalAggregate =falseBuiltIn =falseNiladicFunction =falseIsComposable =trueParameterTypeSemantics =AllowImplicitConversionSchema =LINQ_TEST>
< Parameter Name =V1Type =decimalMode =In/>
< Parameter Name =V2Type =decimalMode =In/>
< / Function>
< / Schema>< / edmx：StorageModels>
我创建了一个扩展方法来定义实体数据模型的功能，如下所示： p>
public partial class LINQtest2Entities
{
[EdmFunction（LINQtest2Model，FNC_ADD）]
public decimal FNC_ADD（decimal V1，decimal V2）
{
//不需要实现函数
throw new ApplicationException（）;
}
}
我在LINQ表达式中调用该函数，如下所示：
using（var context = new LINQtest2Entities（））
{
var parents = from p in context .PARENTs
选择新
{
children = from c in p.Children
select new
{
p.PARENT_ID，
c。 CHILD_ID，
a = context.FNC_ADD（p.PARENT_ID，c.CHILD_ID）
}
};
foreach（父母中的父项）
{
foreach（parent.children中的var child）
{
Console.WriteLine（P {0 } C {1} A {2}，child.PARENT_ID，child.CHILD_ID，child.a）;
}
}
}
所有内容都正确编译，我运行代码我得到这个：
指定的方法'System.Decimal FNC_ADD（System.Decimal，System.Decimal）'在类型'LINQtest2.LINQtest2Entities'无法翻译成LINQ to Entities存储表达式。
我做错了什么？
您的命名空间参考 EdmFunctionAttribute 看起来很可疑。这看起来像CLR类型，而不是存储空间。 这篇博客文章可能会帮助您排除这一点。
I am using Visual Studio 2010, C#, Entity Framework 4 and Oracle 10g.
I need to be able to return the result of a database function as a scalar property of an anonymous type.
My Oracle schema has two tables, PARENT and CHILD, and a function FNC_ADD. I have created an entity model using the Visual Studio ADO.NET Entity Data Model template, including both tables and the function.
The StorageModels section of my .edmx file looks like this:
<!-- SSDL content -->
<edmx:StorageModels>
<Schema Namespace="LINQtest2Model.Store" Alias="Self" Provider="Devart.Data.Oracle" ProviderManifestToken="ORA" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
<EntityContainer Name="LINQtest2ModelStoreContainer">
<EntitySet Name="CHILD" EntityType="LINQtest2Model.Store.CHILD" store:Type="Tables" Schema="LINQ_TEST" />
<EntitySet Name="PARENT" EntityType="LINQtest2Model.Store.PARENT" store:Type="Tables" Schema="LINQ_TEST" />
<AssociationSet Name="LINQ_TEST_FK_PARENT" Association="LINQtest2Model.Store.LINQ_TEST_FK_PARENT">
<End Role="PARENT" EntitySet="PARENT" />
<End Role="CHILD" EntitySet="CHILD" />
</AssociationSet>
</EntityContainer>
<EntityType Name="CHILD">
<Key>
<PropertyRef Name="CHILD_ID" />
</Key>
<Property Name="CHILD_ID" Type="decimal" Nullable="false" />
<Property Name="PARENT_ID" Type="decimal" Nullable="false" />
<Property Name="F_NAME" Type="VARCHAR2" MaxLength="20" />
<Property Name="L_NAME" Type="VARCHAR2" MaxLength="50" />
<Property Name="CREATE_DATE" Type="DATE" Nullable="false" />
</EntityType>
<EntityType Name="PARENT">
<Key>
<PropertyRef Name="PARENT_ID" />
</Key>
<Property Name="PARENT_ID" Type="decimal" Nullable="false" />
<Property Name="F_NAME" Type="VARCHAR2" MaxLength="20" />
<Property Name="L_NAME" Type="VARCHAR2" MaxLength="50" />
<Property Name="CREATE_DATE" Type="DATE" Nullable="false" />
</EntityType>
<Association Name="LINQ_TEST_FK_PARENT">
<End Role="PARENT" Type="LINQtest2Model.Store.PARENT" Multiplicity="1">
<OnDelete Action="Cascade" />
</End>
<End Role="CHILD" Type="LINQtest2Model.Store.CHILD" Multiplicity="*" />
<ReferentialConstraint>
<Principal Role="PARENT">
<PropertyRef Name="PARENT_ID" />
</Principal>
<Dependent Role="CHILD">
<PropertyRef Name="PARENT_ID" />
</Dependent>
</ReferentialConstraint>
</Association>
<Function Name="FNC_ADD" ReturnType="decimal" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="LINQ_TEST">
<Parameter Name="V1" Type="decimal" Mode="In" />
<Parameter Name="V2" Type="decimal" Mode="In" />
</Function>
</Schema></edmx:StorageModels>
I created an extension method to define the function for the entity data model that looks like this:
public partial class LINQtest2Entities
{
[EdmFunction("LINQtest2Model", "FNC_ADD")]
public decimal FNC_ADD(decimal V1, decimal V2)
{
// don’t need to implement the function
throw new ApplicationException();
}
}
I call the function in a LINQ expression like this:
using (var context = new LINQtest2Entities())
{
var parents = from p in context.PARENTs
select new
{
children = from c in p.Children
select new
{
p.PARENT_ID,
c.CHILD_ID,
a = context.FNC_ADD(p.PARENT_ID, c.CHILD_ID)
}
};
foreach (var parent in parents)
{
foreach (var child in parent.children)
{
Console.WriteLine("P {0} C {1} A {2}", child.PARENT_ID, child.CHILD_ID, child.a);
}
}
}
Everything compiles correctly, but when I run the code I get this:
The specified method 'System.Decimal FNC_ADD(System.Decimal, System.Decimal)' on the type 'LINQtest2.LINQtest2Entities' cannot be translated into a LINQ to Entities store expression.
What am I doing wrong?
Your namespace arg to EdmFunctionAttribute looks suspicious. That looks like a CLR type, not a store space. This blog post might help you sort that out.
这篇关于如何从Entity Framework 4中的LINQ表达式中执行Oracle函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
