none
EntityFramework can sum without grouping? RRS feed

  • Question

  • hello.

    nice to meet you.

    my name is geomje lim.

    i want like this result query.

    Select SUM(columnName) FROM TableName

    but i think entityframework not support that query result.

    please help T.T

    current my code.

    thank you.

    from oc in table1
                           join o in table2
                           on oc.id1 equals o.id1
                           join occ in table3
                           on new { oc.id1, oc.id2 } equals new { occ.id1, occ.id2 } into g
                           from ocr in g.DefaultIfEmpty()
                           where ocr.id1 == null
                           group oc by 0 into g2
                           select new { Sum = g2.Sum(p=>p.sumcolumn)};

    i want oc's sumcolumn sum. T.T

    • Edited by 임검제 Wednesday, October 8, 2014 8:56 AM
    • Moved by Kristin Xie Thursday, October 9, 2014 3:13 AM
    Wednesday, October 8, 2014 8:56 AM

Answers

  • Hi geomje,

    an aggregate like SUM for a table or any other query source can be expressed as:

    var sum = (from t in table select t.column).Sum();

    as it returns a single scalar value.

    Note: Aggregates (except Count(*)) remove any null values and the base query shouldn't return such values, as it may lead to a inefficient query. You should check this for your initial query, especially if left joins are involved.

    As "p" doesn't exists in your query, just as a hint:

    var sumColum = (from oc in table1
    		join o in table2
    		on oc.id1 equals o.id1
    		join occ in table3
    		on new { oc.id1, oc.id2 } equals new { occ.id1, occ.id2 } into g
    		from ocr in g.DefaultIfEmpty()
    		where ocr.id1 == null
    		select sumcolumn)
    	.Sum();

    Regards, Elmar

    • Marked as answer by 임검제 Friday, October 10, 2014 4:58 AM
    • Unmarked as answer by 임검제 Friday, October 10, 2014 4:58 AM
    • Marked as answer by 임검제 Friday, October 10, 2014 4:58 AM
    Wednesday, October 8, 2014 9:59 AM

All replies

  • Hi geomje,

    an aggregate like SUM for a table or any other query source can be expressed as:

    var sum = (from t in table select t.column).Sum();

    as it returns a single scalar value.

    Note: Aggregates (except Count(*)) remove any null values and the base query shouldn't return such values, as it may lead to a inefficient query. You should check this for your initial query, especially if left joins are involved.

    As "p" doesn't exists in your query, just as a hint:

    var sumColum = (from oc in table1
    		join o in table2
    		on oc.id1 equals o.id1
    		join occ in table3
    		on new { oc.id1, oc.id2 } equals new { occ.id1, occ.id2 } into g
    		from ocr in g.DefaultIfEmpty()
    		where ocr.id1 == null
    		select sumcolumn)
    	.Sum();

    Regards, Elmar

    • Marked as answer by 임검제 Friday, October 10, 2014 4:58 AM
    • Unmarked as answer by 임검제 Friday, October 10, 2014 4:58 AM
    • Marked as answer by 임검제 Friday, October 10, 2014 4:58 AM
    Wednesday, October 8, 2014 9:59 AM
  • Hahaha...

    i am Fool!

    very nice answer!

    thank you Elmar Boye!

    your very nice person!

    good luck!

    Friday, October 10, 2014 4:58 AM