CODEWITHSUNDEEP
c#linqsqlite
Trey Balut
Trey Balut

Reputation: 1395

Select N Random Records from SQLite C#

I want to select 20 Random records from a SQLite table of 100 records.

Here is my code and I'm getting the following error. $exception {"Order By does not support: x => NewGuid()"} System.NotSupportedException

int tempRun = 10;
        var dbPath = Path.Combine("OPS.db");
        using (var db = new SQLite.SQLiteConnection(dbPath))
        {
            var rec = db.Table<CoversData>().Where(p => p.homeR >= tempRun).Take(15);

            var randomrec = db.Table<CoversData>().OrderBy(x => Guid.NewGuid()).Take(15);
            // error {"Order By does not support: x => NewGuid()"}  System.NotSupportedException

            foreach (CoversData cd in rec)
            {
                ResultsListBox.Items.Add(cd.Id.ToString() + "  " + cd.GameDate + "  " + cd.HometeamName + "  " + cd.homeR.ToString());
            }
        }

The answer should be using the Linq format not SQL Select statement.

Upvotes: 1

Views: 444

Answers (1)

PaulF
PaulF

Reputation: 6773

The Table<T>() method of the SQLiteConnection class returns a TableQuery<T>; - see http://www.rbdocumentation.com/html/8594fc64-ce81-faa6-1472-25dcdb59a1ce.htm.

The NewGuid not supported exception is due to the OrderBy being treated as an SQLite query command - creating a new Guid instance is done at the .Net application level, it is not a SQLite function.

As you only have 100 records in your table, you could try reading all of the table data & then ordering the results at the application level. Something like :

 var randomrec = db.Table<CoversData>().ToList().OrderBy(x => Guid.NewGuid()).Take(15);

or

 var randomrec = db.Table<CoversData>().Where(x => true).OrderBy(x => Guid.NewGuid()).Take(15);

Upvotes: 3

Related Questions