How to use Case Conditions in LINQ To DataSets

Question

I have a DataTable containing some records. Based on some conditions I have to filter records into two datatables.

Say

When row["ItemType"]==1
     //Insert item into DataTable 1
When row["ItemType"]==2
     //Insert item into DataTable 2
Else
     //Insert item into DataTable 3

How to do this in LINQ for a DataTable that contains all the records?

Answer 1

LINQ is a query language that is meant for read operations. You can't do an insert operation in a LINQ query. What you can do is create three LINQ queries and iterate over them separately:

var rows1 = from row in rows where row["ItemType"] == 1 select row;
var rows2 = from row in rows where row["ItemType"] == 2 select row;
var rows3 = from row in rows where row["ItemType"] == 3 select row;

foreach (var row in rows1) { /* Insert item into DataTable1 */ }
foreach (var row in rows2) { /* Insert item into DataTable2 */ }
foreach (var row in rows3) { /* Insert item into DataTable3 */ }

---

Update:

Or when you can do do it in a single LINQ statement, you can do this:

var rows =
    from row in rows 
    select new { Row = row, Item =  row["ItemType"] };

foreach (var row in rows)
{
    switch (row.Item)
    {
        case 1:
            // Insert item into DataTable1
            break;
        case 2:
            // Insert item into DataTable2
            break;
        case 3:
            // Insert item into DataTable3
            break;
    }
}

Update: Another option:

var rows =
    from row in rows
    select new
    {
        Row = row, 
        Table = row["ItemType"] == 1 ? table1 : 
            (row["ItemType"] == 2 ? table2 : table3)
    };

foreach (var row in rows)
{
    var table = row.Table;
    var row = row.Row;

    table.Rows.Add(row);
}