Ahmed Emad Ahmed Emad -4 years ago 51
SQL Question

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

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)

};

Answer Source

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()

                };
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download