Scalar Valued Function support

[Nahu]

Hello,

I tried to follow your example (http://www.telerik.com/help/openaccess-orm/openaccess-feature-ref-linq-support-querying-db-functions.html) on how to use scalar valued functions but every time i use the function in my linq query, i get a NotImplemented exception even though the documentation states that "You should not write any code for the domain method. You will use this method in LINQ queries and OpenAccess ORM will look for the attribute. The actual method will never be invoked"

I am using the new Openaccess version (Q3) with visual studio 2010 and SQL 2008.

My shared Function is declared as follows in my context :

<MappedFunctionAttribute(Name:="[dbo].[DALTest]", IsDeterministic:=False, backend:=Telerik.OpenAccess.Metadata.Backend.MsSql)> _
 Public Shared Function DALTest(ByVal orderId As Integer?) As System.Int16
   Throw New NotImplementedException()
 End Function

My DALTest function is defined in my database as:
CREATE FUNCTION [dbo].[DALTest](@OrderId [INT])
RETURNS INT
AS
BEGIN
RETURN @OrderId;
END;
GO
And i am calling it in the following code:
Using dbContext = New EntityDiagrams1()
      Dim query = (From order In dbContext.Accounts
               Let CRMStateID = EntityDiagrams1.DALTest(10)
               Select New With {.CRMID = CRMStateID})

      MessageBox.Show(query.Count)
End Using

[Damyan Bogoev]

Hi Nahu,

I am afraid that currently you cannot use the scalar functions in Linq statement with constants.

If the database function has arguments you should call it using the entity’s properties in order to supply values to it:

Using dbContext = New EntityDiagrams1()
      Dim query = (From order In dbContext.Accounts
               Let CRMStateID = EntityDiagrams1.DALTest(order.ID)
               Select New With {.CRMID = CRMStateID})

      MessageBox.Show(query.Count)
End Using

Regards,
Damyan Bogoev
the Telerik team

[Nahu]

Thanks for your reply, it works when i changed the parameter to an entity's property.
Unfortunately it doesn't work if the function doesn't take any parameters.

How would you call a function that's defined like this?

<MappedFunctionAttribute(Name:="[dbo].[DALTest]", IsDeterministic:=False, backend:=Telerik.OpenAccess.Metadata.Backend.MsSql)> _
Public Shared Function DALTest() As System.Int16
  Throw New NotImplementedException()
End Function

Thanks for your help.

[Damyan Bogoev]

Hello Nahu,

The issue is the same as when the scalar functions are used with constants in Linq statement.

We will improve this behavior in the future.

I am sorry for the inconvenience caused.

Please find your Telerik points updated.

Kind regards,
Damyan Bogoev
the Telerik team

[Matt]

Hello,

Is there any improvement in behavior for the scalar function with constants in Linq statement?

Thanks 

 

[Thomas]

Hello Matt,

yes, the functionality is there. I just verified this with

var result = from c in ctx.Products
                             let z = Product.FuncVS()
                             where c.ProductName == z
                             select new
                             {
                                 key = c.ID,
                                 val = z
                             }; 

and had the following definition

 [Telerik.OpenAccess.Metadata.MappedFunction(Name = "[dbo].[MyFuncVS]", IsDeterministic = false, Backend = Telerik.OpenAccess.Metadata.Backend.MsSql)]
        public static string FuncVS()
        {
            throw new NotImplementedException();
        }

using this method on the server

CREATE FUNCTION [dbo].[MyFuncVS] 
(
-- Add the parameters for the function here
)
RETURNS varchar(max)
AS
BEGIN

DECLARE @Result varchar(max)
SELECT @Result = 'XYZ'
RETURN @Result

END


The LINQ above then produced that SQL

SELECT a.[ID] AS COL1, [dbo].[MyFuncVS]() AS  EXPR2 FROM [Products] a WHERE a.[ProductName] = [dbo].[MyFuncVS]()

The () brackets at the end are not avoidable at the moment.

Alternatively you can also use
let z = "[dbo].[MyFuncVS]()".SQL<string>()

but you loose the ability to mark the resulting LINQ query as producing non-deterministic results, and you tie your code more to a specific database backend.

Please be aware that the let keyword will just allow the reuse of the _expression_, not the reuse of the _value_ in the query; you can see that the same function is invoked two times.

Regards,
Thomas
Telerik