none
LINQ Join combined with GroupBy RRS feed

  • Question

  • Does anyone know how to translate this relatively simple query to LINQ?

     

    SELECT c.FirstName, c.LastName, SUM(o.Amount) as TotalAmount

    FROM Customers c

    INNER JOIN Orders o

    ON c.CustomerNumber = o.CustomerNumber

    GROUP BY c.Firstname, c.LastName

     

    I've been doing something like the following but I really want to figure out how to combine them into one query:

     

    var query = from a in db.Customers

                     join b in db.Orders on a.CustomerNumber equals b.CustomerNumber

                     select new { a.FirstName, a.LastName, o.Amount };

     

    var summedQuery = from a in query

                                  group a by new { a.FirstName, a.LastName } into b

                                  select new { b.Key.FirstName, b.Key.LastName, TotalAmount = b.SUM(a => a.Amount) };

     

    Thanks,

    Nick

    Tuesday, July 1, 2008 8:21 PM

Answers

  • Here you are.

     

    from c in db.Customers

    join o in db.Orders on c.CustomerNumber equals o.CustomerNumber

    group new {c, o} by new {

    c.FirstName,

    c.LastName

    } into g

    select new {

    g.Key.FirstName,

    g.Key.LastName,

    TotalAmount = g.Sum(p => p.o.Amount)

    }

     

    This LINQ statement has been produced by Linqer -  SQL to LINQ convertor from www.sqltolinq.com

     

     

    Wednesday, July 2, 2008 6:37 AM

All replies

  • Here you are.

     

    from c in db.Customers

    join o in db.Orders on c.CustomerNumber equals o.CustomerNumber

    group new {c, o} by new {

    c.FirstName,

    c.LastName

    } into g

    select new {

    g.Key.FirstName,

    g.Key.LastName,

    TotalAmount = g.Sum(p => p.o.Amount)

    }

     

    This LINQ statement has been produced by Linqer -  SQL to LINQ convertor from www.sqltolinq.com

     

     

    Wednesday, July 2, 2008 6:37 AM
  • If you have an association between customers and orders, you should be able to simplify the query and not require the explicit grouping at all.

     

    var summedQuery = from c in db.Customers

                                  select new

                                  {

                                      c.FirstName,

                                      c.LastName,

                                      TotalAmount = c.Orders.Sum(o => o.Amount)

                                  };

     

    Jim Wooley

    www.ThinqLinq.com

    Wednesday, July 2, 2008 2:35 PM
    Moderator