how convert sql to linq

Question

How do I do this

Select top 10 Foo from MyTable

SELECT        TOP (30) Item, Descripcion, SUM(Amount) AS Suma         
FROM            Venat 
GROUP BY Item, Descripcion                          
ORDER BY Suma

in Linq to SQL?

with this only agrup by Item but not Description

var filtroprimeros30 = from nuevo in registrosVipDosAños
                     group nuevo by nuevo.Item into g         
                       select new
                        {
                            Item = g.Key,
                            Suma = g.Sum(nuevo => nuevo.Amount)

                        };

Sergey Berezovskiy · Accepted Answer · 2013-03-14 13:44:22Z

1

Use anonymous type for grouping:

var filtroprimeros30 = 
     (from nuevo in registrosVipDosAños
      group nuevo by new { nuevo.Item, nuevo.Description } into g  // here    
      select new {
          g.Key.Item,
          g.Key.Description,
          Suma = g.Sum(n => n.Amount)
      })
     .OrderBy(x => x.Suma)
     .Take(30);

I'd actually go this way (because query syntax has nice syntax for grouping, but do not have ability to take N items):

var items = from n in registrosVipDosAños
            group n by new { n.Item, n.Description } into g
            select new {
              g.Key.Item,
              g.Key.Description,
              Suma = g.Sum(x => x.Amount)
            };

var topItems = items.OrderBy(x => x.Suma).Take(30);

Query still will be executed only once, but now it's more readable.

edited Mar 14, 2013 at 13:44

answered Mar 14, 2013 at 13:09

Sergey Berezovskiy

237k44 gold badges441 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aducci Over a year ago

What about adding orderby g.Sum(n => n.Amount) between group and select?

Sergey Berezovskiy Over a year ago

@Aducci generated SQL will contain two Sum(Amount) columns in that case - one for ordering, and one for result. (according to LinqPad)

Sergey Berezovskiy Over a year ago

@user1178740 what donts work means? Exception? If you want to have items with biggest sum, you need to do OrderByDescending instead of OrderBy (same relates to you SQL)

Raphaël Althaus · Accepted Answer · 2013-03-14 13:14:41Z

1

syntax alternative

var filtroprimeros30 =  registrosVipDosAnos
                        .GroupBy(m => new {m.Item, m.Description})
                        .Select(g => new {
                           Item = g.Key.Item,
                           Description = g.Key.Description,
                           Suma = g.Sum(n => n.Amount)
                        })
                        .OrderBy(x => x.Suma)
                        .Take(30);

answered Mar 14, 2013 at 13:14

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Collectives™ on Stack Overflow

how convert sql to linq

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related