CodenameCain CodenameCain - 1 year ago 133
C# Question

How to use LINQ query with DataGridView but a subset of table columns?

I am trying to learn from a code example from MSDN, but I clearly do not understand the true workings of LINQ. What I wish to do is populate a DataGridView for a "lookup window" with a subset of the available columns in a table from a strongly typed dataset. I am attempting the following:

IEnumerable<DataRow> query =
(from t in DataAccess.ds.GL40200.AsEnumerable()
where t.SGMTNUMB == 3
select new { t.SGMNTID, t.DSCRIPTN });

DataTable myTable = query.CopyToDataTable<DataRow>();

dgv_Exhibitors.DataSource = myTable;

However, this complains of an implicit conversion error from

I tried to add the following, but it results in a runtime error:

select new { t.SGMNTID, t.DSCRIPTN }).Cast<DataRow>();

While selecting just 't' works, the table contains columns I do not want to show to the user:

where t.SGMTNUMB == 3
select t;

Can you help with with an easy way to populate the DataGridView with just the two desired columns? If there is a perceived better or easier way than using LINQ I am open to recommendations.

Answer Source

It fails because your select statement is creating an anonymous class with two fields in it. It's no longer a DataRow, nor does the compiler know how to cast it back to a DataRow via Cast<T>().

One option is to stick with the method that works, then simply hide the columns you don't want to see in the DataGridView:

dgv_Exhibitors.DataSource =
    DataAccess.ds.GL40200.AsEnumerable().Where(t => t.SGMTNUMB == 3).CopyToDataTable();

foreach (var col in dgv_Exhibitors.Columns.Cast<DataGridViewColumn>()
                                  .Where(c => c.Name != "SGMNTID" && c.Name != "DSCRIPTN"))
    col.Visible = false;

Alternatively, set AutoGenerateColumns = false and then just define the columns you want to see and add them to the DataGridView.Columns collection.

Another option is to use your existing method, but call ToList() on it, and don't bother trying to convert it back to a DataTable. The DataGridView can display any collection:

dgv_Exhibitors.DataSource = (from t in DataAccess.ds.GL40200.AsEnumerable()
                             where t.SGMTNUMB == 3 
                             select new { t.SGMNTID, t.DSCRIPTN }).ToList();