c# - Entity Framework 标量函数上的位 bool 值引发 'cannot be translated' 异常

Question

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

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 中运行时有效
• 更改返回类型似乎不起作用。

我尝试将返回类型更改为...

• 内部
• 对象
• bool

为什么会失败？

调用看起来像:

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

LET 函数看起来像:

[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

Answer 1

I have many working Entity Framework Scalar Function's

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

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

如 Add functions to entity model 中所述:

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覆盖:

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

忘记这样做允许您在 LINQ to Entities 查询之外使用像这样的标量函数，例如

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

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

通过 FunctionConvention 注册它们允许正确处理后面的场景。