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.

http://msdn.microsoft.com/en-us/library/bb386979.aspx – Andrey Apr 30 '10 at 11:45 — Andrey, Apr 30 '10 at 11:45
just use the Union operator as @Ian-P suggested – Guillaume86 May 08 '12 at 15:59 — Guillaume86, May 08 '12 at 15:59

score 48 · Answer 1 · edited Jan 05 '15 at 21:35

48

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)

edited Jan 05 '15 at 21:35

Ben

54,723
49
178
224

answered Apr 30 '10 at 11:46

Amit Rai Sharma

4,015
1
28
35

25

`Concat`, `Union` and `Except` are different things: `Concat` will just merge two sequences into one, `Union` will merge but remove duplicates (distinct), and `Except` is completely different: it returns the elements of the first sequence that do not belong to the second sequence. (the last one is `Intersect`: returns the common elements from 2 sequences) – Guillaume86 May 08 '12 at 15:57
As Guillaume86 mentioned, this post is incorrect. A little wording change would easily fix the problem. – Griffin Oct 21 '13 at 19:23
I think it is worth noting that like `Union`, `Except` and `Interset` also remove any duplicates, including duplicates in the source sequences. – NetMage Apr 25 '17 at 20:25

score 13 · Answer 2 · answered Apr 30 '10 at 14:47

Here's an example of a generic union, without regard to the scenario you posted:

var something =
                (from e in _repository
                 select new { e.Property1, e.Property2 }).Union(
                (from e in _repository
                 select new { e.Property1, e.Property2 }));

score 5 · Answer 3 · edited Jul 28 '15 at 09:27

There are the 101 Linq Samples - with two union samples Union1 and Union2

This Linq statement should get you the same results as your SQL: (it has for me on a test record-set)

var results = (from a in (from d in DiscountPromotions
            group d by d.BarCode into g
            select new { 
                BarCode = g.Key,
                AmountTaken = g.Sum(p => p.AmountTaken)
                }).Union(from i in ItemSaleTransactions
            group i by i.BarCode into o
            select new { 
                BarCode = o.Key,
                AmountTaken = o.Sum(i => i.AmountTaken)
                }) group a by a.BarCode into b
                select new {
                    BarCode = b.Key,
                    AmountTaken = b.Sum(c => c.AmountTaken)
                });

score 0 · Answer 4 · answered Jun 21 '23 at 11:48

return await (
                                 from b in _db.Brands
                                 where b.brand_id == 0
                                 select new brandInfo
                                 {
                                     brand_id = b.brand_id,
                                     brand_name = b.brand_name
                                 }).Union<brandInfo>(
                                        from pd in _db.Product_Details
                                        join b in _db.Brands on pd.brand_id equals b.brand_id
                                        where pd.cate_id == cate_id && pd.pro_id == pro_id || b.brand_id == 0
                                        select new brandInfo
                                        {
                                            brand_id = b.brand_id,
                                            brand_name = b.brand_name
                                        }
                              ).Distinct().OrderBy(o=>o.brand_name).ToListAsync();

score 0 · Answer 5 · answered Jun 21 '23 at 16:46

var discountPromotionQuery =
from d in dbContext.DiscountPromotion
group d by d.Barcode into g
select new
{
    Barcode = g.Key,
    AmountTaken = g.Sum(d => d.AmountTaken)
};

var itemSaleTransactionQuery =
from i in dbContext.ItemSaleTransaction
group i by i.Barcode into g
select new
{
    Barcode = g.Key,
    AmountTaken = g.Sum(i => i.AmountTaken)
};

var result =
(from d in discountPromotionQuery
 select new
 {
     d.Barcode,
     AmountTaken = d.AmountTaken
 })
.Concat(from i in itemSaleTransactionQuery
        select new
        {
            i.Barcode,
            AmountTaken = i.AmountTaken
        })
.GroupBy(x => x.Barcode)
.Select(g => new
{
    Barcode = g.Key,
    AmountTaken = g.Sum(x => x.AmountTaken)
})
.Take(10);

how to convert sql union to linq

5 Answers5

Linked