"Cannot Resolve Symbol Select" Trying to use LINQ with DataRowCollection

I'm 开发者_JAVA百科trying to use LINQ to iterate over the rows in a DataTable, and instantiate objects from the column values. The tricksy code is shown below.

The problem is that the code won't even compile. The nefarious error message is "cannot resolve symbol Select".

I'm satisfied that DataRowCollection implements IEnumerable (it gets it from System.Data.InternalDataCollectionBase), so you would think there'd be no problem with the following (obviously, I'm wrong on that count).

I have included System.Linq System.Data, and System.Data.SqlClient in my project. It also has all the necessary references. I've been using LINQ throughout the app before (mostly with lists of POCOs and XDocuemnts), and this is the first time I've seen this particular message.

Any advice on how to resolve it?

using (var command = connection.CreateCommand())
{
    command.CommandText = "dbo.sp_pTicklerContacts_sel_W_ContactRole_by_ComKey";
    command.CommandTimeout = 120;
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@iComKey", SqlDbType.Int).Value = companyKey;

    using (var adapter = new SqlDataAdapter(command))
    {
        var dataset = new DataSet();
        adapter.Fill(dataset);
        if (dataset.TableCount() > 0 && dataset.Tables[0].Rows.Count > 0)
        {
            return (from row in dataset.Tables[0].Rows
                    select new TicklerContact
                               {
                                   CompanyKey = row.ToInt32("iTicklerContact"),
                                   Contact = row.ToString("ccontact"),
                                   ContactKey = row.ToInt32("iconkey"),
                                   TicklerContactKey = row.ToInt32("iTicklerContactKey"),
                                   Role = row.ToString("contactrole"),
                                   Exists = row.ToBool("contactexists")
                                 }).ToList();

        }
        return null;
    }
}

---

I don't think you can use Linq over DataSet rows since it doesn't implement IEnumerable<T>. If you add a reference to System.Data.DataSetExtensions to your project you can use an extension method though, that allows you this:

 return (from row in dataset.Tables[0].AsEnumerable()
 ...

Also see this previous answer on SO: LINQ query on a DataTable

From the msdn article for DataTableExtensions.AsEnumerable:

Language-Integrated Query (LINQ) queries work on data sources that implement the IEnumerable interface or the IQueryable interface. The DataTable class does not implement either interface, so you must call the AsEnumerable method to use the DataTable as a source in the From clause of a LINQ query. You can also obtain custom, domain-specific operators, such as CopyToDataTable, by returning an IEnumerable object.

---

Maybe you need to help LINQ out on this one. It cannot resolve the type of row?

Try:

return (from row as DataRow in dataset.Tables[0].Rows
                select new TicklerContact
                           {
                               CompanyKey = row.ToInt32("iTicklerContact"),
                               Contact = row.ToString("ccontact"),
                               ContactKey = row.ToInt32("iconkey"),
                               TicklerContactKey = row.ToInt32("iTicklerContactKey"),
                               Role = row.ToString("contactrole"),
                               Exists = row.ToBool("contactexists")
                             }).ToList();

EDIT

Stupid VB / C# brain smash :P

Try:

return (from DataRow row in dataset.Tables[0].Rows
                select new TicklerContact
                           {
                               CompanyKey = row.ToInt32("iTicklerContact"),
                               Contact = row.ToString("ccontact"),
                               ContactKey = row.ToInt32("iconkey"),
                               TicklerContactKey = row.ToInt32("iTicklerContactKey"),
                               Role = row.ToString("contactrole"),
                               Exists = row.ToBool("contactexists")
                             }).ToList();