How to filter data in a list using multiple conditions and LINQ?

Question

I created this form to generate a list of students with the ability to filter by some criteria (on the left) and to display any information needed (from the right)

When the form is initializing at the start I am grabbing the whole student list with Entity Framework

List<Student> students = await context.Students.ToListAsync().ConfigureAwait(false);

And I am saving it into two lists:

private List<Student> _listOfAllStudents = new List<Student>();
private List<Student> _filteredStudents = new List<Student>();

Then I am performing my logic against the lists like this:

private void PrepareFilteredStudentListAccordingToFilterCheckedBoxes()
{
    _filteredStudents = _listOfAllStudents;

    if (ColonieFilterCheckBox.Checked)
    {
        _filteredStudents = _filteredStudents.Intersect(_listOfAllStudents.Where(x => x.Colonie).Select(x => x).ToList()).ToList();
    }

    if (NatationFilterCheckBox.Checked)
    {
        _filteredStudents = _filteredStudents.Intersect(_listOfAllStudents.Where(x => x.Nataion).Select(x => x).ToList()).ToList();
    }

    if (ExcursionFilterCheckBox.Checked)
    {
        _filteredStudents = _filteredStudents.Intersect(_listOfAllStudents.Where(x => x.Excursion).Select(x => x).ToList()).ToList();
    }

    //Rest of the code is omitted but you get the idea...
}  

Same logic is being done according to display checkboxes:

private void FillDataGridViewWithFilteredStudentAccordingToDisplayCheckBoxes()
{
    FilteredStudentDataGridView.Columns.Add("Id", "Numero");
    FilteredStudentDataGridView.Columns.Add("LastName", "Nom");
    FilteredStudentDataGridView.Columns.Add("FirstName", "Prenom");

    if (MiddleNameDisplayCheckBox.Checked)
    {
        FilteredStudentDataGridView.Columns.Add("MiddleName", "Nom Du Pere");
    }

    if (BirthdayDateDisplayCheckBox.Checked)
    {
        FilteredStudentDataGridView.Columns.Add("DateOfBirth", "Date De Naissance");
    }

    //Rest of the code omitted, but same concept.

    foreach (Student student in _filteredStudents)
    {
        List<object> rowsValues = new List<object>();
        foreach (object column in FilteredStudentDataGridView.Columns)
        {
            string columnName = ((DataGridViewTextBoxColumn)column).Name;

            if (columnName == "Id")
            {
                rowsValues.Add(student.StudentId);
            }

            if (columnName == "FirstName")
            {
                rowsValues.Add(student.FirstName);
            }
            //Code omitted.
        }
        object[] arrayRowsValues = rowsValues.ToArray();
        FilteredStudentDataGridView.Rows.Add(arrayRowsValues);
    }
}

I was wondering if there is a way to use LINQ instead of all those if blocks to filter the data according to my conditions?

Answer 1

I would definitely move the filtering to the database:

IQueryable<Student> studentQuery = context.Students;
if (ColonieFilterCheckBox.Checked) {
    studentQuery = studentQuery.Where(x => x.Colonie);
}

if (NatationFilterCheckBox.Checked) {
    studentQuery = studentQuery.Where(x => x.Nataion);
}

if (ExcursionFilterCheckBox.Checked) {
    studentQuery = studentQuery.Where(x => x.Excursion);
}

var _filteredStudents = await studentQuery.ToListAsync().ConfigureAwait(false);

If you prefer local filtering logic, you can combine the conditions a little or use Reflection to simplify the code but slow it down some.

For combined conditions, you can do

_filteredStudents = _listOfAllStudents
    .Where(x => (!ColonieFilterCheckBox.Checked || x.Colonie) &&
                (!NatationFilterCheckBox.Checked || x.Natation) &&
                (!ExcursionFilterCheckBox.Checked || x.Excursion)).ToList();

but that checks the CheckBoxs per Student.

Instead, using Reflection, you can build the code dynamically for the filter (again, assuming you name the CheckBox controls after the fields):

IEnumerable<Student> queryStudents = _listOfAllStudents;
var xParm = Expression.Parameter(typeof(Student));

foreach (var filterField in new[] { "Colonie", "Natation", "Excursion" }) {
    if (((CheckBox)Controls.Find($"{filterField}CheckBox")).Checked) {
        var whereLambda = (Expression<Func<Student, bool>>)Expression.Lambda(Expression.PropertyOrField(xParm, filterField), xParm);
        queryStudents = queryStudents.Where(whereLambda.Compile());
    }
}
_filteredStudents = queryStudents.ToList();

For your display logic, I would rename all the check boxes to match the data field names, and then use a lot of Reflection:

private void FillDataGridViewWithFilteredStudentAccordingToDisplayCheckBoxes() {
    var headerText = new Dictionary<string, string> { { "Id", "Numero" }, { "LastName", "Nom" }, { "FirstName", "Prenom" }, { "MiddleName", "Nom Du Pere" },
    { "DateOfBirth", "Date De Naissance" } };

    var viewColumns = new List<string> { "Id", "LastName", "FirstName" };

    foreach (var possibleColumn in headerText.Keys) {
        var displayColumns = Controls.Find(possibleColumn + "DisplayCheckBox", true);
        if (displayColumns.Length == 1) {
            if (((CheckBox)displayColumns[0]).Checked)
                viewColumns.Add(possibleColumn);
        }
    }

    //Rest of the code omitted, but same concept.

    foreach (var dataFieldName in viewColumns)
        FilteredStudentDataGridView.Columns.Add(dataFieldName, headerText[dataFieldName]);

    foreach (var student in _filteredStudents) {
        var studentType = student.GetType();
        var rowValues = new List<object>();
        foreach (var dataFieldName in viewColumns)
            rowValues.Add(studentType.GetProperty(dataFieldName).GetValue(student, null));

        FilteredStudentDataGridView.Rows.Add(rowValues.ToArray());
    }
}

Note that if you care about the order of the columns displayed, you will need some logic to order or sort the headerText.Keys. In my asp implementation of something similar, I just have a manual list of procedure calls with data names in the order that I want, and the procedure checks if the data item is to be displayed (in viewColumns), then adds the data and column header.