C# ComboBox to ComboBox cascade filtering DataTable versus Entity Framework Core
Introduction
This article will provide insight for cascade filtering multiple ComboBox controls in Windows Forms project moving from conventional containers, DataTable to using Entity Framework Core.
Update 08/2022 Source code has been updated in GitHub repository
What is cascade filtering
Cascade filtering is selecting in a primary ComboBox then presenting items related to the first ComboBox. For example, the primary ComboBox provides categories of products while the second ComboBox provides a list of products in the category in the primary ComboBox. Once understanding working with two ComboBox controls others can be added e.g. categories for clothing in the primary ComboBox, products in the second ComboBox while a third list sizes. The principles work with both ComboBox and ListBox alike.
Conventional cascade filtering
Windows form developers gravitate to using DataTable containers for storing data received from querying data from tables in a database. The database used (SQL-Server, Microsoft Access, Oracle etc.) does not matter as once a connection and command have executed the query data is placed into a DataTable which has no knowledge how data was populated into the DataTable.
In the following class data is read from a category table for the primary ComboBox followed by reading a product table for the secondary ComboBox.
using System;
using System.Data;
using System.Data.SqlClient;
using BaseConnectionLibrary.ConnectionClasses;
namespace ComboBoxToComboBoxSqlServer.Classes
{
public class DataOperations : SqlServerConnection
{
public DataOperations()
{
DatabaseServer = ".\\SQLEXPRESS";
if (Environment.UserName.ToLower().Contains("karen"))
{
DatabaseServer = "KARENS-PC";
}
DefaultCatalog = "NorthWindAzure";
}
public DataTable CategoryDataTable()
{
var dt = new DataTable();
using (var cn = new SqlConnection(ConnectionString))
{
using (var cmd = new SqlCommand() {Connection = cn})
{
var selectStatement =
"SELECT CategoryId, CategoryName " +
"FROM Categories ORDER BY CategoryName";
cmd.CommandText = selectStatement;
cn.Open();
dt.Load(cmd.ExecuteReader());
}
}
return dt;
}
public DataTable ProductDataTable()
{
var dt = new DataTable();
using (var cn = new SqlConnection(ConnectionString))
{
using (var cmd = new SqlCommand() { Connection = cn })
{
var selectStatement =
"SELECT ProductId,CategoryId, ProductName " +
"FROM Products ORDER BY ProductName";
cmd.CommandText = selectStatement;
cn.Open();
dt.Load(cmd.ExecuteReader());
}
}
return dt;
}
}
}
The form which consumes the data has two ComboBox controls, one for categories and one for products. A form level BindingSource component is used for applying a filter against the current selection in the category ComboBox.
Form level variable
readonly BindingSource _productBindingSource = new BindingSource();
In form Load event (also works in form Shown event too) each ComboBox DisplayMember is set which represents text to display then each ComboBox DataSource is set to the appropriate DataTable.
private void Form1_Load(object sender, EventArgs e)
{
var ops = new DataOperations();
CategoryComboBox.DisplayMember = "CategoryName";
CategoryComboBox.DataSource = ops.CategoryDataTable();
ProductComboBox.DisplayMember = "ProductName";
_productBindingSource.DataSource = ops.ProductDataTable();
ProductComboBox.DataSource = _productBindingSource;
}
To filter products for the selected category, in SelectedInexChanged event of the Category ComboBox the SelectedItem is cast to a DataRow followed by getting the primary key for the current category were the key is used to filter the BindingSource component for the products ComboBox.
private void CategoryComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
if (CategoryComboBox.SelectedItem == null) return;
var categoryIdentifier = ((DataRowView)CategoryComboBox.SelectedItem).Row.Field<int>("CategoryId");
_productBindingSource.Filter = $"CategoryId = {categoryIdentifier}";
}
Result
Achieving filtering with DataTable contains is easy to implement. When it's time to get the product simply use the same technique used to get the category primary key in SelectedIndexChanged event of the category ComboBox.
Entity Framework cascade filter version 1
For those who have never worked with Entity Framework before and are used to the simplicity working with DataTable containers will note that at first glance implementing the same cascade filtering involves considerably more code to setup reading data followed by more code to implement the actual cascading filter. Once spending time with Entity Framework, a student, hobbyist or developer will see the initial setup is easy using a process known as scaffolding. Unlike having a wizard to scaffold with Entity Framework 6, Visual Studio does not support Entity Framework Core scaffolding but there are tools such as this one which requires typing in server name, database, selecting table and go. By using a tool to scaffold makes life easier actually then working with a connection, command and DataTable container.
In the following example two classes represent data for the ComboBox controls, Category and Product (an option is to pluralize class names when generating models which was not done in this case).
Note that to load data a DbContext is required which in the code is a private form level variable along with a BindingList for a container for products and another BindingList for filtered products. The idea for two BindingList is that the data is read only once and filtered each time the selected index changes in the category ComboBox.
public partial class Form1 : Form
{
private readonly NorthContext _context = new NorthContext();
private BindingList<Product> _productsBindingList = new BindingList<Product>();
private BindingList<Product> _productsBindingListFilter = new BindingList<Product>();
ComboBox controls are setup in form shown event.
private void Form1_Shown(object sender, EventArgs e)
{
List<Category> categories = _context.Categories.AsNoTracking()
.OrderBy(cat => cat.CategoryName).ToList();
CategoryComboBox.DataSource = categories;
_productsBindingList = new BindingList<Product>(_context.Products.AsNoTracking()
.OrderBy(prod => prod.ProductName).ToList());
_productsBindingListFilter = new BindingList<Product>(
_productsBindingList.Where(product =>
product.CategoryId == categories.FirstOrDefault()?.CategoryId).ToList());
ProductComboBox.DataSource = _productsBindingListFilter;
}
For filtering, in the SelectedIndexChanged event for the category ComboBox the current category primary key is obtained by casting the SelectedItem to type Category to access the key which is when used to query the BindingList that is the keeper of the categories into a second BindingList which becomes the DataSource for the Product ComboBox. Since all data never needs to be re-read from the database the process is lightening spped.
private void CategoryComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
var categoryIdentifier = ((Category)CategoryComboBox.SelectedItem).CategoryId;
_productsBindingListFilter = new BindingList<Product>(
_productsBindingList.Where(product => product.CategoryId == categoryIdentifier).ToList());
ProductComboBox.DataSource = _productsBindingListFilter;
}
That's all there is to cascade filtering.
Entity Framework cascade filter version 2
Students, hobbyistâs and developers when working with something new tend to find the simplest solution and move on which in some cases will come back to bite them and may very well if the code above is adapted without understanding what may happened when used with larger reference tables coupled with other controls in the mix such as DataGridView, ListView, TextBox controls. The issue is that related data and primary data may be read too, for example, in the Category class there is a Picture of type byte array which is not needed. Then there is misuse of eager or lazy loading which comes down to how well the developer has studied Entity Framework Core.
In the revised version, the Category class is replaced with CategoryItem class which contains properties enough to populate the ComboBox, no navigation properties or image property. In the later code sample the DbContext was set as a form level variable which in the context of cascade filtering is not needed so the reading of data has been moved to a class as per below which uses a local scoped DbContext for each method reading for categories and products. Note each method is static so there is no reason to create an instance of the class.
using System.Collections.Generic;
using System.Linq;
using NorthWindEntityCore.Contexts;
using NorthWindEntityCore.Models;
namespace NorthWindEntityCore.Classes
{
public class LoadReferenceData
{
public static List<Category> Categories()
{
using (var context = new NorthContext())
{
return context.Categories
.OrderBy(category => category.CategoryName).ToList();
}
}
public static List<CategoryItem> CategoryItems()
{
using (var context = new NorthContext())
{
return context.Categories.Select(category => new CategoryItem()
{
CategoryId = category.CategoryId,
CategoryName = category.CategoryName
}) .OrderBy(category => category.CategoryName).ToList();
}
}
public static List<Product> Products()
{
using (var context = new NorthContext())
{
return context.Products
.OrderBy(product => product.ProductName).ToList();
}
}
}
}
In Form Shown event to setup the ComboBox controls.
List<CategoryItem> categories = LoadReferenceData.CategoryItems();
CategoryComboBox.DataSource = categories;
_productsBindingList = new BindingList<Product>(LoadReferenceData.Products());
SetProductFilter((int)categories.FirstOrDefault()?.CategoryId);
ProductComboBox.DataSource = _productsBindingListFilter;
SetProductFilter encapsulates logic to filter the product ComboBox in form shown and the SelectedIndexChanged event for the category ComboBox while in the prior code sample this logic was repeated.
private void SetProductFilter(int categoryIdentifier)
{
_productsBindingListFilter = new BindingList<Product>(
_productsBindingList
.Where(product => product.CategoryId == categoryIdentifier).ToList());
}
To get the selected items in this example or the first example.
var currentCategory = ((Category) CategoryComboBox.SelectedItem);
var currentProduct = (Product)ProductComboBox.SelectedItem;
if (currentProduct.UnitPrice != null)
{
SelectionTextBox.Text =
$"Category: {currentCategory.CategoryId},{currentCategory.CategoryName} " +
$"Product: {currentProduct.ProductId}, {currentProduct.ProductName} at {currentProduct.UnitPrice.Value:C}";
}
else
{
SelectionTextBox.Text =
$"Category: {currentCategory.CategoryId},{currentCategory.CategoryName} " +
$"Product: {currentProduct.ProductId}, {currentProduct.ProductName}";
}
Summary
By following the article and code samples provided cascade filtering is easy to achieve in your Windows form application.
Running supplied code samples
- Open the data script either in a .sql file in Visual Studio or in SSMS (SQL-Server Management Studio) included in the GitHub source repository, inspect the path as the path may be different than on the current computer.
- Run the script, verify tables have been populated. Note that there are more tables than category and product table which provide a decent foundation for learning more about Entity Framework Core as many sample databases are either not enough or too much.
- If the default SQL-Server name is SQLEXPRESS build the solution while if the name is different change it here and here then build the solution.
See also
Entity Framework Core shadow properties (C#)
Entity Framework Core/Windows Forms tips and tricks
Windows Forms: Entity Framework Core Reverse Engineering databases
Entity Framework Windows Form validation
Source code
Resides in the following GitHub repository