Problem with Take()

I am having a problem with Take in LS 3 (20/1/2010) against ms sql 2008.  

I have the following code to search for Doctors based on their name:

        public IQueryable<Doctor> SearchDoctors(string search)
        {
            var docs = from d in UnitOfWorkScope.Current.Doctors
                       where (d.Provider != null)
                             && (d.IndexName.Contains(search) || d.Provider.ProviderIndex.Contains(search))
                             && !d.Deleted && !d.Provider.Deleted
                       orderby d.IndexName                      
                       select d;

            return docs.Take(MaxDoctorSearchResults);
        }

Without the Take(), I get 494 records searching for "a". 

With the Take(), I only get 21 records.

Rather than returing the first 50 matches, Lightspeed is returning all the matches in the first 50 records in my dataset.  This is not at all what I want or expected.

The generated sql is:

Without Take():

{SELECT
  COUNT(*)
FROM
  Prvr_doc
WHERE
  (((Prvr_doc.ProviderId IS NOT NULL AND (Prvr_doc.IndexName LIKE '%a%' ESCAPE '\' OR EXISTS (
    SELECT
      Provider.*
    FROM
      Provider
    WHERE
      Provider.ProviderID = Prvr_doc.ProviderId AND
      Provider.ProviderIndex LIKE '%a%' ESCAPE '\'
  ))) AND NOT (Prvr_doc.Deleted = True)) AND NOT (EXISTS (
    SELECT
      Provider.*
    FROM
      Provider
    WHERE
      Provider.ProviderID = Prvr_doc.ProviderId AND
      Provider.Deleted = True
  )))

--> Time:   196 ms

}

With Take()

{SELECT
  COUNT(*)
FROM
  (
    SELECT
      Prvr_doc.*,
      ROW_NUMBER() OVER(ORDER BY Prvr_doc.IndexName) as RowNumber
    FROM
      Prvr_doc
  )
  Prvr_doc
WHERE
  (((Prvr_doc.ProviderId IS NOT NULL AND (Prvr_doc.IndexName LIKE '%a%' ESCAPE '\' OR EXISTS (
    SELECT
      Provider.*
    FROM
      Provider
    WHERE
      Provider.ProviderID = Prvr_doc.ProviderId AND
      Provider.ProviderIndex LIKE '%a%' ESCAPE '\'
  ))) AND NOT (Prvr_doc.Deleted = True)) AND NOT (EXISTS (
    SELECT
      Provider.*
    FROM
      Provider
    WHERE
      Provider.ProviderID = Prvr_doc.ProviderId AND
      Provider.Deleted = True
  ))) AND
  RowNumber <= 50

--> Time:   195 ms

}

Any ideas?

Anyone?

Hi Sean,

Sorry for the delay in follow up on this. I will have a look at this now and get back to you once we have made some further progress here.

Jeremy

Hi Sean,

This is a bit of a nuance in the behavior of Count() vs Find(). If you are performing a distinct count then the count will push any criteria to the inner query which makes it consistent with Find() (and the behavior you are expecting). If you dont make it distinct then the criteria will remain on the outer query which allows the count to be performed over the paged result set meaning you get a different result. This allows things like Contains() to be supported in paged scenarios.

So for the above, you could either do a .ToList().Count or .Distinct().Count() to match your intent.

Appreciate this is slightly non-intuative, so we will see if this can be improved and I will pop a ticket on the backlog to review this. Any change in the interim would be breaking so we wouldnt be implementing this until the next milestone release if we go ahead with anything.

Jeremy