Help with LINQ to DataTable query

Question

I've successfully used OLEDB to import an excel file to a datatable and display it in a data grid view. Now I want to use LINQ and set the datasource for the grid as the LINQ query, however it is not working. Here is the full code:

OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Nick\Desktop\Pricing2.xlsx" + @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";

OleDbCommand command = new OleDbCommand
(
    "SELECT * FROM [Pricing$]",conn
);
DataTable dt = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(dt);
var query = from a in dt.AsEnumerable()
            select a;
dataGridView1.DataSource = query; 

Answer 1

Databinding works with ILists, not IEnumerables.

In order to bind to a LINQ query, you need to call ToList().
To bind to a LINQ-to-DataTable query (a LINQ query that returns DataRows), you need to call AsDataView() instead.
This returns an ITypedList implementation that will show properties for the columns in the table.

If you select an anonymous type (not a DataRow), you just need ToList().

Answer 2

.ToList() will not work, .CopyToDataTable() is the correct call.