none
Using Sum() in EntitySQL Query RRS feed

  • Question

  • Hi, 

    I'm using EF 5.0 RC in my application. I have two entities - Batch and Group. Batch has a collection of Groups.

    I'm trying two return a total amount of items inside of Groups in each Batch along with the Batches InvoiceNumber and Id.

    select b.Id, b.InvoiceNumber, ( select value sum( g.TotalAmount ) from b.Groups as g ) from Cnt.Batches as b

    Id and InvoiceNumber are OK, but Sum() returns a collection instead of one Integer value, which contains one element (which is the total amount of Groups)

    What am I doing wrong?

    Thank you


    Clarity VS Precision



    Thursday, August 9, 2012 7:56 AM

Answers

  • Hi,

    I am not sure if ToatalAmount is the sum of groups or not. On my side, this works:

                   var str = @"select value (select value count(u.Id) from p.Groups as u) from Cnt.Batches as p";
                    ObjectQuery<IEnumerable<Int32>> orderQuery = new ObjectQuery<IEnumerable<Int32>>(str, context, MergeOption.NoTracking);
                    //Console.WriteLine(orderQuery);
                    foreach (List<Int32> i in orderQuery)
                    {
                        Console.WriteLine(i[0]);
                    }

    • Marked as answer by Alexander Sun Thursday, August 23, 2012 8:23 AM
    Wednesday, August 15, 2012 7:11 AM
  • Hi,

    thanks for the reply,

    I know that query returns IEnumerables, I just wanted to make it return Int32 directly so that you could write 

    foreach (Int32 i in orderQuery)
    {
          Console.WriteLine(i[0]);
    }

    Thanks again,

    Giorgi


    Clarity VS Precision

    • Marked as answer by Alexander Sun Thursday, August 23, 2012 8:23 AM
    Wednesday, August 15, 2012 8:31 AM
  • Hi,

    Putting the sum outside the inner query, not inside, should do what you are describing.

    select b.Id, b.InvoiceNumber, sum( select value g.TotalAmount from b.Groups as g ) from Cnt.Batches as b

    Rather than

    select b.Id, b.InvoiceNumber, ( select value sum( g.TotalAmount ) from b.Groups as g ) from Cnt.Batches as b

    Aggregates in ESQL accept expressions, so this is possible. The reason why you are having this problem is that a subquery will always produce a reader, even if only one element is present.

    Another way to solve the problem would be to add ANYELEMENT in front of the subquery:

    select b.Id, b.InvoiceNumber, ANYELEMENT( select value sum( g.TotalAmount ) from b.Groups as g ) from Cnt.Batches as b

    ANYELEMENT flattens a given collection so that the customer will get only the first result.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Thursday, August 16, 2012 3:46 PM
    Moderator

All replies

  • Hi Giorgy,

    Shouldn't you be using the .Count() method or the .Count property instead? The Sum computes the sum of a sequence of numeric values. I don't think this is want to want.

    Anyways, give it a try like this:

    var result = from b in Cnt.Batches select new { b.Id, b.InvoiceNumber, b.Groups.Count };

    Hope this helps!


    Cheers, Carlos Sardo

    Thursday, August 9, 2012 2:00 PM
  • Hi,

    you could try the following as a nested select... sum can be used as per the following example...

    (from b in Batch
    select b.Groups.Count).Sum()

    Hope this helps.


    • Edited by devastated Thursday, August 9, 2012 3:49 PM Amended for the count property Carlos suggested
    Thursday, August 9, 2012 3:47 PM
  • I'm totally sorry, I messed up the code, in my application I have the following code:

    select b.Id, b.InvoiceNumber, ( select value sum( g.TotalAmount ) from b.Groups as g ) from Cnt.Batches as b

    to get the sum of total amounts of Items inside of Groups

    I'm changing the code above as well.

    Thanks,

    Giorgi


    Clarity VS Precision

    Friday, August 10, 2012 5:09 AM
  • Thank Carlos,

    I'd like to do this using ESql instead of Linq to Entities.

    Regards,

    Giorgi


    Clarity VS Precision

    Friday, August 10, 2012 5:12 AM
  • Hi,

    I can try and help you out, but first i'd like to understand why the solution has to be ESql.

    There is a fairly small set of scenarios where ESql is significantly better than Linq or something like it, so i'd like to understand your problem domain a bit more. Could you give some more information about it?

    Thanks.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.


    Friday, August 10, 2012 4:40 PM
    Moderator
  • Hi,

    there are two simple reasons, first - I'm constructing the command using the stringbuilder depending on the method's parameter values; and second - I just want to find out how it's done using ESql.

    Thank you,

    Giorgi


    Clarity VS Precision

    Monday, August 13, 2012 5:32 AM
  • Hi,

    I am not sure if ToatalAmount is the sum of groups or not. On my side, this works:

                   var str = @"select value (select value count(u.Id) from p.Groups as u) from Cnt.Batches as p";
                    ObjectQuery<IEnumerable<Int32>> orderQuery = new ObjectQuery<IEnumerable<Int32>>(str, context, MergeOption.NoTracking);
                    //Console.WriteLine(orderQuery);
                    foreach (List<Int32> i in orderQuery)
                    {
                        Console.WriteLine(i[0]);
                    }

    • Marked as answer by Alexander Sun Thursday, August 23, 2012 8:23 AM
    Wednesday, August 15, 2012 7:11 AM
  • Hi,

    thanks for the reply,

    I know that query returns IEnumerables, I just wanted to make it return Int32 directly so that you could write 

    foreach (Int32 i in orderQuery)
    {
          Console.WriteLine(i[0]);
    }

    Thanks again,

    Giorgi


    Clarity VS Precision

    • Marked as answer by Alexander Sun Thursday, August 23, 2012 8:23 AM
    Wednesday, August 15, 2012 8:31 AM
  • Hi,

    Putting the sum outside the inner query, not inside, should do what you are describing.

    select b.Id, b.InvoiceNumber, sum( select value g.TotalAmount from b.Groups as g ) from Cnt.Batches as b

    Rather than

    select b.Id, b.InvoiceNumber, ( select value sum( g.TotalAmount ) from b.Groups as g ) from Cnt.Batches as b

    Aggregates in ESQL accept expressions, so this is possible. The reason why you are having this problem is that a subquery will always produce a reader, even if only one element is present.

    Another way to solve the problem would be to add ANYELEMENT in front of the subquery:

    select b.Id, b.InvoiceNumber, ANYELEMENT( select value sum( g.TotalAmount ) from b.Groups as g ) from Cnt.Batches as b

    ANYELEMENT flattens a given collection so that the customer will get only the first result.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Thursday, August 16, 2012 3:46 PM
    Moderator