Linq merges 2 datatables that split the column and cast the result into a new datatable

Question

I have the following data types

table1

+-----------+------+------+ | catalogid | name | snum | +-----------+------+------+ | 353 | xx | 4 | | 364 | yy | 3 | | 882 | zz | 3 | | 224 | mm | 71 | | 999 | kk | 321 | | 74 | kk | 4 | | 54 | ii | 5 | | 11 | u | 6 | | 23 | yy | 6 | +-----------+------+------+ 

table2

 +-----------+----------+--------------+ | catalogid | numitems | ignoreditems | +-----------+----------+--------------+ | 353 | 4 | 0 | | 364 | 10 | 0 | | 882 | 2 | 0 | | 224 | 0 | 7 | +-----------+----------+--------------+ 

Using LINQ, I want to join them and copy the result into a new datatable. They both share catalogid , and as a result, it should only display entries that their cataloging exists in table2

result

 +-----------+------+------+-----------+---------------+ | catalogid | name | snum | numitems | ignoreditems | +-----------+------+------+-----------+---------------+ | 353 | xx | 4 | 4 | 0 | | 364 | yy | 3 | 10 | 0 | | 882 | zz | 3 | 2 | 0 | | 224 | mm | 71 | 0 | 7 | +-----------+------+------+-----------+---------------+ 

Here is my attempt, but it does not work:

  Dim query = From a In oresult.AsEnumerable Group Join b In products.AsEnumerable On a.Field(Of Integer)("catalogid") Equals b.Field(Of Integer)("catalogid") Into Group query.copytodatatable 

CopyToDatatable does not work and I cannot understand why

Answer 1

CopyToDataTable () only works when your query returns IEnumerable <'DataRow>. In your request, you return an anonymous type. Anonymous types do not carry an extension method for CopyToDataTable ().

You can create a table using the ConvertToDataTable extension listed below. You will have to convert it to VB.NET (there are converters there if you google).

 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using Common; namespace TestConsole { public class Linq_join_2_datatables_that_share_a_column_and_put_result_in_new_datatable { public class Table1 { public int CatalogId { get; set; } public string Name { get; set; } public int SNum { get; set; } } public class Table2 { public int CatalogId { get; set; } public int NumItems { get; set; } public int IgnoredItems { get; set; } } public static void Start() { DataTable table1 = new DataTable(); table1.Columns.Add("catalogid", typeof(int)); table1.Columns.Add("name", typeof(string)); table1.Columns.Add("snum", typeof(int)); DataRow row = table1.Rows.Add(353, "xx", 4); DataTable table2 = new DataTable(); table2.Columns.Add("catalogid", typeof(int)); table2.Columns.Add("numitems", typeof(int)); table2.Columns.Add("ignoreditems", typeof(int)); table2.Rows.Add(353, 4, 0); var query = (from t1 in table1.AsEnumerable() join t2 in table2.AsEnumerable() on t1.Field<int>("catalogid") equals t2.Field<int>("catalogid") select new { catalogid = t1.Field<int>("catalogid"), name = t1.Field<string>("name"), snum = t1.Field<int>("snum"), numitems = t2.Field<int>("numitems"), ignoreditems = t2.Field<int>("ignoreditems") }).ToList(); DataTable table3 = query.ConvertToDataTable(); } } } using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.ComponentModel; using System.Reflection; namespace Common { public static class DataTableExtensions { public static DataTable ConvertToDataTable<T>(this IList<T> data) { PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)); DataTable table = new DataTable(); foreach (PropertyDescriptor prop in properties) table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType); foreach (T item in data) { DataRow row = table.NewRow(); foreach (PropertyDescriptor prop in properties) row[prop.Name] = prop.GetValue(item) ?? DBNull.Value; table.Rows.Add(row); } table.AcceptChanges(); return table; } } } 

Answer 2

CopyToDataRow only works on IEnumerable DataRow . See this MSDN article for an implementation of CopyToDataRow for arbitrary IEnumerable s.