UNION and UNION ALL in LINQ to SQL

7 May 2008 1 mins .NET, Databases in general, LINQ

When you need to hook up two results in LINQ to SQL you will probably first try the method Union. It generates “standard” union (i.e. select a, b, c from x union select e, f, g from y), which is OK. But if you know the difference between union and union all (union (without all) is filtering duplicates in result, so it can be slower) you may want to use union all (for example you know that results are distinct or you want duplicates). So, you will need another method. It’s called little bit “non-SQL” 😃 Concat (at least it took me few minutes to find it, but the name makes sense). Using this method you get result with union all, so you can save some processing and speedup returning result.

Summary:

UNION ALL ⇐=> Concat
UNION ⇐=> Union

Jiří Činčura is .NET, C# and Firebird expert. He focuses on data and business layers, language constructs, parallelism, databases and performance. For almost two decades he contributes to open-source, i.e. FirebirdClient. He works as a senior software engineer for Microsoft. Frequent speaker and blogger at www.tabsoverspaces.com.