Linq to SQL With Left Outer Join and Group By With Sum - How To

Question

Hi ! I'm trying to transform the SQL Query below into Linq to SQL

select Categorias.IdCategoria, Categorias.Nome, SUM(lancamentos.valor)
from   lancamentos
left outer join Categorias on Lancamentos.IdCategoria = Categorias.IdCategoria
where  Month(DataLancamento) = 11
and    Credito = 1
and    Lancamentos.Ocultar = 0
group by Categorias.IdCategoria, Categorias.Nome

This is what I've done

from    lancamento in Lancamentos
where   lancamento.Credito == true
&&      lancamento.Ocultar == false
&&      lancamento.DataLancamento.Month == 10
join    categoria in Categorias on lancamento.IdCategoria equals categoria.IdCategoria into temp
from    lancamentoJoinCategoria in temp.DefaultIfEmpty()
group   lancamentoJoinCategoria by new { lancamentoJoinCategoria.IdCategoria, lancamentoJoinCategoria.Nome } into x
select  new {
        IdCategoria = (int?)x.Key.IdCategoria
        , Nome = x.Key.Nome
}

How do I add the SUM(lancamentos.valor) to the linq to sql above ?

Answer 1

You use the .Sum() method.

Eg;

Public Sub LinqToSqlCount03()
    Dim q = (From o In db.Orders _
        Select o.Freight).Sum()

Console.WriteLine(q)
End Sub

Answer 2

Hi,

according to MSDN there is no query expression equivalent to the Sum() operation. I provided a little sample how you could use the Method Syntax of Sum() in a query.

Some query operations, such as Count or Max, have no equivalent query expression clause and must therefore be expressed as a method call. Method syntax can be combined with query syntax in various ways. For more information, see LINQ Query Syntax versus Method Syntax (C#).

var example = new[]
    {
        new { Count = 1, Name = "a" }, new { Count = 2, Name = "b" },
        new { Count = 2, Name = "c" }, new { Count = 2, Name = "c" }
    };

var result = from x in example
                select new 
                {
                    x.Name, 
                    Sum = (from y in example 
                           where y.Count.Equals(2) 
                               && y.Name==x.Name
                           select y.Count).Sum()
                };
var distinct = result.Distinct().ToList();