Put LINQ query result to DataTable

Question

I have this query:

        var smallExchangeReport =  from ex in exchangeProgReport
                                   where !string.IsNullOrEmpty(ex.comment)
                                   group ex by new { ex.siteName } into g
                                   select new SummuryReportTraffic
                                   {
                                       siteName = g.Key.siteName,
                                       exchangeCounter = g.Where(x => x.Prog1ToProg2Check == 1).Count(),
                                       descriptions = (from t in g
                                                       group t by new { t.comment, t.siteName } into grp
                                                       select new Description
                                                       {
                                                           title = grp.Key.comment,
                                                           numbers = grp.Select(x => x.comment).Count()
                                                       })
                                   };

At some point I put it to the dataTable using foreach loop:

             foreach (var item in smallExchangeReport)
            {
                dr = smrTable.NewRow();

                foreach (var d in item.descriptions)
                {
                    dr[d.title] = d.numbers;
                }

                smrTable.Rows.Add(dr);
            }

But I need to put the LINQ result to dataTable without using foreach loop. So I made some changes to my code above according to this link:

        DataTable dt = new DataTable();
        DataRow dr = dt.NewRow();

        IEnumerable<DataRow> smallExchangeReport =  from ex in exchangeProgReport.AsEnumerable()
                                   where !string.IsNullOrEmpty(ex.comment)
                                   group ex by new { ex.siteName } into g
                                   select new 
                                   {
                                       siteName = g.Key.siteName,
                                       exchangeCounter = g.Where(x => x.Prog1ToProg2Check == 1).Count(),
                                       descriptions = (from t in g.AsEnumerable()
                                                       group t by new { t.comment, t.siteName } into grp
                                                       select new
                                                       {
                                                           title = grp.Key.comment,
                                                           numbers = grp.Select(x => x.comment).Count()
                                                       })
                                   };

    // Create a table from the query.
    DataTable boundTable = smallExchangeReport.CopyToDataTable<DataRow>();

But on changed LINQ query I get this error:

 Cannot implicitly convert type:'System.Collections.Generic.IEnumerable<<anonymous type: string siteName, int exchangeCounter>>' to 
     'System.Collections.Generic.IEnumerable<System.Data.DataRow>'. An explicit conversion exists (are you missing a cast?)

My question is how to cast the query to make it work?I tryed to cast to(DataRow) the result of the LINQ but it didn't worked.

Answer 1

In your LINQ query, you are trying to get IEnumerable<DataRow> as the result, but actually you select new objects of an anonymous type: select new { siteName = .... }. This cannot work because your anonymous type cannot be cast to DataRow.

What you need to do is use a function that would populate a DataRow like this:

DataRow PopulateDataRow(
    DataTable table, 
    string siteName, 
    int exchangeCounter, 
    IEnumerable<Description> descriptions
{
    var dr = table.NewRow();  

    // populate siteName and exchangeCounter
    // (not sure how your data row is structured, so I leave it to you)

    foreach (var d in descriptions)
    {
        dr[d.title] = d.numbers;
    }

    return dr;
}

then in your LINQ query, use it as follows:

IEnumerable<DataRow> smallExchangeReport =  
    from ex in exchangeProgReport.AsEnumerable()
    where !string.IsNullOrEmpty(ex.comment)
    group ex by new { ex.siteName } into g
    select PopulateDataRow(
        smrTable,
        siteName: g.Key.siteName,
        exchangeCounter: g.Where(x => x.Prog1ToProg2Check == 1).Count(),
        descriptions: (from t in g.AsEnumerable()
            group t by new { t.comment, t.siteName } into grp
            select new Description {
                title = grp.Key.comment,
                numbers = grp.Select(x => x.comment).Count()
            }
        )
    );

This solution gets rid of one foreach (on rows) and leaves the other one (on descriptions).

If removing the second foreach is important... I would still leave it inside PopulateDataRow. I don't see an elegant way to remove it. You can call a method from LINQ query which reads like a deterministic function, but actually creates the side effect of setting a column value on a data row, but it doesn't feel right to me.

Answer 2

this is can help you.

defining table structure.

DataTable tbl = new DataTable();
tbl.Columns.Add("Id");
tbl.Columns.Add("Name");

and we need to create datarow from anonymous type.

    Func<object, DataRow> createRow = (object data) =>
    {
        var row = tbl.NewRow();
        row.ItemArray = data.GetType().GetProperties().Select(a => a.GetValue(data)).ToArray();
        return row;
    };

test with fake query:

    var enumarate = Enumerable.Range(0, 10);
    var rows = from i in enumarate
               select createRow( new { Id = i, Name = Guid.NewGuid().ToString() });
    var dataTable  = rows.CopyToDataTable<DataRow>();

Answer 3

You can use this method:

    private DataTable ListToDataTable<T>(List<T> objs, string tableName) {
        var table = new DataTable(tableName);
        var lists = new List<List<object>>();
        // init columns
        var propertyInfos = new List<PropertyInfo>();
        foreach (var propertyInfo in typeof(T).GetProperties()) {
            propertyInfos.Add(propertyInfo);
            if(propertyInfo.PropertyType.IsEnum || propertyInfo.PropertyType.IsNullableEnum()) {
                table.Columns.Add(propertyInfo.Name, typeof(int));
            } else {
                table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
            }
            table.Columns[table.Columns.Count - 1].AllowDBNull = true;
        }
        // fill rows
        foreach(var obj in objs) {
            var list = new List<object>();
            foreach(var propertyInfo in propertyInfos) {
                object currentValue;
                if(propertyInfo.PropertyType.IsEnum || propertyInfo.PropertyType.IsNullableEnum()) {
                    var val = propertyInfo.GetValue(obj);
                    if(val == null) {
                        currentValue = DBNull.Value;
                    } else {
                        currentValue = (int)propertyInfo.GetValue(obj);
                    }
                } else {
                    var val = propertyInfo.GetValue(obj);
                    currentValue = val ?? DBNull.Value;
                }
                list.Add(currentValue);
            }
            lists.Add(list);
        }
        lists.ForEach(x => table.Rows.Add(x.ToArray()));
        return table;
    }

Edit:

this extension method is used:

    public static bool IsNullableEnum(this Type t) {
        var u = Nullable.GetUnderlyingType(t);
        return u != null && u.IsEnum;
    }