时间戳

首页 > 解决方案 > 如何在 LINQ 中结合 LEFT JOIN、GROUP BY 和 SUM？

sql - 如何在 LINQ 中结合 LEFT JOIN、GROUP BY 和 SUM？

问题描述

我想在 C# 中使用 LINQ 编写我的 SQL 查询。

这是我的查询（对于每个组织，显示其 ID、名称、董事、分析师和“收入”表中的总收入）：

SELECT
    o.Id,
    o.Name,
    d.FirstName + ' ' + d.LastName AS Director,
    a.FirstName + ' ' + a.LastName AS Analyst,
    SUM(i.Amount) AS TotalIncome
FROM Organization o
    LEFT JOIN Employee d ON o.DirectorId = d.Id
    LEFT JOIN Employee a ON o.AnalystId = a.Id
    LEFT JOIN Income i ON o.Id = i.OrganizationId
GROUP BY
    o.Id, 
    o.Name, 
    d.FirstName,
    d.LastName,
    a.FirstName,
    a.LastName

我已经尝试过这样的事情：

from o in Organization
join director in Employee on o.DirectorId equals director.Id into directorJoin
from d in directorJoin.DefaultIfEmpty()
join analyst in Employee on o.AnalystId equals analyst.Id into analystJoin
from a in analystJoin.DefaultIfEmpty()
join income in Income on o.Id equals income.OrganizationId into incomeJoin
group o by new 
{
    o.Id,
    o.Name,
    Director = d.FirstName + ' ' + d.LastName,
    Analyst = a.FirstName + ' ' + a.LastName,
    TotalIncome = (decimal?)incomeJoin.Sum(x => x.Amount)
} into g
  select g.Key

但我的程序抛出了一个异常：

不支持嵌套查询。Operation1='GroupBy' Operation2='MultiStreamNest'

我将不胜感激任何帮助。

标签： sqlsql-serverentity-frameworklinq

解决方案

我最终使用了Gert Arnold建议的Navigation Properties。这是我的问题及其解决方案的简化版本：

楷模：

public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class Income
{
    public int Id { get; set; }
    public int OrganizationId { get; set; }
    public decimal Amount { get; set; }
}

public class Organization
{
    public int Id { get; set; }
    public string Name { get; set; }

    public int AnalystId { get; set; }
    public virtual Employee Analyst { get; set; }

    public int DirectorId { get; set; }
    public virtual Employee Director { get; set; }

    public virtual ICollection<Income> Incomes { get; set; }
}

询问：

class Program
{
    static void Main(string[] args)
    {
        var context = new Context();

        var queryResult = context.Organizations.Select(x => new
        {
            x.Id,
            x.Name,
            Director = x.Director.FirstName + " " + x.Director.LastName,
            Analyst = x.Analyst.FirstName + " " + x.Analyst.LastName,
            TotalIncome = x.Incomes.Sum(y => y.Amount)
        });

    }
}

推荐阅读