Linq results to dataset

Question

I use the entity framework together with Linq to get my data out of the database. I would like to download this data in to an excel. The ExcelLibrary works with datasets. is it possible to get the Linq data in an dataset, so I can easily get the data in an excel?

I tried this, but of course this doesn't work.

    protected void btnExcelCheckListDownload_Click(object sender, EventArgs e)
    {
        DataSet dsTest = new DataSet();
        var db = new BillingEntities();
        var query = (from u in db.v_Checklist select u).AsQueryable();
        dsTest =  (DataSet)query.Select(u => u.NCR_ID).Distinct();
        ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", dsTest);
    }

Have you tried using `System.Data.DataTableExtensions.CopyToDataTable()` — Nilesh, Aug 16 '13 at 08:49
I tried: var query = from u in db.v_Checklist select u; DataTable table = query.CopyToDataTable(); CopyToDataTable doesn't exist. because query isn't a datarow... — Freddy, Aug 16 '13 at 09:00

score 2 · Answer 1 · answered Aug 16 '13 at 09:02

2

Try use CopyToDataTable

protected void btnExcelCheckListDownload_Click(object sender, EventArgs e)
{
    DataSet dsTest = new DataSet();
    var db = new BillingEntities();
    var query = (from u in db.v_Checklist select u).AsQueryable();
    DataTable dt =  query.CopyToDataTable();
    dsTest.Tables.Add(dt);
    ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", dsTest);
}

answered Aug 16 '13 at 09:02

Vimal CK

3,543
1
26
47

I get this error when I try copytodatatable. The type 'x.v_Checklist' cannot be used as type parameter 'T' in the generic type or method 'System.Data.DataTableExtensions.CopyToDataTable – Freddy Aug 16 '13 at 09:13

score 1 · Accepted Answer · answered Aug 16 '13 at 11:27

You can write your own ToDatatable extension using reflection. Something like following. This is just a smaple you might want to extend it as per your needs.

public static class ConvertToDatatable
{
    public static DataTable ToDataTable<T>(this IEnumerable<T> enumerable)
    {
        if (enumerable == null) throw new ArgumentException("enumerable");
        var dt = new DataTable();
        var es = enumerable as List<T> ?? enumerable.ToList();
        var first = es.First();
        if (first != null)
        {
            var props = first.GetType().GetProperties();
            foreach (var propertyInfo in props)
            {
                if (!propertyInfo.PropertyType.IsClass || propertyInfo.PropertyType.Name.Equals("String"))
                {
                    dt.Columns.Add(new DataColumn(propertyInfo.Name));
                }
            }
        }

        foreach (var e in es)
        {
            var props = e.GetType().GetProperties();
            DataRow dr = dt.NewRow();
            dt.Rows.Add(dr);
            foreach (var propertyInfo in props)
            {
                if (!propertyInfo.PropertyType.IsClass || propertyInfo.PropertyType.Name.Equals("String"))
                {
                    dr[propertyInfo.Name] = propertyInfo.GetValue(e);
                }
            }
        }

        return dt;
    }

Linq results to dataset

2 Answers2