List of entities to datatable

Question

I have a list of entities which contain some of it fields as other entities.

Eg.

MyEntity
Int id
ContactEntity Contact -> contactId, Name etc…
AddressEntity Address

So I have List< MyEntity> which needs to be converted to a data table. But from the sub entities I want only one field to be selected.

Is it possible or do I have any other alternative.

UPDATE

When I try CopyToDataTable() as ivowiblo describes it gives me following error

 The type 'AnonymousType#1' cannot be used as type parameter 'T' in the generic type or
 method 'System.Data.DataTableExtensions.CopyToDataTable<T>(System.Collections.Generic.IEnumerable<T>)'.
 There is no implicit reference conversion from 'AnonymousType#1' to 'System.Data.DataRow'.

Answer 1

In http://msdn.microsoft.com/en-us/library/bb669096.aspx they explain how to implement a CopyToDataTable() method that doesn't need the type to be a DataRow for working with, for instance, entities.

Just create a query that returns the desired schema and use CopyToDataTable() method:

var table = entities.Select(x => new {
                                       x.Id,
                                       Contact = x.Contact.Name,
                                       Address = x.Address.Address
                                      }).CopyToDataTable();

The only issue with this solution is that it uses reflection and it may hit the performance, depending on the load of you application. If you need to avoid reflection, you will need to create a method that explicitly creates the DataTable from you entities:

var table = new DataTable();

table.Columns.Add("Id", typeof(int))
table.Columns.Add("Contact", typeof(string))
table.Columns.Add("Address", typeof(string))

foreach(var entity in entities) {
    var row = table.NewRow();
    row["Id"] = entity.Id;
    row["Contact"] = entity.Contact.Name;
    row["Address"] = entity.Address.Address;
    table.Rows.Add(row);
}

Answer 2

Try this code (with CopyToDataTable function):

var query = ....
DataTable dataTable = query.CopyToDataTable();