Entity Framework EF4.1 - not found in container stored procedure

Question

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

After importing a function from the data model, references to the stored procedure work fine in my development environment. But when publishing to the server, it fails with a message like: "SqlSearch" FunctionImport cannot be found in the "TallyJ2Entities" container. All other data access works 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 RTM 10.50.1600).

I even pointed the EDMX editor directly to the production database and updated. The result worked fine in development, but the server doesn't work the exact same way.

Other similar questions do not help here . Someone else has a similar problem enter the link here .

Any suggestions?

Update: I found that the problem disappears when deploying 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 registered the result of findFunction , it turns out that the server (in Release mode) did NOT find it, but it was found during development.

Answer 1

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

The ExecuteStoreQuery method also expects you to add parameter names before the stored proc. Find the 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 you use a template to generate code, you can also find the snippet below useful. That is, I modified the standard Fluent TT generator in accordance with 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); } } 

Answer 2

We found that this was caused by an invalid String relationship.

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 match the name of your .edmx model file.

If you copied the connectionString from another location, it might look like this:

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

Pay particular attention to the difference in provider name at the end of connectionStrings.

NB We use EF 6.1, but I believe that this applies to earlier versions. If you correct your connection string, we find that 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.

Answer 3

I do not think the connection string is a problem. In my case, I cannot call the storage procedure, but I can write data to my database. this means that our connection is correct.