LINQ to Entities query to DataTable

Question

I need to store the data returned from this LINQ to Entities query (below) into a DataTable so that I can use it as data source to a DataGridView, how can I do that?

In this case I'm using LINQ to Entities to query against an Entity Framework conceptual model, so db is a class that inherits from System.Data.Entity.DbContext.

using (TccContext db = new TccContext())
{
    var query = from vendedor in db.Vendedores.AsEnumerable()
                where vendedor.codigo == Convert.ToInt32(textBoxPesquisa.Text)
                select vendedor;
    // I'd like to do something like DataTable dt = query;
}

I've tried to do this (below), but it throws an exception during execution [1].

using (TccContext db = new TccContext())
{
    IEnumerable<DataRow> query = (IEnumerable<DataRow>)(from vendedor in db.Vendedores.AsEnumerable()
                                                        where vendedor.codigo == Convert.ToInt32(textBoxPesquisa.Text)
                                                        select vendedor);

    using (DataTable dt = query.CopyToDataTable<DataRow>())
    {
        this.dataGridViewProcura.Rows.Add(
            dt.Rows[0][0],  // Código
            dt.Rows[0][1],  // Nome
            dt.Rows[0][2]); // Venda Mensal
    }
}

[1]: Exception: InvalidCastException

Unable to cast object of type 'WhereEnumerableIterator`1[Projeto_TCC.Models.Vendedor]' to type 'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'.

Thanks in advance

Solution

There is one important thing here, you are casting your Linq query to (IEnumerable<DataRow>) when you are selecting the vendedor, so I assume that vendedor is an instance of Vendedor, so your query will return an IEnumerable<Vendedor>

That should solve your problem, but also, can you try using the generated DataTable as the DataSource for your DataGridView? It would be something like this:

var query = (from vendedor in db.Vendedores.AsEnumerable()
                   where vendedor.codigo == Convert.ToInt32(textBoxPesquisa.Text)
                   select vendedor);
var dt = query.CopyToDataTable<Vendedor>();
this.dataGridViewProcura.DataSource = dt;

Hope I can help!

EDIT

As a side (and very personal) note, you could try using lambdas on your select, they look prettier :)

var pesquisa = Convert.ToInt32(textBoxPesquisa.Text);
var query = db.Vendedores.Where(vendedor => vendedor.codigo == pesquisa);

var dt = query.CopyToDataTable<Vendedor>();
this.dataGridViewProcura.DataSource = dt;

A lot cleaner, don't you think?

EDIT 2 I've just realized what you said on CopyToDataTable being for DataRow only, so last (admittedly not so clean) solution would be to mimic the logic on the helper?

public DataTable CopyGenericToDataTable<T>(this IEnumerable<T> items)
{
    var properties = typeof(T).GetProperties();
    var result = new DataTable();

    //Build the columns
    foreach ( var prop in properties ) {
        result.Columns.Add(prop.Name, prop.PropertyType);
    }

    //Fill the DataTable
    foreach( var item in items ){
        var row = result.NewRow();

        foreach ( var prop in properties ) {
            var itemValue = prop.GetValue(item, new object[] {});
            row[prop.Name] = itemValue;
        }

        result.Rows.Add(row);
    }

    return result;
}

Now, things to consider:

• This solution will not work with complex properties
• Customizing the resulting table might be a bit tricky

While this might solve the issue, I don't think this is a very good approach, but it could be the start of a decent idea :)

I hope I can help this time!

Comments

[Zignd]

The method CopyToDataTable will not be available in the query if I use it like that. Please note that I'm using Entity Framework.

[David Conde]

I made a typo on the last source, and as the edit says, the CopyToDataTable method should work there. btw - Did the answer helped at all?

[Zignd]

As you can see in the same link, to gain access to the CopyToDataTable method the T type in the IEnumerable<T> must be of type DataRow, but as instead you used the class that represents the database table Vendedor I end up not having access to those extension methods that includes the CopyToDataTable.

[Palindrom]

It is not valid solution for converting to data table

[DaniDev]

@David Conde I spend a little time trying to implement your initial solution, as I believed I had stumbled upon a quick fix that I had overlooked. Unfortunately, I arrived at the same dead end as zigrid. I was a little frustrating to then read the comments and realize that this is not a valid option.

[DaniDev]

I have been implementing a couple of other Object "shredder" to DataTable solutions such as the one you suggest in your EDIT 2(@David Conde ), but I have discovered that can be either problematic for complex types (as mentioned) especially EntityTypes with foreign keys or very costly or both. I think that in many cases it is simply best to abandon the Entity types and query the DB directly which easily converts to a DataTable