Answered by:
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
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(); }
- Marked as answer by Chester Hong Sunday, June 23, 2013 4:00 PM
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 RalphTuesday, 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 WizardTuesday, June 4, 2013 6:34 PM
-
Does this help? http://entityframeworktutorial.net/data-read-using-stored-procedure.aspx
JP Cowboy Coders Unite!
- Edited by Mr. Javaman II Tuesday, June 4, 2013 8:48 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(); }
- Marked as answer by Chester Hong Sunday, June 23, 2013 4:00 PM
Wednesday, June 5, 2013 11:01 AM