使用实体框架 6 调用数据库函数 [英] Calling DB Function with Entity Framework 6

问题描述

我按照这些说明将标量函数添加到我的 Entity Framework 6 数据模型中.如何在linq to entity中使用标量值函数?

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?

但是，我无法在 LINQ 查询中调用该函数，尽管直接在 DataContext 上调用该方法是可行的.

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.

我做错了什么?如何在 LINQ 查询中调用数据库函数?

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

另外，如果查询代码有时会针对数据库执行，有时会在内存中执行，有没有办法以在这两种情况下都有效的方式调用函数?我有一个相同函数的 C# 版本.

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.

谢谢

这是我正在尝试使用的功能.

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

推荐答案

我找到了答案.虽然我可以找到很少的关于 Entity Framework 6 的文档，其中 EdmFunctionAttribute 已过时，但我得到了这段代码.

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.

在 EDMX 文件中，IsComposable 必须为 True，并且必须删除 CommandText.我只需要函数声明而不需要函数导入.

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

我将函数添加到 MediaRating 对象，这样我就可以在不需要引用数据上下文的情况下调用它.

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

这行得通！

这篇关于使用实体框架 6 调用数据库函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！