Sandri Sandri - 6 months ago 20
SQL Question

Select random record from table with further conditions in C# code

I'm coding little process where I can select random record from table, where field with checkbox/boolean is also unchecked.
The moral of the story is to select random record, work with it and set checkbox/boolean field == Checked.
I have approach this work by selecting random record from Count() method, I got Integer in return and I can choose random number from that pool. But I also need to filter the query to select only records who have the checkbox/boolean field unchecked, but I haven't managed in the following code to go so far.
I have used Lambda expression as follows but maybe better way can exists.

public class Program
{
static void Main(string[] args)
{
DatabaseModelContainer mc = new DatabaseModelContainer();
MyTable myTable = new MyTable();
Random rnd = new Random();
int index = rnd.Next(myTable.Count);

string selectedRec = myTable.MyField.OrderBy(r => myTable.MyField).Take(index).ToString();

//Selected record to be shown at the screen, but will not appear (only reference point is shown)
Console.WriteLine(selectedRec);

//Selected random value is displayed here and that indeed worked, I get random value there, but I can't use the id to select record above based on that id.
Console.WriteLine(index.ToString());

Console.ReadLine();

}
}


Code above does NOT show who to choose random record WHERE CheckboxField == Checked, so that code needs to be added as well.
What approach is best?

Answer

Assuming your MyTable is a sequence, the correct way of doing it should be :

var randomRecord = myTable
    .Where(x => x.CheckboxField)
    .ToList()
    .OrderBy(x => rnd.Next())
    .FirstOrDefault();

Full Example:

See it here: https://dotnetfiddle.net/B4JLU0

using System;
using System.Collections.Generic;
using System.Linq;

public class Program
{

    class TableRow 
    {
        public int I {get;set;}
        public bool CheckboxField {get;set;}

        TableRow(int i)
        {
            this.I = i;
            this.CheckboxField = (i % 3 == 0); //in our demo, assume that some are already checked, some aren't
        }

        public static IEnumerable<TableRow> GenerateTableRows(int howMany)
        {
            for (int i=0; i<howMany; i++)
            {
                yield return new TableRow(i);
            }
        }

    }

    public static void Main()
    {
        new Program();
        Console.WriteLine("Done");
    }

    Program() 
    {
        ICollection<TableRow> myTable = new List<TableRow>(TableRow.GenerateTableRows(12));
        while (myTable.Count(x => !x.CheckboxField) > 0) //keep looping until we've processed all records
        {
            var randomRecord = GetRandomRecord(myTable);
            Console.WriteLine(randomRecord == null ? "No Matching Rows Exist" : randomRecord.I.ToString()); 
        }
    }

    TableRow GetRandomRecord(ICollection<TableRow> myTable)
    {
        var rnd = new Random(); //we could optionally provide this as a parameter too to avoid recreating each time
        var randomRecord = myTable
            .Where(x => !x.CheckboxField)
            .ToList()
            .OrderBy(x => rnd.Next())
            .FirstOrDefault();  
        if(randomRecord != null) //avoid issue if all items are checked
        {
            randomRecord.CheckboxField = true; //mark this record as having been processed
        }
        return randomRecord;
    }

}
Comments