Quick binding with LINQ and anonymous types

I have a requirement to create a search screen with search criteria that are populated dynamically from the full table. In other words I have a table with 1000 rows that contains 15 distinct values in a specific column (say, JobTitle). Populate a drop down list with those 15 values. This would imply bad design if the table was a datatable, but it would be OK against a view.

I could do it with a call to the data base specifying the DISTINCT keyword and bind my drop down list to the results, but LINQ has a distinct method so I could utilize that against the entire data table.

I don't really like this approach because you end up with LINQ generic enumerables that are difficult to navigate. But it does work up to a point.

I wrote this demo against the AdventureWorks database from Microsoft. Start a new WPF application using Framework 4.0 or later and C#. I'm using Visual Studio 2019. Call the solution BindToAnonymousClasses.

The MainWindow simply defines a drop down list and a data grid. The application starts by loading a data table of Employees from the AdventureWorks' vEmployee view and populating the drop down list with distinct job titles. Whenever the user selects a job title, all the employees with that job title are listed in the grid. The XAML is trivial.

 <Window x:Class="BindToAnonymousClasses.MainWindow"

        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"

        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"

        xmlns:local="clr-namespace:BindToAnonymousClasses"

        mc:Ignorable="d"

        DataContext="{Binding RelativeSource={RelativeSource Self}}"

        Title="MainWindow" SizeToContent="WidthAndHeight">

    <StackPanel Orientation="Vertical">

        <ComboBox ItemsSource="{Binding JobTitles}" DisplayMemberPath="JobTitle" SelectedValuePath="JobTitle" SelectedValue="{Binding SelectedJobTitle}" IsEditable="False" Width="300"/>

        <DataGrid ItemsSource="{Binding DisplayEmployees}" AutoGenerateColumns="True" Height="200"/>

    </StackPanel>

</Window>

The code behind isn't very complex either.

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Windows;

namespace BindToAnonymousClasses

{

    public partial class MainWindow : Window, INotifyPropertyChanged

    {

        public DataTable Employees;

        public object DisplayEmployees { get; set; }

        public object JobTitles { get; set; }

        private string _SelectedJobTitle;

        public string SelectedJobTitle

        {

            get { return _SelectedJobTitle; }

            set

            {

                _SelectedJobTitle = value;

                DisplayEmployees = from e in Employees.Select("JobTitle='" + SelectedJobTitle + "'")

                                   select new { FirstName = e["FirstName"], LastName = e["LastName"], JobTitle = e["JobTitle"] };

                NotifyPropertyChanged("DisplayEmployees");

            }

        }

        public MainWindow()

        {

            Employees = LoadEmployees();

            JobTitles = from jt in (Employees.Select().Select((r) => r["JobTitle"]).Distinct().OrderBy((jt) => jt))

                        select new { JobTitle = jt };

            InitializeComponent();

        }

        private DataTable LoadEmployees()

        {

            SqlDataAdapter da;

            DataTable dt = new DataTable();

            string sConn = "Server=localhost;Database=AdventureWorks2017;Trusted_Connection=True;";

            string sSQL = "SELECT * FROM [HumanResources].[vEmployee]";

            SqlConnection oConn;

            oConn = new SqlConnection(sConn);

            oConn.Open();

            da = new SqlDataAdapter(sSQL, oConn);

            da.Fill(dt);

            oConn.Close();

            return dt;

        }

        public event PropertyChangedEventHandler PropertyChanged;

        public void NotifyPropertyChanged(string name)

        {

            if (PropertyChanged != null)

                PropertyChanged(this, new PropertyChangedEventArgs(name));

        }

    }

}

The interesting lines are the ones that populate JobTitles and DisplayEmployees. Let's start with JobTitles first. We start by creating a list of distinct job titles. Remember, Employees is a datatable.

Convert the data table to an enumeration of data rows.
Employees.Select()

Then project the enumeration of data rows into an enumeration of objects containing job titles.
Employees.Select().Select((r) => r["JobTitle"])

Get an enumeration of distinct job titles.
Employees.Select().Select((r) => r["JobTitle"]).Distinct()

Sort the enumeration in ascending sequence
Employees.Select().Select((r) => r["JobTitle"]).Distinct().OrderBy((jt) => jt)

Once we have the list, we can select from it creating a new anonymous class with a single property called JobTitle. Note it is easiest to declare JobTitles as a var or object because the class has no name. WPF binding figures it out.

JobTitles = from jt in (Employees.Select().Select((r) => r["JobTitle"]).Distinct().OrderBy((jt) => jt))

                select new { JobTitle = jt };

We can use a similar technique whenever the selected job title changes to populate an enumerable of a class with multiple properties.

DisplayEmployees = from e in Employees.Select("JobTitle='" + SelectedJobTitle + "'")

                       select new { FirstName = e["FirstName"], LastName = e["LastName"], JobTitle = e["JobTitle"] };