1

Suppose I have this initial code:

        DataTable table = new DataTable();
        table.Columns.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.

Improve this question

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

What about [c]? should I just expand the LINQ instead? Or can I use the var result on another LINQ?
If you need the output of [a] query use the value of the result.
Also, I think you meant select t1 there.
Right. I've changed the query and added Distinct() at the end.
You could do something like var resultInteresct = table.AsEnumerable().Select(i=>i.Field<int>(0)).Intersect(table.AsEnumerable().Select(j=>j.Field<int>(1))); to get the int values that exists in the columns. After that you can do another query on table to select the Rows with those value.
|
1

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);
Improve this answer

2 Comments

Is the myList like this: List<int> myList = new List<int>() {1,2,3,4,5};(contents of column1)?
No... Create a class with three properties. List<myClass>.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.