Set Operators

Method

Description

SQL equivalents

Concat

Returns a concatenation of elements in each of the two sequences

UNION ALL

Union

Returns a concatenation of elements in each of the two sequences, excluding duplicates

UNION

Intersect

Returns elements present in both sequences

WHERE…IN…

Except

Returns elements present in the first, but not the second sequence

EXCEPT or WHERE…NOT IN…

Concat and Union

Contact returns all the elements of the first sequence, followed by all the elements of the second. Union does the same, but removes any duplicates:

	int[] seq1 = { 1, 2, 3 }, seq2 = { 3, 4,5 };

	IEnumerable<int>
	  concat = seq1.Concat (seq2), // { 1, 2,3, 3, 4, 5 }
	  union  = seq1.Union  (seq2); // { 1, 2, 3,4, 5 }

Intersect and Except

Intersect returns the elements that two sequences have in common. Except returns the elements in the first input sequence that are not present in the second:

	int[] seq1 = { 1, 2, 3 }, seq2 = { 3, 4,5 };

	IEnumerable<int>
	  commonality = seq1.Intersect (seq2),   // { 3 }
	  difference1 = seq1.Except    (seq2),   // {1, 2 }
	  difference2 = seq2.Except    (seq1);   // {4, 5 }

Enumerable.Except works internally by loading all of the elements in the first collection into a dictionary, then removing from the dictionary all elements present in the second sequence. The equivalent in SQL is a NOT EXISTS or NOT IN subquery:

	SELECT number FROM numbers1Table
	WHERE number NOT IN (SELECT number FROM numbers2Table)

Get LINQ Pocket Reference now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.