Bit-Bool on a Entity Framework Scalar Function Throws 'cannot be translated' Exception

Question

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:

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.

• The scalar function works when run using in SQL MANAGEMENT STUDIO
• Changing the RETURN TYPE doesn't seem to work.

I have tried changing the RETURN TYPE to...

• int
• object
• bool

Why is this failing?

THE CALLING LOOKS LIKE:

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

THE LET FUNCTION LOOKS LIKE:

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

THE SQL LOOKS LIKE:

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

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

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

As mentioned in the 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

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

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

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

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

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