Scalar Valued Function support

6 posts, 0 answers
  1. Nahu
    Nahu avatar
    2 posts
    Member since:
    Oct 2012

    Posted 22 Oct 2012 Link to this post

    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
  2. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 24 Oct 2012 Link to this post

    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
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  3. DevCraft banner
  4. Nahu
    Nahu avatar
    2 posts
    Member since:
    Oct 2012

    Posted 25 Oct 2012 Link to this post

    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.
  5. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 29 Oct 2012 Link to this post

    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
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  6. Matt
    Matt avatar
    5 posts
    Member since:
    Jun 2014

    Posted 14 Sep 2015 in reply to Damyan Bogoev Link to this post

    Hello,

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

    Thanks 

     

  7. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 16 Sep 2015 Link to this post

    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
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Back to Top
DevCraft banner