DevForce 2012: Get SQL from query

Get SQL from query

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2012
Forum Discription: For .NET 4.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=4174
Printed Date: 10-Apr-2025 at 2:02pm

Topic: Get SQL from query

Posted By: murray.bryant
Subject: Get SQL from query
Date Posted: 12-Jun-2013 at 2:45am

I have the need to extract the sql from a query into a string. I basically need to pass this dynamic where statement into a stored procedure to return data ( for performance reasons)

  var p1 = FilterViewModel.FilterPredicate;
                ITypedEntityQuery query = EntityQuery.Create(entityType);

                if (p1 != null)
                {
                    query = query.Where(p1);
                }

in Entity Framework you can use the getcommand to return the sql statement as per http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.getcommand.aspx - http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.getcommand.aspx

Is there a way to do this with Ideablade?

thanks

Murray

Replies:

Posted By: sbelini
Date Posted: 12-Jun-2013 at 12:24pm

murray.bryant,

Unfortunately this is not possible in DevForce.
One of the main reasons is that DevForce is n-tier, so when a query is executed in the client, it is sent to the server as is.
Translating the EntityQuery into an "EF query" in order to obtain the corresponding SQL in the client wouldn't be feasible.

Posted By: murray.bryant
Date Posted: 12-Jun-2013 at 5:01pm

Ok can you think of a work around then?

At some point devforce does convert its queries into SQL statements. Is it in anyway possible to capture those statements?

Posted By: murray.bryant
Date Posted: 12-Jun-2013 at 5:06pm

someone else requested this functionality two years ago. But noone replied.

http://www.ideablade.com/forum/forum_posts.asp?TID=2793&title=feature-request-support-totracestring-on-a-perquery-basis - http://www.ideablade.com/forum/forum_posts.asp?TID=2793&title=feature-request-support-totracestring-on-a-perquery-basis

did anything happen with this?

Posted By: murray.bryant
Date Posted: 12-Jun-2013 at 5:37pm

My other option would be to execute the query on the client then pass the colleciton of results back to the server for processing.

This would need the use of Table values in Stored Procedures.

I know you can do this in EntityFramework ( see http://stackoverflow.com/questions/8157345/entity-framework-stored-procedure-table-value-parameter )

Would something like this work in DevForce?

I would worry about performance with this method still though.

Posted By: sbelini
Date Posted: 13-Jun-2013 at 1:37am

murray.bryant,

Regarding the post at http://www.ideablade.com/forum/forum_posts.asp?TID=2793&title=feature-request-support-totracestring-on-a-perquery-basis - http://www.ideablade.com/forum/forum_posts.asp?TID=2793&title=feature-request-support-totracestring-on-a-perquery-basis , it's unlikely that casting EntityQuery to ObjectQuery will be possible.

I'm confused as to what you are trying to accomplish. You mentioned that you "need to pass this dynamic where statement into a stored procedure to return data ( for performance reasons)", but then suggest that an "option would be to execute the query on the client then pass the colleciton of results back to the server for processing"... I can't make sense of it.

Please explain in detail what you are trying to do, so we can better understand the problem and suggest an appropriate approach, since retrieving the SQL from an EntityQuery is not an option.

Regards.

Posted By: murray.bryant
Date Posted: 13-Jun-2013 at 2:38am

I have a stored procedure that processes some data and returns the results. I am using a stored procedure to process this data on the sql server for performance reasons.

The user can filter what data they want processed ( Ie which locations they want the data processed for). I determine the locations that they are interested on the client by building a IPredicateDescription and then doing a typed entity query. This will give me a list of the locations on the client.

My problem is passing the list of locations ( id guids) back to the server so that the stored procedure can run and make the results for those locations.

The most elegant method I can come up with is to extract the SQL from the entity query, pass that string to the server stored procedure and get the results back.

I have considered the following

- I can't pass a list of Guids to the server because there is the potential for there to be far to many.

- I can't do one at a time because the time cost of calling the stored procedure through devforce is too great

- If i could pass a table of results to the stored procedure this might work, though I am not sure it is supported and it would not have as good a performance as just passing the sql

Does this make sense?

thanks for your help

Posted By: cefernan
Date Posted: 13-Jun-2013 at 6:56am

murray.bryant,

As an alternative, you can put necessary data in a temporary table in database and the stored procedure can read those rows and do what need to be done.

Posted By: murray.bryant
Date Posted: 13-Jun-2013 at 5:26pm

Hi Cefernan

Thanks for the suggestion, however performance wise doing tens of thousands of inserts into a table through devforce would be terrible.

The only way to do the insert would be to bypass devforce completely. Which I am trying not to do.

Posted By: murray.bryant
Date Posted: 19-Jun-2013 at 6:52pm

Hi Sbelini

I am still waiting on your response on this issue

thanks

Murray

Posted By: sbelini
Date Posted: 19-Jun-2013 at 8:26pm

Murray,

You will not be able to do this by getting the SQL from an EntityQuery.

The choice is to use a stored procedure, but like you mentioned "there is the potential for there to be far to many" (guids). What I can think of is that if the list of guids grow too big, split it and make multiple stored procedure calls.

Unfortunately, I can't think of any other option to deal with this sort of bulk operation.