C# Linq-SQL query result returning nothing

Question

I'm fairly new to LINQ (and SQL at that). When trying to query my SQL Database in C# "Supervisors" (which contains only one column "Names" made of nvarchar(50) variables, none of which are Null), supvName ends up being an empty list. If I don't cast it as a List, supvName is of typeSystem.Data.EnumerableRowCollection<string> if that helps.

public addNewEmp()
{
    InitializeComponent();
    using (TestDataSet db = new TestDataSet())
    {
        var supvName = (from n in db.Supervisors
                        select n.Names).ToList();
        foreach (string n in supvName)
        {
            supv_cbox.Items.Add(n);
        }
    }
}

Even when using a Where statement, that one result doesn't show up, so I'm sure it's something simple in my code that I just can't seem to figure out. I've already tried using AsEnumerable() which didn't change anything.

EDIT: I'm doing this in VS 2010 WPF. Also when I Preview Data in TestDataSet.xsd, it does return the all the data in the Database.

Solution: The problem was when I used a DataSet. When I used a DataContext instead it worked perfectly fine. Thanks to your DataSet or DataContext question lazyberezovsky or I never would have tried that. Using the following works:

var supvName = db.Supervisors.Select(m => m.Names);
supv_cbox.ItemsSource = supvName;

Thanks Surjah Singh too.

Answer 1

When you are enumerating over DataTable with Linq to DataSet, you should call AsEnumerable() on datatable and use Field<T> extension to get column value:

 var supvName = (from r in db.Supervisors.AsEnumerable()
                 select r.Field<string>("Names")).ToList();

BTW query variable r will be of DataRow type.

---

Your code can be simplified to:

 var names = db.Supervisors.AsEnumerable().Select(r => r.Field<string>("Names"));
 supv_cbox.DataSource = names.ToList();