Binding LINQ query to DataGridView

Question

This is very confusing, I use AsDataView to bind query result to a dgv and it works fine with the following:

var query = from c in myDatabaseDataSet.Diamond where c.p_Id == p_Id select c;
dataGridView1.DataSource = query.AsDataView();

However, this one results in an Error:

var query = from item in myDatabaseDataSet.Items
    where item.p_Id == p_Id
    join diamond in myDatabaseDataSet.Diamond
        on item.p_Id equals diamond.p_Id
    join category in myDatabaseDataSet.DiamondCategory
        on diamond.dc_Id equals category.dc_Id
    select new
    {
        Product = item.p_Name,
        Weight = diamond.d_Weight,
        Category = category.dc_Name
    };

    dataGridView1.DataSource = query.AsDataView();

Error:

Instance argument: cannot convert from
'System.Collections.Generic.IEnumerable<AnonymousType#1>' to 
'System.Data.DataTable'

AsDataView doesn't show up in query.(List). Why is this happen? How to bind the query above to the dgv then?.

Solution 1

The signature of the AsDataView is as follows:

public static DataView AsDataView(
    this DataTable table
)

The only parameter is the DataTable.

The query you have is returning an IEnumerable of an anonymous type which doesn't have an implicit conversion to a DataTable (or a sequence of DataRow instances, in which case you could use that to help you create a DataTable).

You need to get the results back into a DataTable or something you can convert into a DataTable and then it will work.

In your particular case, it seems that you were (or are) using typed DataSets. If that is the case, then you should be able to take the values that were selected and then create new typed DataRow instances (there should be factory methods for you) which can then be put into a typed DataTable, which AsDataView can be called on.

Solution 2

just simply convert the result to a list and bind it to your grid.

var query = from item in myDatabaseDataSet.Items
    where item.p_Id == p_Id
    join diamond in myDatabaseDataSet.Diamond
        on item.p_Id equals diamond.p_Id
    join category in myDatabaseDataSet.DiamondCategory
        on diamond.dc_Id equals category.dc_Id
    select new
    {
        Product = item.p_Name,
        Weight = diamond.d_Weight,
        Category = category.dc_Name
    }.ToList();
dataGridView1.DataSource = query;

Comments

[DanSogaard]

Thanks but I was looking for an easy way, some of these ready-made methods don't work. So I guess I'll be doing it with SqlCeDataReader instead.

[casperOne]

@DanSogaard: I've updated my answer to reflect the fact you are using typed data sets and how you might still be able to use the AsDataView extension method.