linq语法方法group by和subquery

4ioopgfo 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(275)

使用linq是否可以执行此查询？

SELECT 
    SUM([Weight]) * (SELECT TOP 1 Value FROM dbo.Prices WHERE '2020-04-02' BETWEEN [From] AND [To] AND ScrapId = CS.ScrapId), [FromCompany] ,CS.[ScrapId]
    FROM [dbo].[CollectedScrap] AS CS
    WHERE Date BETWEEN '2020-05-01' AND '2020-05-29' GROUP BY[ScrapId], [FromCompany]

最新测试：

content.table.Where(cs => cs.Date > from)
                .Where(cs => cs.Date < to)
                .GroupBy(collectedGroup => new { collectedGroup.ScrapId, collectedGroup.FromCompany})
                .Select(group => new
                {
                    total = group.Sum(c => c.Weight) * _context.Prices.Select(p => new { p.ScrapId, p.Value, p.From, p.To })
                                                                .Where(a => a.From > from)
                                                                .Where(a => a.To < to)
                                                                .Where(a => a.ScrapId == group.Select(gr => new { gr.ScrapId }).First().ScrapId)
                                                                .FirstOrDefault()

                }).ToList();

表之间没有直接关系。我使用子查询按日期获取正确的价格，并将其与group by查询的sum结果相乘。

sql group-by c#subquery linq

来源：https://stackoverflow.com/questions/62016117/linq-syntax-method-group-by-and-subquery

1条答案

按热度按时间

uemypmqf1#

content.table.Join(
                        _context.Prices,
                        collectedScrap => collectedScrap.ScrapId,
                        price => price.ScrapId,
                        (collectedScrap, price) => new
                        {
                            label = collectedScrap.Scrap.Label,
                            date = collectedScrap.Date,
                            price = price.Value,
                            scrap = collectedScrap.Scrap.Label,
                            weight = collectedScrap.Weight
                        }
                    )
                    .Where(d => d.date > from)
                    .Where(d => d.date < to)                    
                    .Select(result => new
                    {
                        scrap = result.label,
                        weight = result.weight,
                        price = result.price,
                        total = result.price * result.weight
                    }).ToList();

对于普通sql，我可以为连接使用一个条件。我怎样才能在连接中使用where。现在它的行为几乎像一个右连接（我需要将连接限制为一个结果）。----立即使用groupjoin进行测试
生成的sql:

SELECT [collectedScrap.Scrap].[Label] AS [scrap], [collectedScrap].[Weight], [price].[Value] AS [price], [price].[Value] * [collectedScrap].[Weight] AS [total]
FROM [CollectedScrap] AS [collectedScrap]
LEFT JOIN [Scrap] AS [collectedScrap.Scrap] ON [collectedScrap].[ScrapId] = [collectedScrap.Scrap].[ID]
INNER JOIN [Prices] AS [price] ON [collectedScrap].[ScrapId] = [price].[ScrapId]
WHERE ([collectedScrap].[Date] > @__from_0) AND ([collectedScrap].[Date] < @__to_1)

赞(0）回复(0）举报 2021-07-26

我来回答

linq语法方法group by和subquery

1条答案

相关问题

热门标签

最新问答