Linq to SQL – Difference between Concat and Union?

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().

Technorati Tags: , , ,

2 thoughts on “Linq to SQL – Difference between Concat and Union?”

Leave a Reply to carlos Cancel reply

Your email address will not be published. Required fields are marked *