How to set autocomplete feature from database using LINQ?

Question

I want to set the autocomplete feature to the textbox using LINQ. I already did in one way with sqlCommands. The code is

  OleDbCommand cmdinst = new OleDbCommand("select distinct cst_City from cstTable", con);
  OleDbDataReader drinst = cmdinst.ExecuteReader();
  AutoCompleteStringCollection instcol = new AutoCompleteStringCollection();
  while (drinst.Read())
  {
      instcol.Add(drinst.GetString(0));
  }
  txtCity.AutoCompleteCustomSource = instcol;

With this I can add the autocomplecustom source to the textbox. Now I want to add the same feature with LINQ. Please any one help me..

Answer 1

Adding strings to AutoCompleteStringCollection one by one is not efficient. Because when each string is added inner array list ensures its capacity and resizes storage (makes it two times bigger) if there is not enough space for new string. Also fore each added string CollectionChangedEvent will try to fire. When you adding values via AddRange storage is resized only once, and CollectionChangedEvent is fired only once.

Also you can simply apply Distinct operator, instead of grouping and selecting first group.

var db = FooDataContext();
var cities = db.cstTable.Select(c => c.cst_City).Distinct().ToArray();

AutoCompleteStringCollection instcol = new AutoCompleteStringCollection();
instcol.AddRange(cities);
txtCity.AutoCompleteCustomSource = instcol;