Fill Datatable from linq query

Question

i am using the below code

IEnumerable<DataRow> query = from c in at.appointmentcalendars.AsEnumerable() 
                             select c;

DataTable dt = query.CopyToDataTable();

But i am getting the below error

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<appointmentcalendar>' to 'System.Collections.Generic.IEnumerable<System.Data.DataRow>'. An explicit conversion exists (are you missing a cast?)

Twerp

The error tells you that your appointmentcalendar class is not able to be converted into a DataRow. You need to create a function that will do that conversion. – Rectum

system.data.linq.table – Twerp

can you please help me with the same, i am new to linq ... i thought it will be a easy go get the data from a linq query in a datatable – Twerp

Answer 1

Since the query returns an IEnumerable of Type DataRow, you have to specify what to insert into the datatable, in this case DataRow.

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

If you used

var query = //linq query of what you need for new datatable....
DataTable dt = query.CopyToDataTable();

Your table name is dt so select what you need from the original dt

var query = from c in db.something
            where c.othersomething == "onlyyouknow"
            orderby c.othersomething
            select new { NewObject = c.othersomething };

DataTable MyDataTable = new DataTable();
myDataTable.Columns.Add(
    new DataColumn()
    {
        DataType = System.Type.GetType("System.String"),//or other type
        ColumnName = "Name"      //or other column name
    }
);

foreach (var element in query)
{
    var row = MyDataTable.NewRow();
    row["Name"] = element.NewObject;
    myDataTable.Rows.Add(row);
}

Fries

Answer 2

at.appointmentcalendars is not a DataTable. Thus your query returns collection of appointmentcalendar objects, which cannot be cast to a collection of DataRow.

You need to use CopyToDataTable method from MSDN article How to: Implement CopyToDataTable Where the Generic Type T Is Not a DataRow:

IEnumerable<appointmentcalendar> query = at.appointmentcalendars.AsEnumerable();
DataTable dt = query.CopyToDataTable();

Default CopyToDataTable() method works only with collection of DataRow objects, so you can't use it here.

Elisa

Answer 3

If some one need the solution in VB.net and with Aggregate Functions:

    Dim MyDataTable As DataTable = New DataTable
    MyDataTable.Columns.Add("ProviderName", GetType(String))
    MyDataTable.Columns.Add("TotalNumSale", GetType(Integer))
    MyDataTable.Columns.Add("TotalValSale", GetType(Double))

    Dim testquery = (From p In adviceProductData _
                                         Where p.IsOffPanel = False _
                                   Group By p.ProviderName _
                                   Into _
                                   totalNoOfSale = Sum(p.NoOfSales), _
                                   totalValueOfSale = Sum(p.ValueOfSales)
                                   Select New With {
                                       .ProvName = ProviderName,
                                        .TotSale = totalNoOfSale,
                                       .TotVal = totalValueOfSale
                                       }).ToList()

    Dim item
    For Each item In testquery
        Dim row = MyDataTable.NewRow()
        row("ProviderName") = item.ProvName
        row("TotalNumSale") = item.TotSale
        row("TotalValSale") = item.TotVal
        MyDataTable.Rows.Add(row)
    Next

Ruddie

in order to share your knowledge you can create your own question + answer (it's completely fine on SO) and link it to this one – Attrition

Answer 4

DataTable getListRow(DataTable dt, int intSndBCode, int intPostManSCode)
    {
        IEnumerable<DataRow> results = (from MyRows in dt.AsEnumerable()
                       where
                       MyRows.Field<int>("m_sndBCode") == intSndBCode
                       &&
                       MyRows.Field<int>("m_postManSCode") == intPostManSCode
                       select MyRows);
        DataTable dtNew = results.CopyToDataTable();
        return dtNew;
    }

Hexosan