如何在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'

我将不胜感激任何帮助。

米恰尔·斯基曼斯基

我最终使用了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)
        });

    }
}

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。