Simple sql to Linq query with group by and aggregate functions

Question

I'm fighting with linq trying to learn the syntax and I can't figure out how to do the following simple query

SELECT DISTINCT
    user.firstname,
    user.lastname,
    COUNT(invoice.amount),
    SUM(invoice.amount)
FROM
    company_user
    INNER JOIN 
        user
    ON 
        company_user.user_id = user.user_id
    INNER JOIN 
        invoice
    ON
        invoice.user_id= invoice.user_id
WHERE 
    company_user.company_id = 1
GROUP BY
    user.firstname,
    user.lastname,
GO

Any help turning this into linq would be great.

Answer 1

The query you're after should be pretty close to this:

var query =
    from cu in company_user
    where cu.company_id == 1
    join u in user on cu.user_id equals u.user_id
    join i in invoice on u.user_id equals i.user_id
    group i.amount by new
    {
        u.firstname,
        u.lastname,
    } into gs
    select new
    {
        firstname = gs.Key.firstname,
        lastname = gs.Key.lastname,
        count = gs.Count(),
        sum = gs.Sum(),
    };

Enjoy!

Answer 2

Since op mentioned learning syntax, here's a fluent version:

company_user
    .Where(x => x.company_id == 1)
    .Join(
        user,
        x => x.user_id,
        x => x.user_id,
        (o,i) => new { 
            FirstName = i.firstName, 
            LastName = i.lastName,
            InvoiceCount = invoice.Count(y => y.user_id == o.user_id),
            InvoiceSum = invoice.Where(y => y.user_id == o.user_id).Sum(y => y.amount)
        }
    ).GroupBy(x => new { x.FirstName, x.LastName })
    .Distinct()