Put LINQ query result to DataTable

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.

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

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