CODEWITHSUNDEEP
c#sqllinqunion
lowlyintern
lowlyintern

Reputation: 331

how to convert sql union to linq

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.

Upvotes: 33

Views: 60874

Answers (5)

lareb
lareb

Reputation: 92

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);

Upvotes: 0

sohail naseer
sohail naseer

Reputation: 71

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

Upvotes: 0

Nicholas Murray
Nicholas Murray

Reputation: 13533

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)
                });

Upvotes: 5

Amit Rai Sharma
Amit Rai Sharma

Reputation: 4225

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)

Upvotes: 48

Ian P
Ian P

Reputation: 12993

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 }));

Upvotes: 13

Related Questions