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
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:
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!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With