how to convert sql union to linq

Question

I have the following Transact SQL query using a union. I need some pointers as to how this would look in LINQ i.e some examples wouldbe nice or if anyone can recommend a good tutorial on UNIONS in linq.

select top 10 Barcode, sum(ItemDiscountUnion.AmountTaken) from (SELECT d.Barcode,SUM(AmountTaken) AmountTaken   FROM [Aggregation].[dbo].[DiscountPromotion] d    GROUP BY d.Barcode    UNION ALL    SELECT i.Barcode,SUM(AmountTaken) AmountTaken   FROM [Aggregation].[dbo].ItemSaleTransaction i    group by i.Barcode)  ItemDiscountUnion    group by Barcode 

---

Note the original SQL is merging the 2 selects NOT concatenating them. I need to know how to merge the results i.e. removing duplicates and summing the rows amount value where there is duplication based on bar code.

Answer 1

Three useful Linq concepts operating on sets. Given set c and set e:

Concat gives you everything in c or e:

(From c In db.Customers Select c.Phone).Concat( _              From c In db.Customers Select c.Fax).Concat( _              From e In db.Employees Select e.HomePhone)  (From c In db.Customers _             Select Name = c.CompanyName, Phone = c.Phone).Concat(From e In db.Employees _             Select Name = e.FirstName & " " & e.LastName, Phone = e.HomePhone) 

Union also gives you everything in c and e, but removes any duplicates:

(From c In db.Customers _         Select c.Country).Union(From e In db.Employees _         Select e.Country) 

Except gives you everything in c that is not in e:

(From c In db.Customers _              Select c.Country).Except(From e In db.Employees Select e.Country)