none
linq query returning only first 100 records

    Question

  • Hi -

    I'm new to OData and to Linq, so apologies if this is a basic question. To get started, I carefully followed the instructions in this article:

    http://msdn.microsoft.com/en-us/library/gg312152.aspx

    And I'm using the Gender Info 2007 data set.

    The code works, but it only returns the first 100 records, and I can't figure out why. Filtering on the same value on the Explore Data Set page, there should be 2730 records. Nor have I been able to figure out how it might be possible to retrieve the next page, if this is a pagination issue.

    Here's what my code looks like that 

            // public method that returns a list of fertility data
            public IList<Values> GetFertilityData()
            {
                IEnumerable<Values> query = context.Values.Where(fertility => fertility.DataSeriesId == "13").OrderBy(fertility => fertility.CountryName);

                try
                {
                    return query.ToList();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: {0}", ex.Message);
                    return null;
                }  // end try
            }  // end function 

    Thanks for your help.

    Wednesday, August 14, 2013 9:02 PM

Answers

  • Hi,

      >> how it might be possible to retrieve the next page, if this is a pagination issue.

    From my experience, this could be caused by paging. Since you're using LINQ, you can take advantage of the Skip and Take methods to implement paging. Please refer to http://msdn.microsoft.com/en-us/library/bb386988.aspx for a general LINQ paging sample.

    Best Regards,

    Ming Xu


    Ming Xu
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 15, 2013 8:27 AM
    Moderator
  • I did figure out how to make this work with Skip and Take. In order to do so, it's necessary to return the query as an IQueryable. Here's the new code (on a different data set, so the field names are different):

            public IList<Values> GetValuesData2()
            {
                int i = 0;
                int page = 100;
                IQueryable<Values> q;
                List<Values> list = new List<Values>();
                List<Values> listRange;

                do
                {
                    try
                    {
                        // Using LINQ with IQueryable here because DataServiceQuery did not return data properly for this data set.
                        q = context.Values.Where(values => values.DataSeriesId == "10").OrderBy(values => values.CountryName).Skip(i * page).Take(page);
                        i++;

                        listRange = q.ToList();
                        if (listRange.Count > 0)
                        {
                            list.AddRange(q.ToList());
                            OutputValuesList(listRange);
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Error: {0}", ex.Message);
                        return null;
                    }

                } while (listRange.Count > 0);

                return list;
            }

    • Marked as answer by tlm2 Wednesday, August 21, 2013 8:11 PM
    Wednesday, August 21, 2013 8:11 PM

All replies

  • Hi,

      >> how it might be possible to retrieve the next page, if this is a pagination issue.

    From my experience, this could be caused by paging. Since you're using LINQ, you can take advantage of the Skip and Take methods to implement paging. Please refer to http://msdn.microsoft.com/en-us/library/bb386988.aspx for a general LINQ paging sample.

    Best Regards,

    Ming Xu


    Ming Xu
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 15, 2013 8:27 AM
    Moderator
  • Thank you. I am not sure whether Skip and Take will work in this case. However, I did figure out that the data set is returning a continuation token after 100 records. The way to work with the continuation token seems to be to execute a DataServiceQuery and then cast it to a QueryOperationResponse, as follows:

            //Populate table with all rows from the data series.
            public void PopulateDataSeries(CloudTable table)
            {
                //Construct and execute the OData query
                var x = from t in context.DataSeries
                        select t;

                var response = (QueryOperationResponse<DataSeries>)((DataServiceQuery<DataSeries>)x).Execute();

                int i = 0;

                while (true)
                {
                    // Enumerate the response data and add values to DynamicTableEntity objects.
                    foreach (DataSeries dataSeries in response)
                    {
                        Console.WriteLine("Id: " + dataSeries.Id);
                        Console.WriteLine("DataSetId: " + dataSeries.DataSetId);
                        Console.WriteLine("Name: " + dataSeries.Name);
                        Console.WriteLine("Count: " + ++i);
                        }

                    // Get the continuation token and retrieve the next page of data.
                    var continuation = response.GetContinuation();
                    if (continuation == null)
                    {
                        break;
                    }

                    response = context.Execute(continuation);
                }
            }

    Wednesday, August 21, 2013 3:18 PM
  • I did figure out how to make this work with Skip and Take. In order to do so, it's necessary to return the query as an IQueryable. Here's the new code (on a different data set, so the field names are different):

            public IList<Values> GetValuesData2()
            {
                int i = 0;
                int page = 100;
                IQueryable<Values> q;
                List<Values> list = new List<Values>();
                List<Values> listRange;

                do
                {
                    try
                    {
                        // Using LINQ with IQueryable here because DataServiceQuery did not return data properly for this data set.
                        q = context.Values.Where(values => values.DataSeriesId == "10").OrderBy(values => values.CountryName).Skip(i * page).Take(page);
                        i++;

                        listRange = q.ToList();
                        if (listRange.Count > 0)
                        {
                            list.AddRange(q.ToList());
                            OutputValuesList(listRange);
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Error: {0}", ex.Message);
                        return null;
                    }

                } while (listRange.Count > 0);

                return list;
            }

    • Marked as answer by tlm2 Wednesday, August 21, 2013 8:11 PM
    Wednesday, August 21, 2013 8:11 PM