I want to get records by using paging from Query Service of Dynamics AX 2012 through MVC WebAPI. When I run this code in Console Application works fine.
QueryServiceClient client = new QueryServiceClient();
DataSet dataSet;
Paging paging = new PositionBasedPaging() { StartingPosition = 1, NumberOfRecordsToFetch = 10 };
QueryMetadata query = new QueryMetadata();
QueryDataSourceMetadata customerDataSource;
query.QueryType = QueryType.Join;
query.DataSources = new QueryDataSourceMetadata[1];
customerDataSource = new QueryDataSourceMetadata();
customerDataSource.Name = "AccountNum";
customerDataSource.Enabled = true;
customerDataSource.FetchMode = FetchMode.OneToOne;
customerDataSource.Table = "CustTable";
customerDataSource.Company = "CEU";
customerDataSource.DynamicFieldList = true;
customerDataSource.OrderMode = OrderMode.OrderBy;
query.DataSources[0] = customerDataSource;
query.OrderByFields = new QueryDataOrderByMetadata[]{
new QueryDataOrderByMetadata(){ DataSource="AccountNum", FieldName="AccountNum",SortOrder=SortOrder.Ascending}
};
dataSet = client.ExecuteQuery(query, ref paging);
but the same code when runs in MVC WebAPI gives error
System.ServiceModel.FaultException`1 was unhandled by user code
HResult=-2146233087
Message=Page size 'PositionBasedPaging.StartingPosition' is out of range.
Parameter name: PositionBasedPaging.StartingPosition
Source=mscorlib
Action=http://schemas.microsoft.com/net/2005/12/windowscommunicationfoundation/dispatcher/fault
StackTrace:
Server stack trace:
at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter)
at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at SL_AX_MVC_WebAPI.QueryService.IQueryService.ExecuteQuery(ExecuteQueryRequest request)
at SL_AX_MVC_WebAPI.QueryService.QueryServiceClient.SL_AX_MVC_WebAPI.QueryService.IQueryService.ExecuteQuery(ExecuteQueryRequest request) in d:\Projects\SL_AX_MVC\SL_AX_MVC_WebAPI\Service References\QueryService\Reference.cs:line 5269
at SL_AX_MVC_WebAPI.QueryService.QueryServiceClient.ExecuteQuery(QueryMetadata queryMetadata, Paging& paging) in d:\Projects\SL_AX_MVC\SL_AX_MVC_WebAPI\Service References\QueryService\Reference.cs:line 5276
at SL_AX_MVC_WebAPI.Repositories.CustomerRepository.getCustomers(Int32 skip, Int32 take) in d:\Projects\SL_AX_MVC\SL_AX_MVC_WebAPI\Repositories\CustomerRepository.cs:line 108
at SL_AX_MVC_WebAPI.Repositories.CustomerRepository.Get(Int32 skip, Int32 take) in d:\Projects\SL_AX_MVC\SL_AX_MVC_WebAPI\Repositories\CustomerRepository.cs:line 47
at SL_AX_MVC_WebAPI.Controllers.CustomerController.Get(Int32 skip, Int32 take) in d:\Projects\SL_AX_MVC\SL_AX_MVC_WebAPI\Controllers\CustomerController.cs:line 31
at lambda_method(Closure , Object , Object[] )
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass13.<GetExecutor>b__c(Object instance, Object[] methodParameters)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.<>c__DisplayClass5.<ExecuteAsync>b__4()
at System.Threading.Tasks.TaskHelpers.RunSynchronously[TResult](Func`1 func, CancellationToken cancellationToken)
InnerException:
Any help ? i have wasted my time to tackle with this issue but no success...
Problem Solved! When using in WebAPI, QueryService of Dynamics AX 2012 needs additional properties to provide value true for whatever property is given. These properties are Post-fix with name "Specified". These properties must be given value true otherwise exception will be thrown or no data will return.
Correct Code is below:
QueryServiceClient client = new QueryServiceClient();
DataSet dataSet;
Paging paging = new PositionBasedPaging() { StartingPosition = 1, NumberOfRecordsToFetch = 10, NumberOfRecordsToFetchSpecified = true, StartingPositionSpecified = true };
QueryMetadata query = new QueryMetadata();
QueryDataSourceMetadata customerDataSource;
query.QueryType = QueryType.Join;
query.DataSources = new QueryDataSourceMetadata[1];
customerDataSource = new QueryDataSourceMetadata();
customerDataSource.Name = "AccountNum";
customerDataSource.Enabled = true;
customerDataSource.FetchMode = FetchMode.OneToOne;
customerDataSource.Table = "CustTable";
customerDataSource.Company = "CEU";
customerDataSource.DynamicFieldList = true;
customerDataSource.OrderMode = OrderMode.OrderBy;
customerDataSource.EnabledSpecified =
customerDataSource.FetchModeSpecified =
customerDataSource.DynamicFieldListSpecified =
customerDataSource.OrderModeSpecified = true;
query.DataSources[0] = customerDataSource;
query.OrderByFields = new QueryDataOrderByMetadata[]{
new QueryDataOrderByMetadata(){ DataSource="AccountNum", FieldName="AccountNum",SortOrder=SortOrder.Ascending}
};
dataSet = client.ExecuteQuery(query, ref paging);