Returning a result set AND output parameter

[richardFlow]

Hello, I am having trouble returning a value from a reverse mapped stored procedure. I am using SQL Server 2008 and the latest version of Telerik ORM

When I reverse map the sproc, I let Telerik ORM create the following function in StoredProcedure.cs:

public static IEnumerable<ViewServiceProvider> ServiceProvidersDirectorySearch(IObjectScope scope, double? latitude, double? longitude, double? maxDistance, bool? deleted, int? currentPage, int? pageSize, string categories, string services, ref int? totalRecords)
       {
           Query<ViewServiceProvider> query = scope.GetSqlQuery<ViewServiceProvider>("[ServiceProvidersDirectorySearch] ?,?,?,?,?,?,?,?,?", "DOUBLE Latitude,DOUBLE Longitude,DOUBLE MaxDistance,BIT Deleted,INTEGER CurrentPage,INTEGER PageSize,LONGVARCHAR Categories,LONGVARCHAR Services,OUT.INTEGER TotalRecords");

           QueryResultEnumerable<ViewServiceProvider> res = query.ExecuteEnumerable(new object[] { latitude, longitude, maxDistance, deleted, currentPage, pageSize, categories, services, totalRecords });

           return res;
       }

Now, this returns the result set perfectly as a list of "ViewServiceProvider". However, the output variable totalRecords is not returned. When I debug in VS2008, I can see the following error for the output parameter (after expanding res -> non-public members -> Query Result -> OutParameter -> base:

{"A non-sequential operation like 'Count' has been executed, while the IQuery.ForwardsOnly property was set to True. Set IQuery.ForwardsOnly property to False to allow proper execution of non-sequential operations."}

Now, within the sproc, the @TotalRecords variable is indeed set by a count statement (on a temporary table). I have looked through the forums to see about IQuery.ForwardsOnly, but I am a bit confused as to why the generated code from reverse mapping creates a "Query" object, but the error says I need to use an "IQuery" object and set ForwardsOnly to true.?

I have tried looking for ForwardsOnly under the Query object but it doesn't seem to be an option I can set anywhere. If I try to change my result set to an IQuery object, this datatype is not recognised.

What am I doing wrong?

Many thanks!

[Ady]

Hello Web Belief Ltd,

 Obtaining the OUT parameter value via the QueryResultEnumerable<T> is currently not possible. We are currently working on this and it should be available soon.

Alternatively, you could use the IObjectScope.GetSqlQuery (string,Type,param) method to execute the stored procedure.  You can specify 'typeof(ViewServiceProvider)' as the second parameter.  the The IQueryResult has the 'OutParameter' property from which you can obtain the OUT parameter value. You can find more information about that, here.
 
  Do get back to us in case you need further assistance.

Greetings,
Ady
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[richardFlow]

Many thanks for your reply.

I tried your suggestion, using IQueryResult, and it does return the output parameter OK. However, I need to get the result set as a List of <ViewServiceProvider>. How can I convert this result into the specific list? A non-specific IList is no good - is there any way to get a "IList<ViewServiceProvider>" from the IQueryResult?

Thanks again!

[Ady]

Hi Web Belief Ltd,

 You can use the following overload of 'GetSqlQuery' -

Query GetSqlQuery(string sqlExpression, Type result,string param);

and specify typeof(ViewServiceProvider) as the second parameter. This would return you instances of 'ViewServiceProvider' when  you execute the query and enumerate the returned IQueryResult.

Do let me know if that works for you.

All the best,
Ady
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.