LINQ query concept

Question

I'm having a hard time wrapping my head around this requirement of a query I need.

Here is the data I have in a table:

row1  row2
A     B
A     C
A     D
A     E
E     B
E     C
E     A
E     D

I want all rows

where row1 IN ('A', 'E')

But I don't want the row where row1 = E and row2 = A.

I think in straight SQL I could do an aliased table like SELECT....AS Table1 and then select only the rows where row2 was not found in Table1, but I'm not sure how to go about this in LINQ.

Any ideas ?

EDIT: Sorry for the ambiguity of my question, but I do not know in advance the criteria where row1 in ('A', 'E'). Basically if I have retrieved a record (record1) with a specific value in row1, if I find that same specific value in row2 in another record (record2), I do not want record2.

`.Where((obj => obj.row1 == "E" && obj.row2 != "A") || (obj => obj.row1 == "A" && obj.row2 != "E")` This? Your requirements aren't quite clear. — vcsjones, Feb 10 '13 at 17:07
Use a LINQ-left-join onto the same table and look for nulls. http://stackoverflow.com/questions/1122942/linq-to-sql-left-outer-join-with-multiple-join-conditions . This question changed significantly between edits — Brian Webster, Feb 10 '13 at 17:17

Gert Arnold · Answer 1 · 2013-02-10T21:07:16.730

Still not entirely clear after your update but I think this gets you on the right track:

var rows = new[] {
Tuple.Create("A", "B"),
Tuple.Create("A", "C"),
Tuple.Create("A", "D"),
Tuple.Create("A", "E"),
Tuple.Create("E", "B"),
Tuple.Create("E", "C"),
Tuple.Create("E", "A"),
Tuple.Create("E", "D"),
};
var reference = Tuple.Create("A", "E");

var result = rows.Where(r => (r.Item1 == reference.Item1 || r.Item1 == reference.Item2)
                          && r.Item2 != reference.Item1
                          && r.Item1 != reference.Item2);

After your update I'm not sure whether or not the last condition should be included. Presently it returns:

Item1 Item2 
A     B 
A     C 
A     D 
A     E

Maybe you can show the desired output if this still misses the mark.

score 0 · Answer 2 · answered Feb 11 '13 at 20:48

I am going to assume that you want to create a PIVOT query.

So with your input you want to get an output of the form

Key - Value

A - B,C,D,E

B - B,C,A,D

Where Key is of type string and Value is of type IEnumerable

In that case you want: -

var output = allRows.ToLookup(x => x.row1, x => x.row2);

LINQ query concept

2 Answers2