How can I execute an Oracle function from within a LINQ expression in Entity Framework 4?

Question

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"&gt;
    <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?

Answer 1

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.

Answer 2

Use the Model.Store namespace:

public partial class LINQtest2Entities
{
    [EdmFunction("LINQtest2Model.Store", "FNC_ADD")]
    public decimal FNC_ADD(decimal V1, decimal V2)
    {
        // don’t need to implement the function
        throw new ApplicationException();
    }
}

In case you have provided the namespace correctly everything works smoothly.