Select specific fields from Linq query and populate a DataTable

Question

I'm trying to select a number DataRows with only specified fields from a Linq query, and then use these DataRows to populate a DataTable. The problem is that when I add these DataRows to the new DataTable I'm expect both ID and Name field to be populated respectively. However, the ID field in the DataTable contains both ID and Name values. Can someone point out what I'm doing wrong.

Here's the code:

var query2 = from s in Tables[Table_Sec].AsEnumerable()
             where query.Contains(s["sectype"])
             select new { id = s["id"], name = s["name"] }; // I only want these fields



DataTable dt = new DataTable();  // Create my new dataTable
dt.Columns.Add("id", typeof(string));
dt.Columns.Add("name", typeof(string));

foreach(var row in query2)
{
  dt.Rows.Add(row);  // ID field contains both ID and Name strings. Name field contains nothing
}

What is going wrong? Can you describe the problem more clearly, do you get an exception? What is `query`? — Tim Schmelter, Feb 26 '13 at 16:34

score 1 · Answer 1 · answered Feb 26 '13 at 16:43

You could try this because a DataRow constructor allows you to pass an object-array

var result = from s in Tables[Table_Sec].AsEnumerable()
             where query.Contains(s["sectype"])
             select new object[] 
             {
                 s["id"],
                 s["name" ]
             };

DataTable dt = new DataTable();  
dt.Columns.Add("id", typeof(string));
dt.Columns.Add("name", typeof(string));

foreach(var row in result)
{
    dt.Rows.Add(row); 
}

// EDIT:
I would not recommend this way because it heavily depends on the right order of the columns and i'm not even sure if there are another circumstances how this could end up in a mess =) Pick one from the other solutions (at least for coding)

score 0 · Answer 2 · answered Feb 26 '13 at 16:34

From MSDN:

DataRow newCustomersRow = dataSet1.Tables["Customers"].NewRow();

newCustomersRow["CustomerID"] = "ALFKI";
newCustomersRow["CompanyName"] = "Alfreds Futterkiste";

dataSet1.Tables["Customers"].Rows.Add(newCustomersRow);

This is how you add a row to a datatable

So yours would be like:

foreach(var row in query2)
{
var newRow = dt.NewRow();
newRow["id"] = row.id;
newRow["name"] = row.name;
  dt.Rows.Add(newRow);  // ID field contains both ID and Name strings. Name field contains nothing
}

score 0 · Accepted Answer · answered Feb 26 '13 at 16:34

0

You have to populate each cell within a row manually:

foreach(var row in query2)
{
    var newRow = dt.NewRow();
    newRow["id"]= row.id;
    newRow["name"]= row.name;
    dt.Rows.Add(newRow);
}

answered Feb 26 '13 at 16:34

MarcinJuraszek

124,003
15
196
263

score 0 · Answer 4 · answered Feb 26 '13 at 16:35

I would avoid the Datatable for this method and use POCO, Plain Old Class Object.

public class POCO
{
     public string id { get; set; }
     public string name { get; set; }
}

var query2 = from s in Tables[Table_Sec].AsEnumerable()
             where query.Contains(s["sectype"])
             select new POCO { id = s["id"], name = s["name"] };

The advantages of POCO are you separate your data from an association with something existing and instead refer it to a class that exists to only serve as a collection.

score 0 · Answer 5 · edited May 23 '17 at 11:43

You haven't described the problem very well. However, you should not add rows to different DataTables because you normally get an exception("Row already belongs to another table").

You should use the strongly typed field extension method to ensure that you don't use ReferenceEquals mistakenly when you select an object. You can use CopyToDataTable to create a new DataTable from the Linq query when it contains an IEnumerable<DataRow>.

I would also use Enumerable.Join instead of query.Contains for performance reasons:

var query2 = from secRow in Tables[Table_Sec].AsEnumerable()
             join sectype in query on secRow.Field<string>("sectype") equals sectype 
             select secRow;

DataTable dt = query2.CopyToDataTable();

Select specific fields from Linq query and populate a DataTable

5 Answers5