Entity framework : Return grouped by value and sum of some columns in the same query

Question

I have one table and I want to do the following in entity frame work :

1- Return some columns from the object not all columns ( I created new class for this new type contain only the columns I need and return values in it in select statement.

2- Return columns should be grouped by some columns and two of this columns will contain the count value. ( which is my question).

For more clarification : I need to map the following query on querable object of my dataset in Entity Framework :

select SUM (Col1) SUM_Col1, SUM (Col2) SUM_Col2, COUNT (*) RECORDS, Col3, Col4, Col5
from myTable
where col3 = 'some value'
group by Col3, Col4, Col5;

My trial for grouping was as following :

        query = from record in context.tables
                group record by new
                {
                    record.Col3,
                    record.Col4,
                    record.Col5,
                } into g
                select new QueryResult
                {
                    Col1 = (need Sum of Col1 here),
                    Col2 = (need Sumn of Col2 here),
                    Col3 = g.Key.Col3,
                    Col4 = g.Key.Col4,
                    Col5 = g.Key.Col5,
                    Col6 = ( need count of grouped records here)

                };

I used it but what about returning sum & count in the same query ? — Ahmed Emad, Jun 07 '16 at 07:58
Possible duplicate of [Using GroupBy, Count and Sum in LINQ Lambda Expressions](http://stackoverflow.com/questions/3414080/using-groupby-count-and-sum-in-linq-lambda-expressions) — Saadi, Jun 07 '16 at 08:49

Anonymous Duck · Accepted Answer · 2016-06-07T08:19:00.590

Use Sum and Count

 query = from record in context.tables
                group record by new
                {
                    record.Col3,
                    record.Col4,
                    record.Col5,
                } into g
                select new QueryResult
                {
                    Col1 = g.Sum(x => x.Col1),
                    Col2 = g.Sum(x => x.Col2),
                    Col3 = g.Key.Col3,
                    Col4 = g.Key.Col4,
                    Col5 = g.Key.Col5,
                    col6 = g.Count()

                };

score 2 · Answer 2 · answered Jun 07 '16 at 08:18

Try this query:

var query = context.tables.GroupBy(r=>new{r.Col3, r.Col4, r.Col5})
                          .Select(g=>{
                                         Col1 = g.Sum(c=>c.Col1),
                                         Col2 = g.Sum(c=>c.Col2),
                                         Col3 = g.Key.Col3,
                                         Col4 = g.Key.Col4,
                                         Col5 = g.Key.Col5,
                                         Col6 = g.Count
                                     })

score 0 · Answer 3 · answered Jun 14 '17 at 16:28

Maybe this example can help you, it sums two columns and makes a join with another table

from e in _context.LearnResults
 join c in _context.Country on e.CountryId equals c.CountryId
 where c.DomainId.Equals("xx")
 group e by e.Country.Name into newCountry
 let Approved = newCountry.Sum(e => e.Approved)
 let Total = newCountry.Sum(e => e.Total)
 select new LearnResults() { CountryName = newCountry.Key, Approved= Approved, Total=Total };

Entity framework : Return grouped by value and sum of some columns in the same query

3 Answers3