CODEWITHSUNDEEP
sqlsql-serverentity-frameworklinq
Michał Szymański
Michał Szymański

Reputation: 354

How to combine LEFT JOIN, GROUP BY and SUM in LINQ?

I want to write my SQL query using LINQ in C#.

Here is my query (for each Organization display its Id, Name, Director, Analyst and total sum of income from table "Income"):

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

I've already tried something like this:

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

but my program throws me an exception:

The nested query is not supported. Operation1='GroupBy' Operation2='MultiStreamNest'

I will greatly appreciate any help.

Upvotes: 0

Views: 4355

Answers (2)

Michał Szymański
Michał Szymański

Reputation: 354

I ended up using Navigation Properties suggested by Gert Arnold. Here is simplified version of my problem and its solution:

Models:

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; }
}

Query:

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)
        });

    }
}

Upvotes: 1

D Stanley
D Stanley

Reputation: 152566

You don't want to do the summation within the group key - do that outside the group:

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,
    i.Amount
} into g
  select new 
{
    g.Key.Id,
    g.Key.Name,
    g.Key.Director,
    g.Key.Analyst,
    TotalIncome = (decimal?)g.Sum(i => i.Amount)
}

You might also ned to check for null values since you're LEFT joining to d, i, and a

Upvotes: 0

Related Questions