Linq to SQL - Return DataTable as a result of Linq query

[szwejk]

Hi!

How to get result od dataTable from Linq query? I have typied DataSet
and I want to join couple of tables. And I have a problem with change
this result to DataTable type. (I don't want to rewrite everything in
foreach)

e.g. How can I make DataTable from it? Thx for help!

var query =
from firmy in boKontakty.DataSetKontakty.FIRMY
join kancelarie in
boKontakty.DataSetKontakty.KANCELARIE on firmy.FIRMA_O equals
kancelarie.ID into firmKan
join kategorie in
boKontakty.DataSetKontakty.KATEGORIE on firmy.KATEGORIA equals
kategorie.NAZWA into firmKanKat
select
new
{
Id = firmy.ID,
Nazwa = firmy.NAZWA,
Nazwa_pelna = firmy.NAZWA_PELNA,
Kancelaria_obslugujaca = kancelarie.NAZWA,
Kolor = kategorie.KOLOR
};

 

[Alberto Poblacion]

How to get result od dataTable from Linq query? I have typied DataSet
and I want to join couple of tables. And I have a problem with change
this result to DataTable type. (I don't want to rewrite everything in
foreach)

The standard libraries do not contain such a functionality, but it
shouldn't be terribly difficult to write an Extension method for
IEnumerable<T> returning a DataTable, using Reflection to enumerate the
properties of T and creating a DataTable (or filling an existing one) with
equivalent columns. Of course, the internal implementation would have to use
foreach to populate the table, but this would be invisible to you once the
extension method was created.

 

[Alberto Poblacion]

The standard libraries do not contain such a functionality, but it
shouldn't be terribly difficult to write an Extension method for
IEnumerable<T> returning a DataTable, using Reflection to enumerate the
properties of T and creating a DataTable (or filling an existing one) with
equivalent columns. Of course, the internal implementation would have to
use foreach to populate the table, but this would be invisible to you once
the extension method was created.

OK, I decided to write it. Here it is:

public static class MyExtenders
{
public static DataTable ToDataTable<T>(this IEnumerable<T>
collection)
{
DataTable dt = new DataTable();
Type t = typeof(T);
PropertyInfo[] pia = t.GetProperties();
//Create the columns in the DataTable
foreach (PropertyInfo pi in pia)
{
dt.Columns.Add(pi.Name, pi.PropertyType);
}
//Populate the table
foreach (T item in collection)
{
DataRow dr = dt.NewRow();
dr.BeginEdit();
foreach (PropertyInfo pi in pia)
{
dr[pi.Name] = pi.GetValue(item, null);
}
dr.EndEdit();
dt.Rows.Add(dr);
}
return dt;
}
}

Of course, this routine is only valid for the simplest case, where the list
of selected objects expose their contents through properties. It won't work
in other cases, such as if they are exposing their content through public
fields.

You can test it with this program:

class Program
{
static void Main(string[] args)
{
MyTestClass[] test = { new MyTestClass { prop1 = "hello", prop2
= 1 },
new MyTestClass { prop1 = "world", prop2
= 2 }};

DataTable dt = (from t in test
select new { Id = t.prop2, Desc = t.prop1, Len =
t.prop1.Length }
).ToDataTable();

foreach (DataRow dr in dt.Rows)
{
foreach (DataColumn dc in dt.Columns)
{
Console.Write("{0}={1} ", dc.ColumnName, dr[dc]);
}
Console.WriteLine();
}
Console.ReadLine();
}
}


class MyTestClass
{
public string prop1 { get; set; }
public int prop2 { get; set; }
}

 

[Nicholas Paldino [.NET/C# MVP]]

Another alternative to this approach (if you do not want to iterate over
the set twice, once for populating the objects, and then once for populating
the data set), you could try to modify the designer generated code and
attach the Column and Table attributes to the appropriate
classes/properties. Then, the data context should pick it up.

The problem with that, of course, is editing the designer-generated
code.