DB function call with Entity Framework 6

Question

I followed these instructions to add a scalar function to my Entity Framework 6 data model. How do I use a scalar function with linq for an object?

However, I cannot call the function in the LINQ query, although the method call works directly in the DataContext.

using (Entities context = new Entities()) { // This works. var Test1 = context.fn_GetRatingValue(8, 9, 0).FirstOrDefault(); // This doesn't work. var Test2 = (from r in context.MediaRatings select context.fn_GetRatingValue(r.Height, r.Depth, 0)).ToList(); } 

The second request causes this error.

 LINQ to Entities does not recognize the method 'System.Data.Entity.Core.Objects.ObjectResult`1[System.Nullable`1[System.Single]] fn_GetRatingValue(System.Nullable`1[System.Single], System.Nullable`1[System.Single], System.Nullable`1[System.Single])' method, and this method cannot be translated into a store expression. 

In addition, the developer gives me this warning

 Error 6046: Unable to generate function import return type of the store function 'fn_GetRatingValue'. The store function will be ignored and the function import will not be generated. 

What am I doing wrong? How can I call a database function in a LINQ query?

Also, if the query code is sometimes run against the database, and sometimes in memory, is there a way to call the function in a way that works in both cases? I have a C # version of the same function.

thanks

Edit: here is the function I'm trying to use.

 public float? GetValue(float? Height, float? Depth, float ratio) { if (Height != null || Depth != null) { float HeightCalc = Height ?? Depth.Value; float DepthCalc = Depth ?? Height.Value; if (ratio < 0) DepthCalc = DepthCalc + (HeightCalc - DepthCalc) * -ratio; else if (ratio > 0) HeightCalc = HeightCalc + (DepthCalc - HeightCalc) * ratio; return (float)Math.Round(HeightCalc * DepthCalc * .12, 1); } else return null; } 

It can also be written on one line as follows. This line can be copied / pasted wherever I need to use it, but it will create very ugly code, although it may work. I would rather save it as a function.

 return (float)Math.Round( (Height.HasValue ? Height.Value + (ratio > 0 ? ((Depth ?? Height.Value) - Height.Value) * ratio : 0) : Depth.Value) * (Depth.HasValue ? Depth.Value + (ratio < 0 ? ((Height ?? Depth.Value) - Depth.Value) * -ratio : 0) : Height.Value) * .12, 1); 

Answer 1

I have found the answer. Although I could find very little documentation about Entity Framework 6 in which EdmFunctionAttribute is deprecated, I got this code to work.

In the EDMX file, IsComposable must be True and CommandText must be deleted. I only need to declare a function without importing the function.

Then, in a partial class of my data context, I created this function

 [DbFunction("NaturalGroundingVideosModel.Store", "fn_GetRatingValue")] public float? DbGetValue(float? height, float? depth, float ratio) { List<ObjectParameter> parameters = new List<ObjectParameter>(3); parameters.Add(new ObjectParameter("height", height)); parameters.Add(new ObjectParameter("depth", depth)); parameters.Add(new ObjectParameter("ratio", ratio)); var lObjectContext = ((IObjectContextAdapter)this).ObjectContext; var output = lObjectContext. CreateQuery<float?>("NaturalGroundingVideosModel.Store.fn_GetRatingValue(@height, @depth, @ratio)", parameters.ToArray()) .Execute(MergeOption.NoTracking) .FirstOrDefault(); return output; } 

I added a function to the MediaRating object, so I can call it without having to reference the data context.

 var Test2 = (from r in context.MediaRatings select r.DbGetValue(r.Height, r.Depth, 0)).ToList(); 

It works!