如何将SQL的UNION转换为LINQ

Question

我有一个使用UNION的Transact SQL查询，需要一些指针，了解如何在LINQ中实现，比如一些示例会很好，或者如果有人可以推荐一个关于LINQ中UNIONS的好教程。

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

请注意，原始 SQL 是将 2 个选择 合并 而不是连接它们。我需要知道如何合并结果，即去除重复项并对重复基于条形码的行金额进行求和。

- lowlyintern

---

http://msdn.microsoft.com/en-us/library/bb386979.aspx - Andrey

只需按照 @Ian-P 的建议使用 Union 运算符。 - Guillaume86

Answer 1

三个有用的Linq概念操作集合。给定集合c和集合e：

Concat给出了c或者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 还会给你 c 和 e 中的所有内容，但会去除重复项：

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

< p >Except 返回 c 中所有不在 e 中的元素：

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

- Amit Rai Sharma

---

“Concat”，“Union”和“Except”是不同的东西：“Concat”仅将两个序列合并成一个，“Union”会合并但删除重复项（去重），而“Except”则完全不同：它返回第一个序列中不属于第二个序列的元素。（最后一个是“Intersect”：返回2个序列中的公共元素） - Guillaume86

正如Guillaume86所提到的，这篇文章是不正确的。稍微改一下措辞就可以轻松解决问题。 - Griffin

我认为值得注意的是，像Union一样，Except和Intersect也会删除任何重复项，包括源序列中的重复项。 - NetMage

Answer 2

下面是一个泛型联合的示例，与您所提出的场景无关：

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

- Ian P

Answer 3

这里有 101个Linq示例 - 其中包括两个联合示例 Union1 和 Union2

这个Linq语句应该会给你与你的SQL相同的结果： (对于我测试的记录集来说是这样的)

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

- Nicholas Murray

Answer 4

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

- sohail naseer

Answer 5

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

- lareb