Entity Framework EF4.1 - stored procedure "could not be found in the container"

Question

I have a SP in my database. For EF4.1, using the DbContext API.

After importing the function from the data model, references to the stored procedure works fine in my development environment. But when published to the server, it fails with a message like: The FunctionImport 'SqlSearch' could not be found in the container 'TallyJ2Entities'. All other data access is working fine.

It seems that in production, some aspects of the EF4 configuration are forgotten.

The databases are identical, and both servers are SQL 2008 (local is Express SP1 10.50.2500, host is Express RTM 10.50.1600).

I've even pointed the EDMX editor directly to the production database, and updated. The result worked fine in development, but fails in the same way on the server.

Other similar questions here don't help. Someone else seems to have a similar problem enter link description here.

Any suggestions?

Update: I've found that the problem goes away when I deploy the host in Debug mode!

Inside my DbContext derived class, I put this code:

((IObjectContextAdapter)this).ObjectContext.MetadataWorkspace
var findFunction = metadataWorkspace.GetItems(DataSpace.SSpace)
            .SelectMany(gi => gi.MetadataProperties)
            .Where(m=> Equals(m.Value, "SqlSearch"))
            .Select(m => "Found {0}".FilledWith(m.Value))
            .FirstOrDefault();

When I logged the findFunction result, it turns out that the server (in Release mode) did NOT find it, while in development, it is found.

Answer 1

If using EF 4.1 and above, change "ObjectParameter" to "SqlParameter" and "ExecuteFunction" to "ExecuteStoreQuery" in your Context.cs file.

The "ExecuteStoreQuery" method also expects you to add the parameter names in-front of the stored proc. Find a snippet below:

var param1Parameter = param1 != null ?
new SqlParameter("param1", param1) :
new SqlParameter("param1", typeof(string));

var param2Parameter = param2 != null ?
new SqlParameter("param2", param2) :
new SqlParameter("param2", typeof(int));

return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<sp_TestSproc_Result>("sp_TestSproc @param1, @param2", param1Parameter, param2Parameter);

If using a template to generate your code, you might find the snippet below useful also. I.e. I've modified the standard "Fluent TT" generator to suit EF 4.3:

    void WriteFunctionImport(EdmFunction edmFunction, bool includeMergeOption)
    {
        var parameters = FunctionImportParameter.Create(edmFunction.Parameters, Code, EFTools);
        var paramList = String.Join(", ", parameters.Select(p => p.FunctionParameterType + " " + p.FunctionParameterName).ToArray());
        var returnType = edmFunction.ReturnParameter == null ? null : EFTools.GetElementType(edmFunction.ReturnParameter.TypeUsage);
        var processedReturn = returnType == null ? "int" : "ObjectResult<" + MultiSchemaEscape(returnType) + ">";

        if (includeMergeOption)
        {
            paramList = Code.StringAfter(paramList, ", ") + "MergeOption mergeOption";
        }
    #>

        <#=AccessibilityAndVirtual(Accessibility.ForMethod(edmFunction))#> <#=processedReturn#> <#=Code.Escape(edmFunction)#>(<#=paramList#>)
        {
    <#+
            if(returnType != null && (returnType.EdmType.BuiltInTypeKind == BuiltInTypeKind.EntityType ||
                                      returnType.EdmType.BuiltInTypeKind == BuiltInTypeKind.ComplexType))
            {
    #>
            ((IObjectContextAdapter)this).ObjectContext.MetadataWorkspace.LoadFromAssembly(typeof(<#=MultiSchemaEscape(returnType)#>).Assembly);

    <#+
            }

            foreach (var parameter in parameters.Where(p => p.NeedsLocalVariable))
            {
                var isNotNull = parameter.IsNullableOfT ? parameter.FunctionParameterName + ".HasValue" : parameter.FunctionParameterName + " != null";
                var notNullInit = "new SqlParameter(\"" + parameter.EsqlParameterName + "\", " + parameter.FunctionParameterName + ")";
                var nullInit = "new SqlParameter(\"" + parameter.EsqlParameterName + "\", typeof(" + parameter.RawClrTypeName + "))";
    #>
            var <#=parameter.LocalVariableName#> = <#=isNotNull#> ?
                <#=notNullInit#> :
                <#=nullInit#>;

    <#+
            }

            var genericArg = returnType == null ? "" : "<" + MultiSchemaEscape(returnType) + ">";
            var callParams = Code.StringBefore(", ", String.Join(", ", parameters.Select(p => p.ExecuteParameterName).ToArray()));
            var spParams = Code.StringBefore("@", String.Join(", @", parameters.Select(p => p.EsqlParameterName).ToArray()));

            if (includeMergeOption)
            {
                callParams = ", mergeOption" + callParams;
            }
    #>
            return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<#=genericArg#>("<#=edmFunction.Name#> <#=spParams#>"
                        <#=callParams#>);
        }
    <#+
        if(!includeMergeOption && returnType != null && returnType.EdmType.BuiltInTypeKind == BuiltInTypeKind.EntityType)
        {
            WriteFunctionImport(edmFunction, true);
        }
    }