This morning I came across the Concat and Union methods within Linq to SQL. From the surface they look very similar.
var query = from o in db.Orders
where o.ShipCountry == “UK”
select o;
var query2 = from o in db.Orders
where o.ShipCountry == “France”
select o;
var union = query.Union(query2);
Console.WriteLine(union.Count());
var concat = query.Concat(query2);
Console.WriteLine(concat.Count());
The SQL they is also very similar, Union is first, concat is second.
SELECT COUNT(*) AS [value]
FROM (
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [t0].[ShipCountry] = @p0
UNION
SELECT [t1].[OrderID], [t1].[CustomerID], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate], [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[ShipCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry]
FROM [dbo].[Orders] AS [t1]
WHERE [t1].[ShipCountry] = @p1
) AS [t2]
SELECT COUNT(*) AS [value]
FROM (
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [t0].[ShipCountry] = @p0
UNION ALL
SELECT [t1].[OrderID], [t1].[CustomerID], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate], [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[ShipCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry]
FROM [dbo].[Orders] AS [t1]
WHERE [t1].[ShipCountry] = @p1
) AS [t2]
The only difference being, Union uses ‘UNION’ while Concat uses ‘UNION ALL’. A look on W3 Schools gives us the difference between the two keywords. Union returns distinct values – duplicate rows removed, while UNION ALL selects all the values and doesn’t remove any duplicates. In this example, no duplicates are produced so they return the same result set. However, if we change it to (for example purposes only)
var query = from o in db.Orders
where o.ShipCountry == “UK”
select o;
var query2 = from o in db.Orders
where o.ShipCountry == “UK”
select o;
Then UNION will return 56 rows, while UNION ALL returns 112 and that is the difference between .Union() and .Concat().
Good tip! It helped, thanks a lot
Thanks!