DevForce 2010: Two Issues with StoredProcQuery

Print Page | Close Window

Two Issues with StoredProcQuery

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1968
Printed Date: 12-May-2026 at 11:22pm

Topic: Two Issues with StoredProcQuery

Posted By: jmpinchot
Subject: Two Issues with StoredProcQuery
Date Posted: 15-Jul-2010 at 8:17am

I've recently hit two issues with using StoredProcQuery.

The first is relatively minor and has a workaround.

I have a stored procedure called GetFellowUserGroupMembers that takes an int as a parameter, when I attempt to call it like so:

StoredProcQuery sproc = CoreEntities.DefaultManager.GetFellowUserGroupMembersQuery(CurrentUser.Id);

I get the following exception:

The value of EntityCommand.CommandText is not valid for a StoredProcedure command. The EntityCommand.CommandText value must be of the form 'ContainerName.FunctionImportName'.
   at IdeaBlade.EntityModel.EntityManager.HandleEntityServerException(Exception pException, Boolean pTryToHandle, PersistenceOperation pOperation)
   at IdeaBlade.EntityModel.EntityManager.<ExecuteQueryAsyncCore>b__47(EntityQueryOperation op)
   at IdeaBlade.EntityModel.AsyncProcessor`1.<Execute>b__5(Object x)

This is easily fixed by having a helper method somewhere that looks like this

public static void FixSprocName(StoredProcQuery sproc)
{
    sproc.ProcedureName = sproc.DataSourceKeyName + "." + sproc.ProcedureName;
}

It feels like I shouldn't have to worry about having this helper method out there.

The second issue I have been unable to find a satisfiable work around for.

My Contact entity has a column that in that database is named _Guid, but in the edmx is named UniqueId (the designer will automatically change any column beginning with an underscore to "C<ColumnName>" (e.g C_Guid). When the above stored procedure runs I get this exception:

The data reader is incompatible with the specified 'CoreModel.DbContact'. A member of the type, 'UniqueId', does not have a corresponding column in the data reader with the same name.
   at IdeaBlade.EntityModel.EntityManager.HandleEntityServerException(Exception pException, Boolean pTryToHandle, PersistenceOperation pOperation)
   at IdeaBlade.EntityModel.EntityManager.<ExecuteQueryAsyncCore>b__47(EntityQueryOperation op)
   at IdeaBlade.EntityModel.AsyncProcessor`1.<Execute>b__5(Object x)

Ordinary EntityQuery<DbContact> queries have no problems pulling in the entity even though the column name and entity scalar property names are different. The only workaround I can find is to rename the column name in the database to UniqueId. While this works (and happily in this case affects no other stored procedures), my concern is that it will not always be viable or easy to alter column names in the database or alter c# code and the edmx to change the names there (especially in this case because of EF automattically prepends the 'C' to underscore named columns).

Is there any other workaround to this second issue?

Replies:

Posted By: ting
Date Posted: 16-Jul-2010 at 6:33pm

For the first issue, you shouldn't have to define the helper method. This may have been an issue DevForce 6.0.1, so make sure to regenerate the code using 6.0.3 or higher.

The second problem looks like it could be an issue with the Entity Framework. They have some known inconsistencies with their EDMX handling. Can you try this with the raw Entity Framework and see what happens? On the property sheet for the EDMX, set 'DevForce Enabled' to false and it will revert back to raw Entity Framework code generation. That will allow you to test it without having to recreate all your infrastructure.

Posted By: jmpinchot
Date Posted: 19-Jul-2010 at 6:56am

Even with 6.0.3.1 the first issue continues.

I haven't been able to test the second issue with EF 4, but it does happen with EF 1 so it looks like that one can be chalked up to an EF issue.

Thanks

Posted By: ting
Date Posted: 21-Jul-2010 at 7:15pm

For the first issue, can you open a support case and send the edmx as well as a script to create the stored proc? Thanks.

Posted By: stephenmcd1
Date Posted: 28-Jan-2013 at 4:39pm

I know this is a very old thread but we've also started seeing the exception "The value of EntityCommand.CommandText is not valid for a StoredProcedure command. The EntityCommand.CommandText value must be of the form 'ContainerName.FunctionImportName'." Was there any resolution to that issue? Our case seems to be intermittent so I'm at a loss debugging it. I'm hoping maybe this ancient thread can shed some light on it.

Posted By: kimj
Date Posted: 28-Jan-2013 at 6:37pm

The 6.0.4 release included several stored procedure fixes, although none specific to the problem as described, and these apparently solved the problem. We haven't had any reports of this since.

What has changed recently for you that you've started seeing the exception? New DevForce version? New sproc? Application changes? In the generated code check the methods returning a StoredProcQuery to see that they're setting the DataSourceKeyName and procedure name. Also double check the function imports in the EDMX to ensure the information is correct.

Posted By: stephenmcd1
Date Posted: 29-Jan-2013 at 11:16am

I gathered all the times that we've seen this error in development and came across 7 instances - which goes to show just how rare this is. We have many developers constantly banging on the app and the problem has only happened 7 times in the last few months.

We've since seen the problem happen on 10/17, 10/25, 10/26, 12/5, 12/7 and 1/10 in case that is helpful. (EDIT: Removed a bunch of incorrect information about when we upgraded DevForce versions)

I looked at the other changes to our code base at about that same time but didn't see anything that jumped out at me.

It's hard for me to track down what specific stored procedure is causing this problem. Most of the times when we get this error, it is a result of doing an InvokeServerMethodAsync from the client - in that case, the server-side method does a lot of querying and stored procedure calls - I don't know where the problem is happening and the exception details are not very specific. There is one other case that I think I have tracked down. It is a simple call to a stored procedure from the client and everything looks fine with it. The generated code is correctly setting the DataSourceKeyName and the EDMX looks fine. And this particular procedure gets called very frequently while the app is running (it's a procedure that checks if the user's license is still available and we verify that every few minutes) and in like 99.99% of the cases, it runs without problems.

Posted By: kimj
Date Posted: 29-Jan-2013 at 2:38pm

I'm confused by your timeline. You first saw the error on 11/17 with version 6.1.9, or on 10/25 with version 6.1.7.1?

There were a few stored proc related changes in the 6.1.7.1 release: one having to do with cloning and another with setting the DataSourceKeyName. The latter also included changes specific to sprocs returning scalars.

Intermittent problems are very frequently due to multi-threading issues. Is there any chance you're using a StoredProcQuery instance across threads? In server-side code, are you sharing an EntityManager or using the DefaultManager?

Posted By: stephenmcd1
Date Posted: 29-Jan-2013 at 4:50pm

Oh wow, sorry about that. My timeline was all wrong. Somehow I confused October and November :-( (I've updated the previous post to remove all the confusing bits).

So it appears that we saw the error a few times when we were on 6.1.7.1 as well as seeing it more recently on 6.1.11 (we didn't see it at all for the brief time we were on 6.1.9.0 but that is probably just chance). We had been on 6.1.7.1 since 5/29 but we didn't have the necessary exception logging for me to know about these errors prior to 5/16. So it's very much possible that we were running into these errors before 6.1.7.1 but they were getting swallowed by our application (or otherwise reported as some other problem due to some bugs in our code). That probably only makes this issue harder to debug!

I don't see any places where we are hanging out to a StoredProcQuery for more than a few lines - we mostly just get the StoredProcQuery from the auto-generated code, execute it and then throw it away. We don't use the DefaultManager in our app. On the server we use the 'server EM' passed in to the Server RPC methods or use the EntityManager property on the EntityServerQuery/SaveInterceptor. We also sometimes make new Entity Managers on the server via the copy constructor - but those aren't very long lived - at least not that I can see.

Posted By: kimj
Date Posted: 29-Jan-2013 at 7:51pm

What is the return type of the stored procedure you mentioned above? Also, do you do any cloning of these queries for any reason?

I'm not sure what else to look at. If possible, it would help to dump the query being executed when the error occurs.

Posted By: stephenmcd1
Date Posted: 04-Feb-2013 at 4:57pm

Hi Kim, sorry for the delay. The stored procedure does not have any return type although there is an output parameter. The EDMX for it looks like this in case it helps:

          <FunctionImport Name="CheckSessionActive">            <Parameter Name="as_session_id" Mode="In" Type="Guid" />
            <Parameter Name="as_message_text" Mode="InOut" Type="String" />
          </FunctionImport>

...

        <Function Name="p_check_session_active" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
          <Parameter Name="as_session_id" Type="uniqueidentifier" Mode="In" />
          <Parameter Name="as_message_text" Type="nvarchar" Mode="InOut" />
        </Function>

We do however use the return code of this method. We do that via a bit of somewhat tricky parameter manipulation and custom DbProviderFactory logic. It sounds complicated but it isn't too bad. We don't ever clone the stored procedure for that logic though. All we do is add or remove QueryParameter instances. In the other cases where we've run into this problem, I don't think we were doing any complicated return code processing - although, I can't tell for sure since the error happens somewhere in an RPC method.

I started looking into a way that we can have some server-side error handling to try to dump the query in case this error happens along with some context so I know where the query came from. I'm not quite sure where the best place for such logic would be. It seems I could use EntityServerQueryInterceptor.OnError(...) or EntityServerErrorInterceptor.OnError(...). Is one of those better than the other?

One thing I noticed, any time an exception like this happens, the stack trace isn't really helpful. In EntityServerQueryInterceptor.HandleException, you end up rethrowing the exception but by doing a "throw e" statement - that means that the stack trace will get lost. When I went back to the errors that our app had logged, I saw this is true because all the stack traces look like this:

RemoteStackTrace:    at IdeaBlade.EntityModel.Server.EntityServerQueryInterceptor.HandleException(Exception e, PersistenceFailure failureType)   at IdeaBlade.EntityModel.Server.EntityServerQueryInterceptor.OnExecuteQuery()
   at IdeaBlade.EntityModel.Server.EntityServerQueryInterceptor.Execute(IEntityQuery entityQuery, SessionBundle sessionBundle, IEntityServer entityServer)
   at IdeaBlade.EntityModel.Server.EntityServer.Fetch(SessionBundle sessionBundle, IEntityQuerySurrogate surrogate)
   at IdeaBlade.EntityModel.EntityManager.ExecuteServerQuery(IEntityQuerySurrogate entityQuery)

That is not very helpful - especially in our case where we'd really like to know what was happening when the problem occurred. It would be nice if the HandleException method could be updated to just rethrow the original exception so that the stack trace isn't lost.

Thanks again for the help with this annoying problem.

Posted By: kimj
Date Posted: 05-Feb-2013 at 10:50am

First, sorry about the "throw e" problem. We've been trying to root these out, but as you've found there are still some (maybe more than "some") still around.

Since you have a query interceptor, I'd add logging to the OnError method there.

Do you think the problem is only happening with stored procedures with output parms? That's something we can look at further, although so far we haven't been able to reproduce the problem.

Posted By: stephenmcd1
Date Posted: 05-Feb-2013 at 2:19pm

Yeah, those "throw e" things seem to show up at the worst times. :-). But I know they are hard to track down. In case you are in the mood to fix more "throw e" type things, there is a similar problem in CoroutineOperation.HandleError(...). That method does a "throw Error" which has caused me some problems in the past by obscuring the real stack trace (see http://www.ideablade.com/forum/forum_posts.asp?TID=2636&PID=10530&title=coroutine-and-errors#10530 - this post ).

I'll try to add some logic to OnError. Unfortunately, the error happens so rarely, we might be waiting a while before my extra error handling becomes useful.

Hmm, output parameters could be part of the problem. In the other place where I think this error has happened, there is a good chance that we are calling a stored procedure that has output parameters.

Thanks for the help.

Print Page | Close Window