Help with querying a DataTable using LINQ

Suppose I have this initial code:

        DataTable table = new DataTable();
        table.Columns开发者_StackOverflow社区.Add("column1", typeof(int));
        table.Columns.Add("column2", typeof(int));
        table.Columns.Add("column3", typeof(string));
        table.Rows.Add(1, 0, "a");
        table.Rows.Add(2, 1, "b");
        table.Rows.Add(3, 1, "c");
        table.Rows.Add(4, 3, "d");
        table.Rows.Add(5, 3, "e");

How can I do these using LINQ?

a. Return the DataRows whose values in column1 also appears in column2.

So far, I did this:

var x = (from t1 in table.AsEnumerable()
         select t1.Field<int>(0)).Intersect
        ((from t2 in table.AsEnumerable()
         select t2.Field<int>(1)).Distinct());

Bu the problem is, only the values of column1 is returned, which I use a foreach on. Probably because of the select t1.Field<int>(0) part, but I don't know how to return the DataRows itself.

b. Return the values of column3 whose values in column1 also appears in column2.

Almost the same question as [a]. I can only return the column1 row since I already used it. I don't know how to return the DataRows and other columns (e.g. column3) except column1.

I have also tried this:

var x1 = from t in table.AsEnumerable()
         select t;
var x2 = (from t in table.AsEnumerable()
          select t.Field<int>(1)).Distinct();

I was hoping to use Intersect() on x1 and x2, but I don't know how. Especially since x1 is kind of like a DataRow[] and x2 like an int[].

c. Using the answer in [a] for another query.

Or using something from a LINQ for another LINQ. i have no idea at all how to do something like this.

---

An approach:

a)    var result = (from t1 in table.AsEnumerable() 
                   join t2 in table.AsEnumerable() on t1.Field<int>(0) equals t2.Field<int>(1)  select t1).Distinct();

The query above returnsIEnumerable<DataRow>.From this result you can select values of column3 like t2.Field<int>(2) for b) scenario.

---

I would create a new class for the three columns. Then create an Iqueryable or List for the new class and add the table rows into that. Then the Linq expression should work.

Class

public class myClass
{
    public int column1
    {
        get;
        set;
    }
    public int column2
    {
        get;
        set;
    }
    public stringcolumn3
    {
        get;
        set;
    }
}

Linq

a. Return the DataRows whose values in column1 also appears in column2.

var x = (from l1 in myList
         where (from l2 in myList
                select l2.column2).contains(l1.column1)
         select l1);

b. Return the values of column3 whose values in column1 also appears in column2.

var col3Values = (from l1 in myList
                  where l1.column2 = l1.column3
                  select l1.column3);

---

With help from guys here, and some other sites, I just found out how to actually do [b] above.

This returns the values in column3 whose values in column1 does not appear in column2:

   from t in table.AsEnumerable()
       join t2 in table.AsEnumerable().Select(i => i.Field<int>(0)).Except(table.AsEnumerable().Select(j => j.Field<int>(1)))
       //the inner Select() returns column1 whose values in it also appears in column2
       //I can use either this or the first LINQ I made above
       //By the way, I said **does not** because I don't think I can use inner join on the opposite of [b]
       //unlike the Select() with lambda above; I can just change the Intersect() to Except() :)
       on t.Field<int>(0) equals t2
   where t.Field<int>(1) > 2          //If I need some other condition
   select t.Field<string>(2);

For [c], I made another table:

        DataTable tableA = new DataTable();
        tableA.Columns.Add("columnA", typeof(string));
        tableA.Columns.Add("columnB", typeof(string));
        tableA.Rows.Add("apple", "red");
        tableA.Rows.Add("banana", "yellow");
        tableA.Rows.Add("carrot", "orange");
        tableA.Rows.Add("dog", "commonly brown");      //ok, I can't think of a fruit/vegetable that starts with 'd' right now...
        tableA.Rows.Add("eggplant", "purple");

And renamed the first table to table1 to avoid/minimize confusion

    var x = from tA in tableA.AsEnumerable()
        from t1 in (
            from t1 in table1.AsEnumerable()
            join t2 in ((from t2_1 in table1.AsEnumerable()
                            select t2_1.Field<int>(0)).Except
                        ((from t2_2 in table1.AsEnumerable()
                            select t2_2.Field<int>(1))).Distinct())
            on t1.Field<int>(0) equals t2
            where t1.Field<int>(1) > 2        //extra condition
            select t1.Field<string>(2))
        where tA.Field<string>(0).StartsWith(t1)
        select tA;

This returns Rows in tableA whose columnA starts with the returned table1's column3, whose column1 values does not appear in column2 and has a value greater than 2 in its column2.