Lambda expressions order by and take issue

Question

I have a IQueryable list with COLOURS class type

IQueryable<COLOURS> renkler = dbcontext.colours.Select(s=>new COLOURS{ .... 

I want to get random 2 rows, I am using this code block to do this:

renkler.OrderBy(o => Guid.NewGuid()).Take(2);

I want 2 rows but sometimes its getting 3 rows or 5 rows:

Take(2) is not working - what's the problem?

I have noticed something when I check

var result = NewProducts().OrderBy(o => Guid.NewGuid()).Take(2);
int result_count = result.Count(); //This value is 2 :D
                                   //but ToList() result 5 :D

Entire MEthod:

public IQueryable<COLOURS> NewProducts() 
{
    DateTime simdi = DateTime.Now;
    DateTime simdi_30 = DateTime.Now.AddDays(-30);

    var collection_products = DefaultColours()
                             .Where(w => ((w.add_date.Value >= simdi_30 && w.add_date.Value <= simdi) || w.is_new == true))
                             .OrderByDescending(o => o.add_date).Take(200)
                             .Select(s => new COLOURS
                             {
                                 colour_code = s.colour_code,
                                 model_code = s.products.model_code,
                                 sell_price = (decimal)s.sell_price,
                                 market_price = (decimal)s.market_price,
                                 is_new = (bool)s.is_new,
                                 product_id = (int)s.product_id,
                                 colour_name = s.name,
                                 product_name = s.products.name,
                                 description = s.products.description,
                                 img_path = s.product_images.FirstOrDefault(f => f.is_main == true).img_path,
                                 category_id = (int)s.category_relations.FirstOrDefault().category_id,
                                 display_order = (short)s.display_order,
                                 section_id = (int)s.products.section_id,
                                 stock_amount = s.pr_sizes.Where(w => w.is_active == true && w.quantity >= 0).Count() > 0 ? (int)s.pr_sizes.Where(w => w.is_active == true && w.quantity >= 0).Sum(s2 => s2.quantity) : 0,
                                                                      section_name = s.products.pr_sections.name,

                             });    
    return collection_products;
}

public IQueryable<COLOURS> RandomNewProducts(int n) 
{
    var result = NewProducts().OrderBy(o => Guid.NewGuid()).Take(n);
    int result_count = result.Count(); //2
    //When I run this method it's getting 5 rows               
    return result;
}

Answer 1

This could be due to the o => Guid.NewGuid() lambda.

The sorting algorithm requires a unique key that is tied to each element. Calling Guid.NewGuid() means that any given element could have multiple keys associated with it depending on when it gets called. Linq attempts to be opportunistic in how it operates on sets, so this could cause e.g. the lowest two elements to suddenly no longer be the lowest two elements during the sorting operation.

Consider trying to sort a list of random integers where those integers are randomly changing every time the sorting algorithm tries to retrieve them. The only way this would work is if the sorting algorithm provides a guarantee to call the key function once and only once for every element.

The documentation on the OrderBy does not say whether the sorting algorithm is allowed to call the key function more than once for each element, so it is best to assume the worst case unless you can prove otherwise.

As a (hopefully) simple way to test this, if you can temporarily include the random key as a persistent element of your COLOUR object so the order does not change during the sort, the .Take() should begin working exactly as intended.

Also, Guid.NewGuid() is not fast, so turning that temporary test into a permanent solution that uses a bit more memory for each object could improve the speed of your code as well.

Answer 2

This may be no solution to you but it's hard to format comments with multiline code and images.

I'm pretty sure that this is an issue with your data provider. Maybe this component does not implement Take() the way it should.

I tried to rebuild your constellation but instead of any IQueryable provider I built a List<> with 500 objects and called AsQueryable() on it to satisfy the method signature.

    public static IQueryable<COLOURS> DefaultColours()
    {
        const int COUNT = 500;

        List<COLOURS> x = new List<COLOURS>();

        var startDate = DateTime.Today.AddDays(-1 * (int)(COUNT / 2));

        // add 500 date values, and use the date and any random bool value
        for (int i = 0; i < COUNT; i++)
            x.Add(new COLOURS() { add_date = startDate.AddDays(i), is_new = i % 3 == 0 });

        return x.AsQueryable();
    }

But when I do this, the Take() method is always returning two (different) items each time - just as anyone would expect: