Error 6046: Unable to generate function import return type of the store function

Question

I have a scalar-valued function in my sql database.

I receive this error when importing this function into Entity Framework model:

Error 6046: Unable to generate function import return type of the store function 'GetContentByIdAndCul'.
The store function will be ignored and the function import will not be generated.   ..\EntityModels.edmx

my function tsql is:

ALTER FUNCTION [FRM].[GetContentByIdAndCul] 
(@Id int,@Culture nvarchar(5))
RETURNS nvarchar(max)
AS
BEGIN
declare @Result nvarchar(max)

if @Id is not null
    set @Result='This Content not defined in this Language'

select @Result=Value from CUL.Contents
WHERE ID=@Id AND (CUL.Contents.Culture = LOWER(@Culture) 
            OR CUL.Contents.Culture = LOWER(SUBSTRING(@Culture,1,2)))   
return @Result      
END

Answer 1

Preceding answers show the good way to solve the problem but none works in real life.

Here's a tested solution with Entity Framework 6 that works for me. So it should works for you.

Import your scalar valued function

Import your scalar valued function [FRM].[GetContentByIdAndCul] into your Entity Framework model. It automatically creates corresponding entry in the storage model of your EntityModels.edmx file :

<Function Name="GetContentByIdAndCul" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="FRM" ReturnType="nvarchar(max)">
      <Parameter Name="Id" Type="int" Mode="In" />
      <Parameter Name="Culture" Type="nvarchar(5)" Mode="In" />
</Function>

Add code to wrap call to your scalar valued function

Create new source file and add code to auto generated DbContext class (say her name is MyEntities) using Partial class mechanism (https://msdn.microsoft.com/en-us/library/wa80x488%28v=vs.120%29.aspx)

public partial class MyEntities
{
    [DbFunction("EntityModels.Store", "GetContentByIdAndCul")]
    public string GetContentByIdAndCul(int id, string culture)
    {
       var objectContext = ((IObjectContextAdapter)this).ObjectContext;

       var parameters = new List<ObjectParameter>();
       parameters.Add(new ObjectParameter("Id", id));
       parameters.Add(new ObjectParameter("Culture", culture));

       return objectContext.CreateQuery<string>("EntityModels.Store.GetContentByIdAndCul(@Id, @Culture)", parameters.ToArray())
            .Execute(MergeOption.NoTracking)
            .FirstOrDefault();
    }
}

Use your scalar valued function

Client code :

using (var context = new MyEntities())
{
    int id = 1;
    string culture = "fr-FR";
    string result = null;

    result = context.GetContentByIdAndCul(id, culture);
}

Answer 2

Until today, entity framework doesn't suport generating calls for scalar functions. But, you can solve the problem writting a custom method like this inside your DbContext class:

public partial class YouDbContext
{
    [DbFunction("YouDbContext.Store", "YourScalarFunction")]
    public string YourScalarFunction(string parameter)
    {
        var lObjectContext = ((IObjectContextAdapter)this).ObjectContext;

        return lObjectContext.
            CreateQuery<string >(
                "YouDbContext.Store.YourScalarFunction", 
                new ObjectParameter("parameterName", parameter)).
            Execute(MergeOption.NoTracking).
            FirstOrDefault();
    }
}