1

I am using the LinqToExcel project developed by MIT and hosted on Google Code at http://code.google.com/p/linqtoexcel/wiki/UsingLinqToExcel to parse my dataset from an excel table which looks something like this:

Part of the dataset

I'm trying to randomly pick 30 excel rows from my dataset containing area and length using the Linq OrderBy random number technique seen here:

public class Obj
{
    public double Area { get; set; }
    public double Length { get; set; }
}

namespace LinqToExcel.Screencast
{
    class Program
    {
        static void Main()
        {
            var excel = new ExcelQueryFactory();
            excel.FileName = @"C:\\radno\\jame.xls";

            Random rnd = new Random();

            var listExcel = from x in excel.Worksheet<Obj>()
                select x;

            var orderedList = listExcel.OrderBy(r => rnd.Next());

            foreach (var u in orderedList.Take(30))
            {
                Console.WriteLine($"the area is {u.Area} while length is {u.Length}");
            }
        }
    }
}

But it's not working because I always get the same first 30 pairs. Am I doing something wrong here? Should this work and is this a LinqToExcel bug?

I've tryed the list.OrderBy(x => Guid.NewGuid()) trick, but I get the same results.

Frets
  • 141
  • 2
  • 11
  • 1
    This is probably the same issue as you encounter with Linq-to-SQL, where your request (which is perfectly valid in C#) cannot be translated to the target language (SQL/whatever ExcelQueryFactory uses). But I haven't worked with ExcelQueryFactory before so I can't directly confirm it. – Flater Mar 18 '19 at 14:09

1 Answers1

0

It looks like OrderBy is able to work only when you point to exact column. I checked libary sources. If you go to : https://github.com/paulyoder/LinqToExcel/blob/master/src/LinqToExcel/Query/SqlGeneratorQueryModelVisitor.cs (line 113) You can see what is taken to OrderBy SQL. It ignores any method calls and saves only column name. It's not going to work in this approach. As a workaround you can do .ToList() before .OrderBy().

Raik
  • 185
  • 9
  • That's it, it worked! When I added `.ToList()` before `.OrderBy()` it worked. Care to explain what's happening under the hood when you add `.ToList()` in this case? Thank you. – Frets Mar 19 '19 at 06:44
  • ToList, makes an normal list object with elements. Before you make it, its an IQueryable from linqToExcel which doesnt support orderby like you tried. You should read some articles about IQueryable and IEnumerable – Raik Mar 19 '19 at 06:59