convert linq query results to datatable C#

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

  }

Method 2

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

Method 3

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;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0