实体框架上的位布尔标量函数引发“无法翻译"异常 [英] Bit-Bool on a Entity Framework Scalar Function Throws 'cannot be translated' Exception

问题描述

我有许多有效的Entity Framework标量函数.但是，当我尝试通过标量函数返回真实"值时，出现以下异常:

I have many working Entity Framework Scalar Function's. However, when I try to return a 'truthy' value through a scalar function I get the following exception:

指定的方法'Boolean svfn_CanCloneDocument(Int32， 类型上的System.String)' 'ETC.Operations.DbClient.DbClient.Data.DbClientContext'不能为 转换为LINQ to Entities存储表达式.

The specified method 'Boolean svfn_CanCloneDocument(Int32, System.String)' on the type 'ETC.Operations.DbClient.DbClient.Data.DbClientContext' cannot be translated into a LINQ to Entities store expression.

• 在SQL MANAGEMENT STUDIO中运行时，标量函数有效
• 更改返回类型似乎无效.

我尝试将RETURN TYPE更改为...

I have tried changing the RETURN TYPE to...

• int
• 对象
• 布尔

为什么会失败?

喜欢打电话的样子:


public IQueryable<ShakeoutDataItem> Query()
{
    var uow = UnitOfWork as DbClientUnitOfWork;
    var dbContext = UnitOfWork.DbContext as DbClientContext;

    var query = (from document in dbContext.vDocumentStatus
                 join shakeout in uow.Shakeout on document.DocumentId equals shakeout.DocumentId
                 join shakeoutDetail in uow.ShakeoutDetail on shakeout.Id equals shakeoutDetail.ShakeoutId
                 join meter in uow.Meter on shakeoutDetail.MeterId equals meter.Id
                 join product in uow.Product on shakeout.ProductId equals product.Id into productLEFTJOIN
                 from product in productLEFTJOIN.DefaultIfEmpty()

                 // THIS FAILS
                 let cloneable = dbContext.svfn_CanCloneDocument(document.DocumentId, "SHAKEOUT")

                 select new ShakeoutDataItem()
                 {
                     // Other fields LEFT OUT for BREVITY
                     CanClone = cloneable
                 });

    return query.OrderBy(x => x.DocumentCreatedDate).ThenBy(x => x.SchedulingBatch);
}

让我们喜欢的功能:


[Function(FunctionType.ComposableScalarValuedFunction, nameof(svfn_CanCloneDocument), Schema = "dbo")]
[return: Parameter(DbType = "bit")]
public bool svfn_CanCloneDocument(int documentId, string documentTypeShortName)
{
    ObjectParameter documentIdParameter = new ObjectParameter("documentId", documentId);
    ObjectParameter documentTypeShortNameParameter = new ObjectParameter("documentTypeShortName", documentTypeShortName);

    return this.ObjectContext().ExecuteFunction<bool>(nameof(this.svfn_CanCloneDocument), documentIdParameter, documentTypeShortNameParameter).SingleOrDefault();
}

SQL风格:


CREATE FUNCTION [dbo].[svfn_CanCloneDocument]
(
    @DocumentId INT,
    @DocumentTypeShortName NVARCHAR(50)
)
RETURNS BIT
AS
BEGIN
    /*
        Name: [dbo].[svfn_CanCloneDocument]
        Creation Date: 02/02/2019

        Purpose: Retrieves the Full Name for given User.Id or returns NULL

        Input Parameters:   @DocumentId             = The Id for the DOCUMENT record
                            @DocumentTypeShortName  = The Short Name for the DOCUMENT TYPE record

        Format:             @DocumentId             = 1
                            @DocumentTypeShortName  = SHAKEOUT
    */
    DECLARE @Value BIT = CAST(0 AS BIT);

    -- NOTE: They are going to have more DOCUMENT TYPES later-on.  If the rules for Cloneable are the same...simplify this function
    IF(@DocumentTypeShortName = 'SHAKEOUT')
    BEGIN
        DECLARE @Id INT = (SELECT TOP 1 Id FROM [dbo].[tvfn_ListDocumentDescendants](@DocumentId) WHERE Id <> @DocumentId ORDER BY Id DESC);

        -- CAN CLONE When no Descendants Exist
        SELECT @Value = (CASE 
                            WHEN @Id IS NULL THEN CAST(1 AS BIT)
                            ELSE CAST(0 AS BIT)
                         END)
    END

    -- Return the result of the function
    RETURN @Value
END

推荐答案

我有许多有效的Entity Framework标量函数

I have many working Entity Framework Scalar Function's

问题是，在 LINQ to Entities 查询中使用的 this 数据库上下文中，您是否还有其他工作的自定义标量函数?

The question is, do you have other working custom scalar functions in this db context used in LINQ to Entities query?

您正在关注bool返回类型，但是异常消息指示未映射函数(当LINQ查询使用未知的自定义方法无法转换时，EF6会抛出相同的异常.到SQL).

You are concentrating on the bool return type, but the exception message indicates not mapped function (the same exception is thrown by EF6 when the LINQ query is using unknown custom method which cannot be translated to SQL).

如向实体模型中添加函数所述:

在调用任何代码优先功能之前，必须将FunctionConvention或FunctionConvention<TFunctions>添加到DbContext的DbModelBuilder中，函数使用的复杂类型也必须添加

Before calling any code first function, FunctionConvention or FunctionConvention<TFunctions> must be added to DbModelBuilder of the DbContext, so are the complex types used by functions

您需要将以下行添加到您的DbClientContext类OnModelCreating替代项:

You need to add the following line to your DbClientContext class OnModelCreating override:

modelBuilder.Conventions.Add(new FunctionConvention<DbClientContext>());

忘记了，您可以在LINQ to Entities查询之外使用诸如此类的标量函数，例如

Forgetting to do that allows you to use the scalar functions like this outside of the LINQ to Entities query, e.g.

var result = dbContext.svfn_CanCloneDocument(...);

，但是在LINQ to Entities查询中使用时会导致上述运行时异常.

but causes the aforementioned runtime exception when used inside LINQ to Entities query.

通过FunctionConvention注册它们可以正确处理后面的情况.

Registering them via FunctionConvention allows proper handling of the later scenario.

这篇关于实体框架上的位布尔标量函数引发“无法翻译"异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！