UDF not being called from LINQ

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

Answer 1

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

---

JP Cowboy Coders Unite!

Answer 2

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

Answer 3

Is this code-first?  Or did you map the DB user function using the wizard?

---

JP Cowboy Coders Unite!

Answer 4

I used the Wizard

Answer 5

Does this help? http://entityframeworktutorial.net/data-read-using-stored-procedure.aspx

---

JP Cowboy Coders Unite!

Answer 6

That tutorial looks like its for an SP which works for me. I am trying to work with a function. Thanks though.

Answer 7

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();
}