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.

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

Solution 2

We found that this was caused by having the wrong connectionString.

EF needs a connection string that looks like this:

<connectionStrings>
  <add name="MyModel_Entities" connectionString="metadata=res://*/Models.MyModel.csdl|res://*/Models.MyModel.ssdl|res://*/Models.MyModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=192.168.1.200;initial catalog=MyDb_Live;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

Where it says MyModel this should correspond with the name of your .edmx model file.

If you have copied a connectionString from somewhere else it may look like this:

<add name="MyModel_Entities" connectionString="Data Source=.;Initial Catalog=MyDb_Live;Integrated Security=SSPI;MultipleActiveResultSets=true" providerName="System.Data.SqlClient" />

Note particularly the difference in the providerName at the end of the connectionStrings.

NB We are using EF 6.1 but I believe this applies to earlier versions too. If you fix your connection string we have found you can continue to use the code generated by the T4 templates. You do not need to switch ObjectParameter to SqlParameter and ExecuteFunction to ExecuteStoreQuery.

Comments

[Glen Little]

Thanks... I'll try this out in the next week or two. Hopefully be able to get out of Debug mode!

[Glen Little]

I agree... Connection string formats are critical for EF. Thanks for highlighting that.

[Marc]

Perfect, thank you! Don't know how long it would have taken me to find this out by myself!

[ThunD3eR]

I am trying to do something similar but I don,t have a sp i have a TVF. I get back an error saying: "function is not a sp, it is a TVF". Do you have an idea how this might be solved?

[xDevil]

I have added 2 EDMX in my project and i just copy pasted the connection string and this ended up being the solution for me. Because i had the wrong "MyModel" it looked for the stored procedure in another container. Thank you