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 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: