LINQ to DataTable Easy and Fast

Question

I am new to LINQ, I want to convert LINQ query to DataTable

    DataClassesDataContext db = new DataClassesDataContext(MyConncectionString);
    IEnumerable<DataRow> qry = (IEnumerable<DataRow>)(from tbl in db.Table1.AsEnumerable()
                                                     select tbl);
    DataTable dt=new DataTable();
    qry.CopyToDataTable(dt, LoadOption.OverwriteChanges);
    GridView1.DataSource = dt;

But I got error

Unable to cast object of type 'WhereSelectEnumerableIterator to type 'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'.

Can anyone please help me that

Answer 1

I think is unnecessary here to convert into linq? as u already got the datatable and you are linq-ing and again converting it back to linq. So unless you have valid reason to linq here, you should stick with data table.

     DataClassesDataContext db = new DataClassesDataContext(MyConncectionString);
     IEnumerable<DataRow> qry = (IEnumerable<DataRow>)(from tbl in db.Table1.AsEnumerable()
                                                 select tbl);
    DataTable dt=new DataTable();
    qry.CopyToDataTable(dt, LoadOption.OverwriteChanges);
    GridView1.DataSource = dt;

Should be,

    GridView1.DataSource = db.Table1

Answer 2

These are basic concepts:

There are several LINQ providers shipped with the .Net framework

• LINQ to Objects. System.Linq. It's the LINQ core and it is designed to work with in-memory collections

• LINQ to XML. System.Xml.Linq To work with XML documents

• LINQ to DataSet. To work with DataTable, and DataSet objects

• LINQ to SQL. System.Data.Linq It is used to work with SQL Server databases

• LINQ to Entities. (Entity Framework) Used to work with several databases. It provides a richer API

What I think you are trying to do is:

From a query returned from a LINQ to SQL context, you want to return a collection of DataRow objects.

There's no simple conversion between a LINQ to SQL object and a DataRow, so if you insist you would need to use reflection to get the desired output, Fastest way to fill DataTable from LINQ query using DataContext

Now if you really want to work with the DataTable objects, then my recommendation is to use a strongly typed DataSet. To create one, simply add a new DataSet object to your project and from the Server Explorer drag tables like you would do with your LINQ to SQL objects

If possible, evaluate the decision to use DataRow and instead return the objects generated by LINQ To SQL, or even better create your custom DTO objects

About your code, I don't see any reason to try to use a Datatable object (unless you are nto posting that extra code to justify this decision). If you just want to set the DataSource property of a control, simply do the following:

var q = from tbl in db.Table1
        select tbl;

this.myControl.DataSource = q;
this.myControl.DataBind();

If you need, you can transform the result into an anonymous object and used it as the DataSource:

var q = from tbl in db.Table1
        select new
        {
           MyNewProperty = tbl.ExistingProperty,
           AnotherProperty = tbl.MyCoolerProperty
        };

this.myControl.DataSource = q;
this.myControl.DataBind();