如何将sql联合转换为linq

Question

我有以下使用联合的 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 个选择而不是连接它们。 我需要知道如何合并结果，即删除重复项并根据条形码对存在重复的行金额值求和。

Answer 1

在集合上运行的三个有用的 Linq 概念。给定 set c 和 set 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)

Ex except 为您提供 c 中 e 中没有的所有内容：

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

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

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

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

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