如何将sql union转换为linq [英] how to convert sql union to linq
问题描述
我有以下使用联合的Transact SQL查询. 我需要一些关于在LINQ中的外观的指针,例如一些示例 会很好,或者如果有人可以在linq上推荐有关UNIONS的好教程.
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
请注意,原始SQL正在合并,这2个选择不将它们连接在一起. 我需要知道如何合并结果,即删除重复项并根据条形码对存在重复项的行金额求和.
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.
推荐答案
对集合进行操作的三个有用的Linq概念.给定集合c
和集合e
:
Three useful Linq concepts operating on sets. Given set c
and set e
:
Concat为您提供c
或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)
联盟还为您提供c
和e
中的所有内容,但会删除所有重复项:
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)
除了在c
中为您提供的所有内容以外,在e
中没有其他内容:
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)
这篇关于如何将sql union转换为linq的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!