Can I use Linq to project a new typed datarow?

Question

I currently have a csv file that I'm parsing with an example from here: http://alexreg.wordpress.com/2009/05/03/strongly-typed-csv-reader-in-c/

I then want to loop the records and insert them using a typed dataset xsd to an Oracle database.

It's not that difficult, something like:

foreach (var csvItem in csvfile)
{
   DataSet.MYTABLEDataTable DT = new DataSet.MYTABLEDataTable();
   DataSet.MYTABLERow row = DT.NewMYTABLERow();
   row.FIELD1 = csvItem.FIELD1;
   row.FIELD2 = csvItem.FIELD2;
}

I was wondering how I would do something with LINQ projection:

var test = from csvItem in csvfile
  select new MYTABLERow {
    FIELD1 = csvItem.FIELD1,
    FIELD2 = csvItem.FIELD2
}

But I don't think I can create datarows like this without the use of a rowbuilder, or maybe a better constructor for the datarow?

Answer 1

You could, but you shouldn't. NewRow() is a method, not a constructor that allows for an object initializer to be used. You're better off sticking with the foreach loop for clarity.

You can abuse LINQ to pull this off, but this is not recommended and is for demonstration purposes only:

DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Customer", typeof(string));
dt.Rows.Add(new Object[] { 24, "Peter Parker" });
dt.Rows.Add(new Object[] { 42, "Bruce Wayne" });

var query = Enumerable.Range(1, 5).All(i =>
            {
                var row = dt.NewRow();
                row["Id"] = i;
                row[1] = "Customer" + i;
                dt.Rows.Add(row);
                return true;
            });

foreach (DataRow row in dt.Rows)
{
    Console.WriteLine("{0}: {1}", row[0], row[1]);
}

The idea is to use a lambda statement to do your work. Notice you gain nothing and the statements are going to be identical to what you had in the foreach loop. On top of that, LINQ is deferred, so the rows are never added until the query is iterated over. In the snippet above I forced this to be immediate by using the All method and returning true for each item so all items continue to be processed. In fact the var query = bit can be removed since it's not used.

In your case your source would be the csvfile:

csvfile.All(csvItem => { /* do work here */ });

This approach introduces side effects since the LINQ query isn't used for its original intent. LINQ is for querying. You might also find Eric Lippert's post helpful: "foreach" vs "ForEach".