2 ComboBoxes bound to 2 observablecollections, is there a slick way to filter combobox #2 based on my selected property of combobox #1

Question

I am working with VS2019 C# WinUI 3 Desktop. I will take the Market_ID from combobox #1 once selected and want to apply a filter to the already created observablecollection 'metricIdentification' with is bound to combobox #2.

I don't want to have to fetch from SQL again if I don't have to.

Also, is an observable collection the best item source for a combobox for this filter operation? Should I use some other collection like a list perhaps? Thanks again.

My code:

<Window
    x:Class="App_WinUI3_Combobox_Sandbox.MainWindow"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:local="using:App_WinUI3_Combobox_Sandbox"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
   
    mc:Ignorable="d">

    <StackPanel Orientation="Vertical">
        
        <TextBlock Text="TEST FOR COMBOBOX FILTERING" Style="{StaticResource HeaderTextBlockStyle}" Margin="30"/>
        
        <StackPanel Orientation="Horizontal" HorizontalAlignment="Left">
            
            <ComboBox x:Name="cmbMarketID"
                      Header="Combobox #1"
                      PlaceholderText="Select an item" 
                      Width="500" 
                      Margin="30,5,0,0"  
                      ItemsSource="{x:Bind marketIdent}" 
                      SelectionChanged="cmbMarketID_SelectionChanged" 
                      SelectedItem="{Binding Market_ID}">
                <ComboBox.ItemTemplate>
                    <DataTemplate>
                        <StackPanel Orientation="Horizontal">
                            <TextBlock Text="{Binding Market_ID}" Width="15" TextAlignment="Right"/>
                            <TextBlock Text="{Binding Product}" Width="145" Margin="0,0,10,0"/>
                            <TextBlock Text="{Binding Company}" Width="70" Margin="10,0,10,0"/>
                            <TextBlock Text="{Binding System}" Margin="10,0,10,0"/>
                        </StackPanel>
                    </DataTemplate>
                </ComboBox.ItemTemplate>
            </ComboBox>

            <StackPanel Margin="10">
                <TextBlock Style="{StaticResource BaseTextBlockStyle}">Combobox <LineBreak/> Item <LineBreak/> Count </TextBlock>
                <TextBlock x:Name="cmbMarketID_Item_Count" Margin="10"/>
            </StackPanel>
        
            <ComboBox x:Name="cmbMetricName"
                      Header="Combobox #2"
                      PlaceholderText="Select an item" 
                      Width="500" 
                      Margin="30,5,0,0" 
                      ItemsSource="{x:Bind metricIdentification}" 
                      SelectionChanged="MetricName_SelectionChanged"
                      SelectedItem="{Binding Metric_ID}">
                <ComboBox.ItemTemplate>
                    <DataTemplate>
                        <StackPanel Orientation="Horizontal">
                            <TextBlock Text="{Binding Metric_ID}" Margin="0,0,10,0"/>
                            <TextBlock Text="{Binding Metric_Name}" />
                            <TextBlock Text="{Binding Market_ID}" Margin="10,0,0,0"/>
                        </StackPanel>
                    </DataTemplate>
                </ComboBox.ItemTemplate>
            </ComboBox>

            <StackPanel Margin="10">
                <TextBlock Style="{StaticResource BaseTextBlockStyle}">Combobox <LineBreak/> Item <LineBreak/> Count</TextBlock>
                <TextBlock x:Name="cmbMetricName_Item_Count" Margin="10"/>
            </StackPanel>

        </StackPanel>

        <TextBox x:Name="txtBoxProduct" Header="Product" Margin="30" IsReadOnly="True"/>
  
    </StackPanel>
</Window>

CODE BEHIND:

using Microsoft.UI.Xaml;
using Microsoft.UI.Xaml.Controls;
using Microsoft.UI.Xaml.Controls.Primitives;
using Microsoft.UI.Xaml.Data;
using Microsoft.UI.Xaml.Input;
using Microsoft.UI.Xaml.Media;
using Microsoft.UI.Xaml.Navigation;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices.WindowsRuntime;
using Windows.Foundation;
using Windows.Foundation.Collections;
using App_WinUI3_Combobox_Sandbox.Collections;
using System.Data;
using System.Data.SqlClient;

// To learn more about WinUI, the WinUI project structure,
// and more about our project templates, see: http://aka.ms/winui-project-info.

namespace App_WinUI3_Combobox_Sandbox
{
    /// <summary>
    /// An empty window that can be used on its own or navigated to within a Frame.
    /// </summary>
    /// 
    public sealed partial class MainWindow : Window
    {
        readonly ObservableCollection<Market>          marketIdent          = new ObservableCollection<Market>();
        readonly ObservableCollection<Ref_Metric_Main> metricIdentification = new ObservableCollection<Ref_Metric_Main>();
        

        public MainWindow()
        {
            this.InitializeComponent();

            // +----------------------------+
            // | POPULATE/SEED COMBO BOXES  |
            // | DISPLAY ITEM COUNT OF EACH |
            // +----------------------------+
            DataTable dt = new DataTable();
            DataTable dtRef_Metric_Main = new DataTable();

            SqlConnection con = new SqlConnection("Data Source = abc; Initial Catalog = xxx_Master; Integrated Security = True; Connect Timeout = 15; Encrypt = False; TrustServerCertificate = True; ApplicationIntent = ReadWrite; MultiSubnetFailover = False");

            SqlCommand cmd = new SqlCommand("select * from dbo.Metric_State_Codes", con);
            cmd.CommandType = CommandType.Text;
            con.Open();

            // MARKET ID DATA
            cmd.CommandText = "select * from Ref_Com_Sys_Dev";
            using (SqlDataAdapter adap = new SqlDataAdapter(cmd))
            {
                adap.Fill(dt);
                foreach (DataRow row in dt.Rows)
                {
                    marketIdent.Add(new Market() { Market_ID = (int)row["Market_ID"], Company = row["Company"].ToString(), System = row["System"].ToString(), Product = row["Product"].ToString(), Subproduct = row["Subproduct"].ToString() });
                }
            }

            cmbMarketID_Item_Count.Text = GetComboBoxItemCount(cmbMarketID).ToString();

            // METRIC NAME DATA
            //cmd.CommandText = "select * from Ref_Metric_Main_Dev";
            cmd.CommandText = "select r.*, m.Market_ID from Ref_Metric_Main_Dev r join dbo.Metric_Main m on r.Metric_ID = m.Metric_ID where m.Market_ID is not null";
            using (SqlDataAdapter adap = new SqlDataAdapter(cmd))
            {
                dtRef_Metric_Main.Clear();
                adap.Fill(dtRef_Metric_Main);
                foreach (DataRow row in dtRef_Metric_Main.Rows)
                {
                    metricIdentification.Add(new Ref_Metric_Main() { Metric_ID = row["Metric_ID"].ToString(), Metric_Name = row["Metric_Name"].ToString(), Market_ID = (int)row["Market_ID"] });
                }
            }
            cmd.Dispose();
            con.Close();

            cmbMetricName_Item_Count.Text = GetComboBoxItemCount(cmbMetricName).ToString();

        }

        private int GetComboBoxItemCount(ComboBox mycomboBox)
        {
            return mycomboBox.Items.Count;
        }
    
        private void cmbMarketID_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            cmbMarketID_Item_Count.Text = GetComboBoxItemCount(cmbMarketID).ToString();
            var item = cmbMarketID.SelectedItem as Market;
            txtBoxProduct.Text = $"{item.Product.ToString()} - Market ID: {item.Market_ID}";
            var result = metricIdentification.Where(w => w.Metric_ID.Equals("EMB02"));
        }

        private void MetricName_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            cmbMetricName_Item_Count.Text = GetComboBoxItemCount(cmbMetricName).ToString();
        }
    }
}

My test UI looks like this:

Thank you StackOverflow.

I would use `CollectionViewSource`. There are many articles about it. — Demetrius Axenowski, Mar 24 '22 at 09:52
Thank you @DemetriusAxenowski, I will look into CollectionViewSource. — LennyL, Mar 24 '22 at 17:57

Helrho · Answer 1 · 2022-03-25T08:10:18.353

0

So when the user choose an item in the first comboBox, it will be stored in Market_ID.
I think you can use accessors of this property to filter the second collection.

private List<Ref_Metric_Main> allMetricIdentification;
public ObservableCollection<Market> marketIdent {get; set;}
public ObservableCollection<Ref_Metric_Main> metricIdentification {get; set;}


private string _market_ID;
public string Market_ID
{
    get { return _market_ID; }
    set
    {
        _market_ID = value;

        metricIdentification.Clear();
        foreach(Ref_Metric_Main metric in allMetricIdentification
        {
            if (_market_ID == metric.Metric_ID) // Here your condition, on ID for example
            {
                metricIdentification.Add(metric);
            }
        }

    }
}

// ctor
public MainWindow()
{
    allMetricIdentification = new List<Ref_Metric_Main>();
    marketIdent = new ObservableCollection<Market>();
    metricIdentification = new ObservableCollection<Ref_Metric_Main>();
    
    // Your code here to populate allMetricIdentification and marketIdent
}

edited Mar 25 '22 at 08:10

answered Mar 24 '22 at 09:02

Helrho

46
7

Hi @Helrho, thank you very much. I am hoping something cool like this is possible. Is it correct that the code sample you provided should be in my class definition of Ref_Metric_Main where I have the property Market_ID defined? Like so: ' private int market_id; public int Market_ID { get { return market_id; } set { market_id = value; var result = metricIdentification.Where(w => w.Metric_ID.Equals("EMB02")); } }' Can you show the syntax to filter in the property? – LennyL Mar 24 '22 at 17:34
basically I need help adding the filter syntax that would apply to the OC in the set accessor. I apologize I am very new to C#. – LennyL Mar 24 '22 at 17:45
If you want to filter you need 2 collection. A list with the whole data, and an ObservableCollection with the filtered data. each time you choose an item in the first combobox, you will populate your observable collection with the data stored in the list – Helrho Mar 25 '22 at 07:59
You need 2 collection beacause after you have selected in first combobox, the list will be filtered but if you select again in first combox, you will filter again but in a already filtered data. So you need a list that always contains all the data you need. I have updated my code to make it easier to understand ! – Helrho Mar 25 '22 at 08:13
Thank you Helrho for the explanation, and the update to your code. – LennyL Mar 29 '22 at 05:19

score 0 · Answer 2 · answered Mar 25 '22 at 15:07

You could keep track of all items from the database in a separate collection and then simply add or remove items from the data bound one.

Add the items to both collections:

 readonly ObservableCollection<Ref_Metric_Main> metricIdentification = new ObservableCollection<Ref_Metric_Main>();
 readonly ObservableCollection<Ref_Metric_Main> allMetricIdentification = new ObservableCollection<Ref_Metric_Main>();

 public MainWindow()
 {
     ...
     using (SqlDataAdapter adap = new SqlDataAdapter(cmd))
     {
         ...
         foreach (DataRow row in dtRef_Metric_Main.Rows)
         {
             var item = new Ref_Metric_Main() { Metric_ID = row["Metric_ID"].ToString(), Metric_Name = row["Metric_Name"].ToString(), Market_ID = (int)row["Market_ID"] };
             metricIdentification.Add(item);
             allMetricIdentification.Add(item);
         }
     }
     ...
 }

Add/remove items from metricIdentification:

 private void cmbMarketID_SelectionChanged(object sender, SelectionChangedEventArgs e)
 {
     cmbMarketID_Item_Count.Text = GetComboBoxItemCount(cmbMarketID).ToString();
     var item = cmbMarketID.SelectedItem as Market;
     txtBoxProduct.Text = $"{item.Product.ToString()} - Market ID: {item.Market_ID}";

     var result = allmetricIdentifications.Where(w => w.Metric_ID.Equals("EMB02"));
     metricIdentification.Clear();
     foreach (var item in result)
         metricIdentification.Add(item);
 }

Hi mm8!, Thank you. I am going to re-examine my solution. I just saw your response today. — LennyL, Mar 29 '22 at 05:18

score -1 · Answer 3 · answered Mar 25 '22 at 23:59

I was able to get the combobox filtering to work this way:

    private void cmbMarketID_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
        var item = cmbMarketID.SelectedItem as Market;

        // This does a filter
        var result = metricIdentification.Where(w => w.Market_ID.Equals(item.Market_ID));

        // This applies the filter to the combobox
        cmbMetricName.ItemsSource = result;

    }

2 ComboBoxes bound to 2 observablecollections, is there a slick way to filter combobox #2 based on my selected property of combobox #1

3 Answers3