Hi,
When I first saw your question I was a little confused about the combination of DataTables and Entity Framework (which uses LINQ to Entities to query it). They are sort of two different worlds.
DataSet/DataTables and Entity Framework are different techniques to retrieve data from a database. You might want to search CodeProject for the difference or Google it. The Entity Framework can be queried using LINQ to Entity. DataSets/DataTable were introduced in .NET 1.0. Which is about 7 years prior to the introduction of LINQ. However Microsoft build a LINQ to DataSet to query datasets the 'LINQ way'. You might want to read this article on
Wikipedia about LINQ[
^].
You will discover that they look the same but are different as well. The
article you mentioned[
^] is only about LINQ to DataSet. The statement can't be used to copy data from a LINQ query to a DataTable.
There might be reasons why you want to use LINQ to Entity to query a database and transfer the data to a DataTable. Maybe you use DataSets in your interface and don't want to change that. If you are building from scratch you might want to reconsider doing this.
Back to the real question, how to convert a LINQ to Entity result to a DataTable. In the example below I have made a little adjustment to the DataTable to match the columns in the LINQ to Entity query:
DataTable table = new DataTable();
DataColumn dc = table.Columns.Add("NewId", typeof(int));
dc.AutoIncrement = true;
table.Columns.Add("Price", typeof(double));
table.Columns.Add("Genre", typeof(string));
using (TestEntities context = new TestEntities())
{
var query2 = from i in context.Items
where i.Price > 9.99
orderby i.Price
select new { i.Price, i.Genre };
}
I have added the
TestEntities context = new TestEntities() because this is the way to define your workspace (comparable to the DataSet). The TestEntities model is connecting to a SQL Server database with a 'Item' table (containing a ID primary key, a Price and a Genre column).
In the query you see a context.Items. This is a ObjectSet<item> and is comparable to the DataTable.
I'll show you 3 methods to solve this.
1) Using lambda expressions to add the item to the table
2) Using lambda but with a support method
3) Using a partial class Item
The first solution uses a lambda to solve it. If you haven't used any lambda expressions you might want to look at this article about
Lambda expressions[
^]. Or you could have a look at the other solutions (but you will meet them when you are using LINQ). The following code converts the query to a List (using ToList()). A List has the LINQ method ForEach (LINQ equivalent of the foreach statement) and loops through the results. The Lambda creates a method to add the results to the datatable.
private void Method1
{
DataTable table = new DataTable();
DataColumn dc = table.Columns.Add("NewId", typeof(int));
dc.AutoIncrement = true;
table.Columns.Add("Price", typeof(double));
table.Columns.Add("Genre", typeof(string));
using (TestEntities context = new TestEntities())
{
var query2 = from i in context.Items
where i.Price > 9.99
orderby i.Price
select new { i.Price, i.Genre };
query2.ToList().ForEach((n) =>
{
DataRow row = table.NewRow();
row.SetField<double>("Price", n.Price);
row.SetField<string>("Genre", n.Genre);
table.Rows.Add(row);
});
}
}
You could use a conventional foreach loop and a submethod. The
select new { i.Price, i.Genre} is a so called anonymous type. Anonymous types have the advantage of being a dynamically created type (no formal class definition needs to exists), but the disadvantage is that there are not know outside the scope of the method. So in the next example i use
select i to return the whole Item object (ofcourse you don't need the sub method. If you place the adding inside the foreach then you could still use the anonymous type).
private void Method2()
{
DataTable table = new DataTable();
DataColumn dc = table.Columns.Add("NewId", typeof(int));
dc.AutoIncrement = true;
table.Columns.Add("Price", typeof(double));
table.Columns.Add("Genre", typeof(string));
using (TestEntities context = new TestEntities())
{
var query2 = from i in context.Items
where i.Price > 9.99
orderby i.Price
select i;
foreach (var n in query2)
{
AddItemToDataTable(table, n);
}
}
}
private void AddItemToDataTable(DataTable table, Item item)
{
DataRow row = table.NewRow();
row.SetField<double>("Price", item.Price);
row.SetField<string>("Genre", item.Genre);
table.Rows.Add(row);
}
A third possible solution is extending the Item object using a partial class (see:
http://msdn.microsoft.com/en-us/library/wa80x488(v=vs.80).aspx[
^]). This is a clean way to solve this, but does expected certain columns in your DataTable. The Entity Framework generate its entity classes as partial. Thus giving you the opertunity to expand them. This is done rather easy, like this (make sure the namespace is exactly the same as the namespace of your Entity Framework model):
public partial class Item
{
public void FillDataTable(DataTable table)
{
DataRow row = table.NewRow();
row.SetField<double>("Price", this.Price);
row.SetField<string>("Genre", this.Genre);
table.Rows.Add(row);
}
}
Calling this method is quite simple now. You do need the non-anonymous
select i version as the anonymous version won't have a clue where to find the partial class.
private void Method3()
{
DataTable table = new DataTable();
DataColumn dc = table.Columns.Add("NewId", typeof(int));
dc.AutoIncrement = true;
table.Columns.Add("Price", typeof(double));
table.Columns.Add("Genre", typeof(string));
using (TestEntities context = new TestEntities())
{
var query2 = from i in context.Items
where i.Price > 9.99
orderby i.Price
select i;
query2.ToList().ForEach(n => n.FillDataTable(table));
}
}
It has become quite a long answer. Though DataTables and Entities aren't the same and it is not possible to integrate them with one line of code, it is possible. I hope this is what you were looking for.
Regards
Martijn
Submit an article or tip