CODEWITHSUNDEEP
c#linqdatagridviewstrongly-typed-dataset
CodenameCain
CodenameCain

Reputation: 573

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 EnumerableRowCollection<AnonymousType#1> to IEnumerable<System.Data.DataRow>.

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.

Upvotes: 1

Views: 3368

Answers (2)

The One
The One

Reputation: 4784

Expanding on Grant Winney's answer, use var instead of IEnumerable <DataRow> to receive the anonymous types.

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

And if you want the results in a Datatable, you'll have to create one and add the values one by one.

DataTable myTable = new DataTable();
myTable.Columns.Add("SGMNTID", typeof(int));
myTable.Columns.Add("DSCRIPTN", typeof(string));
foreach (var x in query)
        {
            DataRow dr = myTable.NewRow();
            dr[0] = x.SGMNTID;
            dr[1] = x.DSCRIPTN;
            myTable.Rows.Add(dr);
        }

dgv_Exhibitors.DataSource = myTable;

Upvotes: 0

Grant Winney
Grant Winney

Reputation: 66449

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();

Upvotes: 1

Related Questions