CODEWITHSUNDEEP
linqentity-framework-4user-defined-functions
user26901
user26901

Reputation:

Error calling a UDF from Entity Framework LINQ query

I'm running into an issue calling a User Defined Function from a LINQ query. I'm using the .NET 4.0 framework and VS 2010. Here is a snapshot of the XML for the edmx function definition.

  <Schema Namespace="MystoreDWModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
    <Function Name="RegularPrice" store:Name="RegularPrice" IsComposable="true" Schema ="MystoreDWExtension.mystore" Aggregate="false" BuiltIn="false" ReturnType="decimal" StoreFunctionName="fn_GetPrice">
      <Parameter Name="ProductID" Type="varchar" Scale="40" Mode="In"/>
      <Parameter Name="PriceTypeID" Type="varchar" Scale="10" Mode="In"/>
      <Parameter Name="GroupCode" Type="varchar" Scale="10" Mode="In"/>
      <Parameter Name="GroupValue" Type="varchar" Scale="10" Mode="In"/>
      <Parameter Name="EffectiveDate" Type="datetime" Mode="In"/>
    </Function>

I have the following function stub defined in the code ...

[EdmFunction("MystoreDWModel.Store", "RegularPrice")]
public decimal? RegularPrice(
    string ProductID,
    string PriceTypeID,
    string GroupCode,
    string GroupValue,
    DateTime EffectiveDate)
{
    throw new NotImplementedException("You can only call this method as part of a LINQ expression");
}

The call that I'm using to access the function is as follows ...

 MystoreDWEntities4 test = new MystoreDWEntities4();

 var prices = (from products in test.Products
               select RegularPrice("PRODUCTID", "R", "D", "20", DateTime.Now));

When I attempt to access the prices data I receive the following error ...

    The specified method
'System.Nullable`1[System.Decimal] RegularPrice
(System.String, System.String, System.String, System.String, System.DateTime)'
on the type 'EntityFrameworkTest.Form1' cannot be translated
into a LINQ to Entities store expression because the instance
over which it is invoked is not the ObjectContext over which
the query in which it is used is evaluated.

I've attempted several variations of the configuration and call to no avail. Has anyone run across this error before and what steps might I use to fix it?

Upvotes: 0

Views: 2251

Answers (2)

Jenda
Jenda

Reputation: 1

Define the method as an extension method, not a plain old ordinary one.

http://jendaperl.blogspot.com/2010/11/specified-method-xxx-on-type-yyy-cannot.html

Upvotes: 0

Devart
Devart

Reputation: 122032

Try to place the RegularPrice method into the MystoreDWEntities4 class definition (use partial declaration like in the following example): 

public partial class MystoreDWEntities4 {
  [EdmFunction("MystoreDWModel.Store", "RegularPrice")]
  public decimal? RegularPrice(
    string ProductID,
    string PriceTypeID,
    string GroupCode,
    string GroupValue,
    DateTime EffectiveDate)
  {
    throw new NotImplementedException("You can only call this method as part of a LINQ expression");
  }
}

Then call it as the ObjectContext instance method, like here:

ObjectSet<Products> products = test.Products;  
var prices = from prod in products  
             select new { Price = test.RegularPrice("PRODUCTID", "R", "D", "20", DateTime.Now)};

Upvotes: 2

Related Questions