Binding SharePoint list columns with distinct values to a dropdownlist using LINQ and DataTable

Using DataTable

Using the DataTable and DataView we can sort the items, but we can not take the distinct values from the columns. We should explicitly remove the duplicates again by writing a separate method .

SPWeb web = SPContext.Current.Web;
SPList list = web.Lists["DocumentLibraryName"];
SPListItemCollection listItems = list.GetItems();

DataTable dtTable = list.Items.GetDataTable().DefaultView.ToTable(true, “ColumnName”);

DataView dvName = new DataView(dtTable);
dvName.Sort = “ColumnName”;

ddl.DataSource = dvName;
ddl.DataTextField = “ColumnName”;
ddl.DataValueField = “ColumnName”;
ddl.DataBind();

RemoveDuplicateItems(ref ddlBusinessUnits)

private void RemoveDuplicateItems(ref DropDownList ddl)
{
            try
           {
               for (int i = 0; i < ddl.Items.Count; i++)
                {
                    ddl.SelectedIndex = i;
                    string str = ddl.SelectedItem.ToString();
                    for (int counter = i + 1; counter < ddl.Items.Count; counter++)
                    {
                        ddl.SelectedIndex = counter;
                        string compareStr = ddl.SelectedItem.ToString();
                        if (str == compareStr)
                        {
                            ddl.Items.RemoveAt(counter);
                            counter = counter – 1;
                        }
                    }
                }

                for (int i = 0; i < ddl.Items.Count; i++)
                {
                    if ((ddl.Items[i].Text.Length == 0) || (ddl.Items[i].Value.Length == 0))
                    {
                        ListItem item = ddl.Items[i];
                        ddl.Items.Remove(item);
                        i = i – 1;
                    }
                }

                ddl.Items.Insert(0, new ListItem(“Select”, “”));
                ddl.SelectedIndex = 0;
            }
            catch { }
}

The above method using the data table will decrease the performance. It will take a lot of time when there are ‘n’ no. of items to be bind to the dropdown list.

Using LINQ

SPWeb web = SPContext.Current.Web;
SPList list = web.Lists[DocumentLibrary];
var objItems = (from item in (from listitem in list.GetItems().Cast()
                                       where
                                       listitem["ColumnName"] != null
                                       select new { value = listitem["ColumnName"].ToString() }).ToList().Distinct()
                      orderby item.value ascending
                      select item); 

ddl.DataSource = objItems ;
ddl.DataTextField = “value”;
ddl.DataValueField = “value”;
ddl.DataBind();
ddl.Items.Insert(0,new ListItem(“Select”,string.Empty));

That’s it. it will bind the distinct values of the column in the drop down in ascending order of the items. It reduces the lot of loading time when ‘n’ no. of items has to be loaded in the drop down.