使用带linq的条件合并两个表
本文关键字：两个 合并 linq 条件 | 更新日期: 2023-09-27 18:25:48
我试图用一个linq句子来解决问题，但我不知道是否可以这样做。我有一个名为PRICES的表，其中包含以下字段：
pkey: int
region: int?
product_type: int
product_size: int
price: double
desc: string
唯一密钥为：product_type+product_size
我想做一个查询，返回WHERE区域==17的所有行（这是我的第一组行）AND希望添加区域为null的所有行（这是我的第二排）但是如果两个集合中都有product_type和productsize相同的行，那么我希望在最终结果中只显示第一个集合的行。
示例：
pkey | region | product_type | product_size | price | desc
1, null, 20, 7, 2.70, salad1
2, null, 20, 3, 2.50, salad7
3, 17, 20, 7, 1.90, saladspecial
4, 17, 20, 5, 2.20, other
我想要一个返回以下内容的linq查询：
2, null, 20, 3, 2.50, salad7
3, 17, 20, 7, 1.90, saladspecial
4, 17, 20, 5, 2.20, other
（注意，具有pkey 1的行被丢弃，因为具有pkey 3的行具有相同的product_type和product_size）
var query1 = from p in PRICES where p.region == 17
select p;
var query2 = from p in PRICES where p.region is null
select p;
问题：
如何联接query1和query2以获得期望的输出？
只需一个查询就可以完成？
以下查询仅选择区域为17或null的价格，并按唯一关键字{ p.product_type, p.product_size }对其进行分组。然后，它检查组是否包含至少一个具有区域17的价格。如果是，则我们从组中选择该区域的所有价格（并跳过null区域的价格）。否则，我们返回整个组（它只有空区域）：
var query = from p in PRICES.Where(x => x.region == 17 || x.region == null)
group p by new { p.product_type, p.product_size } into g
from pp in g.Any(x => x.region == 17) ?
g.Where(x => x.region == 17) : g
select pp;
输入：
1 null 20 7 2.7 salad1 // goes to group {20,7} with region 17 price
2 null 20 3 2.5 salad7 // goes to group {20,3} without region 17 prices
3 17 20 7 1.9 saladspecial // goes to group {20,7}
4 17 20 5 2.2 other // goes to group {20,5}
输出：
2 null 20 3 2.5 salad7
3 17 20 7 1.9 saladspecial
4 17 20 5 2.2 other
EDIT上面的查询可以很好地处理内存中的对象（即LINQ to objects），但LINQ to Entitis功能并没有那么强大，它不支持嵌套查询。因此，对于Entity Framework，您将需要两个查询——一个是获取null区域的价格，该区域在组中没有17区域的价格；另一个是17:区域的价格
var pricesWithoutRegion =
db.PRICES.Where(p => p.region == 17 || p.region == null)
.GroupBy(p => new { p.product_type, p.product_size })
.Where(g => !g.Any(p => p.region == 17))
.SelectMany(g => g);
var query = db.PRICES.Where(p => p.region == 17).Concat(pricesWithoutRegion);
实际上EF在对服务器的一个UNION查询中执行两个子查询。将生成以下SQL（我删除了desc和price列以适应屏幕）：
SELECT [UnionAll1].[pkey] AS [C1],
[UnionAll1].[region] AS [C2],
[UnionAll1].[product_type] AS [C3],
[UnionAll1].[product_size] AS [C4]
FROM (SELECT [Extent1].[pkey] AS [pkey],
[Extent1].[region] AS [region],
[Extent1].[product_type] AS [product_type],
[Extent1].[product_size] AS [product_size]
FROM [dbo].[Prices] AS [Extent1] WHERE 17 = [Extent1].[region]
UNION ALL
SELECT [Extent4].[pkey] AS [pkey],
[Extent4].[region] AS [region],
[Extent4].[product_type] AS [product_type],
[Extent4].[product_size] AS [product_size]
FROM (SELECT DISTINCT [Extent2].[product_type] AS [product_type],
[Extent2].[product_size] AS [product_size]
FROM [dbo].[Prices] AS [Extent2]
WHERE ([Extent2].[region] = 17 OR [Extent2].[region] IS NULL) AND
(NOT EXISTS
(SELECT 1 AS [C1] FROM [dbo].[Prices] AS [Extent3]
WHERE ([Extent3].[region] = 17 OR [Extent3].[region] IS NULL)
AND ([Extent2].[product_type] = [Extent3].[product_type])
AND ([Extent2].[product_size] = [Extent3].[product_size])
AND (17 = [Extent3].[region])
))) AS [Distinct1]
INNER JOIN [dbo].[Prices] AS [Extent4]
ON ([Extent4].[region] = 17 OR [Extent4].[region] IS NULL)
AND ([Distinct1].[product_type] = [Extent4].[product_type])
AND ([Distinct1].[product_size] = [Extent4].[product_size]))
AS [UnionAll1]
顺便说一句，GroupBy被翻译成有条件的内部联接，这让我很惊讶。
我认为你应该选择1个查询，对于2个查询，我们必须重复一些事情：
//for 2 queries
var query = query1.Union(query2.Except(query2.Where(x=>query1.Any(y=>x.product_type==y.product_type&&x.product_size==y.product_size))))
.OrderBy(x=>x.pkey);
//for 1 query
//the class/type to make the group key
public class GroupKey
{
public int ProductType { get; set; }
public int ProductSize { get; set; }
public override bool Equals(object obj)
{
GroupKey gk = obj as GroupKey;
return ProductType == gk.ProductType && ProductSize == gk.ProductSize;
}
public override int GetHashCode()
{
return ProductSize ^ ProductType;
}
}
//-------
var query = list.Where(x => x.region == 17 || x.region == null)
.GroupBy(x => new GroupKey{ProductType = x.product_type, ProductSize = x.product_size })
.SelectMany<IGrouping<GroupKey,Price>,Price,Price>(x => x.Where(k => x.Count(y => y.region == 17) == 0 || k.region == 17), (x,g) => g)
.OrderBy(x=>x.pkey);