I followed these instructions to add a scalar function into my Entity Framework 6 data model. How to use scalar-valued function with linq to entity?
However, I'm not able to call the function within a LINQ query, although calling the method directly on the DataContext works.
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 query throws 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.
Also, the designer is giving 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 the database function within a LINQ query?
Also, if the query code sometimes gets executed 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's 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 in one line like this. This line could be copy/pasted everywhere I need to use it but that would produce very ugly code, although that could work. I'd rather keep 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);
Step 1: Create an entity class which inherits “DbContext” class. Step 2: The following is the structure of the database with table and stored procedure. Step 3: Create a class to store the returned tabular value. Step 4: Create an object for the entity above and method to call a function.
Step 1 − Select the Console Application from the middle pane and enter TableValuedFunctionDemo in the name field. Step 2 − In Server explorer right-click on your database. Step 3 − Select New Query and enter the following code in T-SQL editor to add a new table in your database.
EF Core allows for using user-defined SQL functions in queries. To do that, the functions need to be mapped to a CLR method during model configuration. When translating the LINQ query to SQL, the user-defined function is called instead of the CLR function it has been mapped to.
I found the answer. Although I could find very little documentation about Entity Framework 6 in which EdmFunctionAttribute is obsolete, I got this code to work.
In the EDMX file, IsComposable must be True and the CommandText must be removed. I need only the function declaration without the function import.
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 the function to the MediaRating object so I can call it without needing a reference to the data context.
var Test2 = (from r in context.MediaRatings
select r.DbGetValue(r.Height, r.Depth, 0)).ToList();
This works!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With