Calling DB Function with Entity Framework 6

Question

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

Solution

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!

Comments

[Etienne Charland]

I just realized that when building LINQ queries, the DbGetValue code doesn't even get called! So you could remove the code in the function, or calculate it without calling the database, as the code only gets called when calling it directly outside of a query.