none
UDF not being called from LINQ RRS feed

  • Question

  • I have created a T-SQL UDF on my database which works when I call it from my database, I would like to execute that function in my c# app.

    I have this in my EDMS file

        <Schema Namespace="Vendor_TrackingModel.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="GetClaimsHistory" ReturnType="varchar" Schema="dbo" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion">
            <Parameter Name="ClaimNumber" Type="varchar" Mode="In" />
            <Parameter Name="ReturnType" Type="int" Mode="In" />
          </Function>

    I have this in my c# app

    [EdmFunction("Vendor_TrackingModel.Store", "GetClaimsHistory")]
            static public string GetClaimsHistory(string ClaimId, int ReturnType)
            {
                throw new NotSupportedException("This function can only be called via LINQ");
            }

    and this is my LINQ statement

            public void GetClaimHistory(string ClaimId, ClaimHistoryReturnType ReturnType)
            {
                var result = from x in ClaimId
                                 select new { CHString  = GetClaimsHistory(ClaimId, (int)ReturnType)};
               var answer = result.ToList();
            }

    when I execute my code, it throws the exception in the GetClaimsHistory c# method and does not execute my DB function.

    From everything I have read over and over this is the correct setup.  But it does not do what I want this to do.  any help is appreciated.

    Thanks

    Monday, June 3, 2013 6:34 PM

Answers

  • You are calling your function in a Linq to Objects expression (ClaimId is string, IEnumerable<char> but not IQueryable<char>). The main difference is that an IEnumerable expression will execute sooner or later whereas an IQueryable expression will be passed as-is to the query provider (EF in our case) without executing anything, then the provider translates it to command text and executes that.

    You need to call your function in a query expression, i.e. as an inline function:

    using( var db = new MyEntities() )
    {
       var result =
          from it in db.MyStuff
          select MyFunction(it.Column1, it.Column2);
       return result.ToList();
    }

    Unfortunately a direct function call to a scalar valued function is not directly supported by EF. You'd better wrap that in an SP. A possible workaround to define your function on the DbContext:

    public string GetClaimsHistory(string claimId, ClaimHistoryReturnType returnType)
    {
       var oc = (this as IObjectContextAdapter).ObjectContext;
       return oc.ExecuteStoreQuery<string>(
             "SELECT dbo.GetClaimsHistory({0}, {1})",
             claimId,
             returnType
       ).First();
    }

    Wednesday, June 5, 2013 11:01 AM

All replies

  • What is the exception and what does the stack trace show?

    JP Cowboy Coders Unite!

    Monday, June 3, 2013 9:44 PM
  • It throws the NotSupportedException that the C# method GetClaimsHistory. So it seems as if the EdmFunction attribute has no impact on my code and is just expecting the c# method like any other method and not going to the database. Hope that makes sense Ralph
    Tuesday, June 4, 2013 2:02 AM
  • Is this code-first?  Or did you map the DB user function using the wizard?

    JP Cowboy Coders Unite!

    Tuesday, June 4, 2013 5:08 PM
  • I used the Wizard
    Tuesday, June 4, 2013 6:34 PM
  • Tuesday, June 4, 2013 8:48 PM
  • That tutorial looks like its for an SP which works for me. I am trying to work with a function. Thanks though.
    Wednesday, June 5, 2013 12:25 AM
  • You are calling your function in a Linq to Objects expression (ClaimId is string, IEnumerable<char> but not IQueryable<char>). The main difference is that an IEnumerable expression will execute sooner or later whereas an IQueryable expression will be passed as-is to the query provider (EF in our case) without executing anything, then the provider translates it to command text and executes that.

    You need to call your function in a query expression, i.e. as an inline function:

    using( var db = new MyEntities() )
    {
       var result =
          from it in db.MyStuff
          select MyFunction(it.Column1, it.Column2);
       return result.ToList();
    }

    Unfortunately a direct function call to a scalar valued function is not directly supported by EF. You'd better wrap that in an SP. A possible workaround to define your function on the DbContext:

    public string GetClaimsHistory(string claimId, ClaimHistoryReturnType returnType)
    {
       var oc = (this as IObjectContextAdapter).ObjectContext;
       return oc.ExecuteStoreQuery<string>(
             "SELECT dbo.GetClaimsHistory({0}, {1})",
             claimId,
             returnType
       ).First();
    }

    Wednesday, June 5, 2013 11:01 AM