CODEWITHSUNDEEP
c#linqentity-framework
Merwan
Merwan

Reputation: 483

Find out which page an item is on

I am using LINQ with entity framework in my application. I have repository method to get a page of data like this:

public IEnumerable<Sample> GetPageData(int orderId, int page, int itemsPerPage)
{
    var samples = _context.Set<Sample>()
                          .Where(s => s.OrderId == orderId)
                          .OrderBy(s => s.Id)
                          .Skip(itemsPerPage * page)
                          .Take(itemsPerPage);

    return samples;
}

I would like to have another repository method so that I can retrieve the page on which a sample is. The method signature would be something like:

public int GetPage(int orderId, int sampleId, int itemsPerPage)
{
    // ???
}

I am struggling to find a way to do it in LINQ. The only idea I have for now is to fetch the pages one after one until I find the needed sample. I know it is not efficient but the requirement is that there are no more than 500 samples and the page size is 25.

How I could do this more efficiently?

Upvotes: 15

Views: 1659

Answers (4)

Colin
Colin

Reputation: 22595

public int GetPage(int orderId, int sampleId, int itemsPerPage)
{
    //protect against divide by zero
   if(itemsPerPage < 1)
      return 1;//or 0 if you want page index

  int index = _context.Set<Sample>()
                       .Where(s => s.OrderId == orderId && s.Id < sampleId)
                       //.OrderBy(s => s.Id) edited after accepted OrderBy not necessary
                       .Count();

   //if index is zero return 1
   //if index == 9 and itemsPerPage == 10 return 1 
   //if index == 10 and itemsPerPage == 10 return 2
   //if you want the page index rather than the page number don't add 1
   return 1 + (index / itemsPerPage);
}

@Rob Lyndon's effort made me think some more, and I came up with this as a way of checking that the page actually contains the sample - in one query to the database 

public int GetPage(int orderId, int sampleId, int itemsPerPage)
{
    //protect against divide by zero
   if(itemsPerPage < 1)
      return 1;//or 0 if you want page index, or -1 if you want to flag this as invalid

   var result = context.Set<Sample>()
                .Where(s => s.OrderId == orderId 
                            && s.Id <= sampleId)//this time include sampleId
                //.OrderBy(s => s.ID)  edited after accepted OrderBy not necessary
                .GroupBy(x => true)
                .Select(group => new
                {
                    MaxID = group.Max(s => s.Id),
                    Count = group.Count()
                })
                .Single();

  //Check the sample is actually in the result
  if(result.MaxID != sampleId)
      return 1;//or 0 if you want page index, or -1 if you want to flag this as invalid

  int index = result.Count - 1;

   //if you want the page index rather than the page number don't add 1
   return 1 + (index / itemsPerPage);
}

Upvotes: 3

Rob Lyndon
Rob Lyndon

Reputation: 12651

public int GetPage(int orderId, int sampleId, int itemsPerPage)
{
    var samples = _context.Set<Sample>()
        .Where(s => s.OrderId == orderId)
        .OrderBy(s => s.Id)
        .Where(s => s.Id <= sampleId)
        .ToList();

    var lastSample = samples.LastOrDefault();
    if (lastSample == null || lastSample.Id != sampleId) return -1;

    return (samples.Count - 1) / itemsPerPage;
}

Upvotes: 1

Sameer
Sameer

Reputation: 3183

The assumption here is there is (at least) an entry for the given sampleId and orderId. 

public int GetPage(int sampleId, int itemsPerPage, int orderId)
{
   return _context.Set<Sample>().Count(s => s.OrderId == orderId && s.Id < sampleId) / itemsPerPage + 1;                
}

Upvotes: 0

Reda
Reda

Reputation: 2289

public int GetPage(int sampleId, int itemsPerPage)
{
    return _context.Set<Sample>()
                   .Count(s => s.Id <= sampleId) / itemsPerPage;
}

Upvotes: 2

Related Questions