convert linq query results to datatable C#

Question

I want to convert the linq query results to datatable so that I can assign datatable to GridView to show it on asp page.

However I am not able to convert the results to datatable, I am not getting CopyToTable() method in my code.

please advise what am I doing wrong here?

 var gradeData = (from data in oAngieCtxt.prc_ShopInstanceCustomersData(Convert.ToInt32(this.ShopInstanceID), 10000, false)
                     .Where( row => row.RecievedPoints != "n/a" )
                    .GroupBy(row => new { row.Name })
                    .Select(g => new GroupedPoints()
                    {
                        Name = g.Key.Name,
                        TotalPoints = g.Sum(x => Convert.ToDouble(x.RecievedPoints) * (x.Weightage.ToString() == "0.00" ? 1 : Convert.ToDouble(x.Weightage)))
                    })
                     select data).ToList();

 DataTable dt = gradeData --gradeData.CopyToTable()

Note: reference to dataextentions dll is available.

Thanks in advance

possible duplicate of [Convert generic List/Enumerable to DataTable?](http://stackoverflow.com/questions/564366/convert-generic-list-enumerable-to-datatable) — Satpal, Sep 04 '13 at 08:33

speti43 · Accepted Answer · 2013-09-04T12:33:56.923

6

You should get DataTableExtensions.CopyToDataTable

Remove ToList().

CopyToDataTable is an IEnumerable<DataRow> extension (unfortunately).

There is a solution with custom CopyToDataTable extension method below.

var gradeData = (from data in oAngieCtxt.prc_ShopInstanceCustomersData(
                 Convert.ToInt32(this.ShopInstanceID), 10000, false)
                .Where( row => row.RecievedPoints != "n/a" )
                .GroupBy(row => new { row.Name })
                .Select(g => new
                {
                    Name = g.Key.Name,
                    TotalPoints = g.Sum(x => Convert.ToDouble(x.RecievedPoints) 
                    * (x.Weightage.ToString() == "0.00" ? 1 
                      : Convert.ToDouble(x.Weightage)))
                })
                 select data);

var dt = gradeData.CopyToDataTable();

Edit:

Here is a more useful implementation of CopyToDataTable There is no type constraint to DataRow.

  public static class DataSetLinqOperators
  {
    public static DataTable CopyToDataTable<T>(this IEnumerable<T> source)
    {
        //you find the ObjectShredder implementation on the blog wich was linked.
        return new ObjectShredder<T>().Shred(source, null, null);
    }

    public static DataTable CopyToDataTable<T>(this IEnumerable<T> source, 
                                     DataTable table, LoadOption? options)
    {
        return new ObjectShredder<T>().Shred(source, table, options);
    }

  }

edited Sep 04 '13 at 12:33

answered Sep 04 '13 at 08:37

speti43

2,886
1
20
23

getting the error: Error 7 The type 'uc_report.GroupedPoints' cannot be used as type parameter 'T' in the generic type or method 'System.Data.DataTableExtensions.CopyToDataTable(System.Collections.Generic.IEnumerable)'. There is no implicit reference conversion from 'uc_report.GroupedPoints' to 'System.Data.DataRow'. C:\Users\DEV7\Documents\csdev\trunk\DWIZA\UserControls\stealth\report.ascx.cs 223 24 C:\...\DWIZA\ – mmssaann Sep 04 '13 at 08:42
try without the class anonymous way. Modified above. – speti43 Sep 04 '13 at 08:45
thanks for the reply anyways i cannot find any modifications,what did u modify? – mmssaann Sep 04 '13 at 08:46
new GroupedPoints(){} => new {} – speti43 Sep 04 '13 at 08:47
the problem, you need to have a IEnumerable() to use this method – speti43 Sep 04 '13 at 09:33
Later, I can find a good solution to your problem, but I don't have time now. – speti43 Sep 04 '13 at 09:54
Here is an implementation of a Custom CopyToDataTable extension method, which is more useful then original: http://blogs.msdn.com/b/aconrad/archive/2007/09/07/science-project.aspx implement it in a static class, and you can use it immediately with a simple IEnumerable. – speti43 Sep 04 '13 at 12:17

score 5 · Answer 2 · answered Sep 04 '13 at 08:37

First declare a new DataTable and add columns, in this :

DataTable dt = new DataTable();
dt.Columns.Add("FirstName");
dt.Columns.Add("LastName");
DataRow row = null;

Now I simply iterate through the query and fill a DataTable:

foreach (var rowObj in query)
{
    row = dt.NewRow();
    dt.Rows.Add(rowObj.FirstName, rowObj.LastName);
}

score 4 · Answer 3 · answered Sep 04 '13 at 12:56

If you want to have you own extension method then you could always do something like this:

    public static DataTable ToDataTable<T>(this IQueryable items)
    {
        Type type = typeof(T);

        var props = TypeDescriptor.GetProperties(type)
                                  .Cast<PropertyDescriptor>()
                                  .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
                                  .Where(propertyInfo => propertyInfo.IsReadOnly == false)
                                  .ToArray();

        var table = new DataTable();

        foreach (var propertyInfo in props)
        {
            table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
        }

        foreach (var item in items)
        {
            table.Rows.Add(props.Select(property => property.GetValue(item)).ToArray());
        }

        return table;
    }

You will need to reference both of these

using System.ComponentModel;
using System.Data;

convert linq query results to datatable C#

3 Answers3

Linked