entity framework + advantage database: UDFs

Question

Hello,

I am using VS2010, Entity Framework 4.0, and Advantage v. 10 in my application. I am trying to make a UDF I have defined in my Advantage DB available to my application code. The designer does not show the UDF under stored procs in the "Update Model from Database" wizard as I would expect it to. So I manually added the UDF to the SSDL as follows:

    <Function Name="Test" ReturnType="numeric" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion">
      <Parameter Name="PartID" Type="integer" Mode="In"/>
    </Function>

I also added a CLR method stub:

    [EdmFunction("namespace.Store", "Test")]
    public static decimal Test(int partID)
    {
        throw new InvalidOperationException("Call from within an L2E query");
    }

I can see the function in my Linq-to-Entities statement; however, the generated SQL is not valid. Using ToTraceString, the UDF call looks something like this:

    "namespace.Store"."Test"("Project3"."PartID") AS "C4"

This gives me the following error:

System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> Advantage.Data.Provider.AdsException: Error 7200: AQE Error: State = 42000; NativeError = 2117; [iAnywhere Solutions][Advantage SQL Engine]Unexpected token: Scalar function name should not be delimited.

It works fine if I run the generated SQL in Advantage Data Architect and correct the function name like so:

     Test("Project3"."PartID") AS "C4"

Is there anyway to tell Entity Framework to generate the correct SQL? Am I doing something wrong in the definition of the function in the SSDL?

Thanks in advance.

Answer 1

Hello *,

It looks good. Only thing i would recommend is to change the decimal to be nullable but i doubt that would fix the problem because then you would see a different exception. its worth a try. I do covered the function a while back.

http://weblogs.asp.net/zeeshanhirani/archive/2010/04/08/calling-user-defined-database-function-from-linq.aspx

Answer 2

You need to change your function element to have BuiltIn="true". User Defined Functions are not quoted in the Advantage SQL grammar.