none
Convert SQL to EF query with (SUM / CAST / GROUP BY, etc.) RRS feed

  • Question

  • Hi, I'm trying to convert the following SQL to an EF query:

    SELECT FooID, SUM(CAST(FooVal AS INT)) FooValCount FROM Foo GROUP BY FooID;
    

    I think it can be done but it isn't immediately clear to me - was wondering if the wizards on here might be able to whip up a quick sample?  FooVal is a bit type -- need to cast as an int to be able to sum it.

    Thanks in advance

     

    Thursday, October 13, 2011 2:57 AM

Answers

  • Hi zenocon,

    I think this would do it:

     

      var results = from f in db.Foos
                             group f by f.FooId
                                 into FooGroup
                                 select new { FooID = FooGroup.Key, FooValCount= FooGroup.Sum(c => int.Parse(c.FooVal.ToString()))};


     


    Tom Overton

    • Edited by Tom_Overton Thursday, October 13, 2011 3:32 AM forgot to convert the bit to int
    • Marked as answer by zenocon Thursday, October 13, 2011 3:38 AM
    Thursday, October 13, 2011 3:20 AM

All replies

  • Hi zenocon,

    I think this would do it:

     

      var results = from f in db.Foos
                             group f by f.FooId
                                 into FooGroup
                                 select new { FooID = FooGroup.Key, FooValCount= FooGroup.Sum(c => int.Parse(c.FooVal.ToString()))};


     


    Tom Overton

    • Edited by Tom_Overton Thursday, October 13, 2011 3:32 AM forgot to convert the bit to int
    • Marked as answer by zenocon Thursday, October 13, 2011 3:38 AM
    Thursday, October 13, 2011 3:20 AM
  • Thanks Tom -- looks good...will get me started at least.  My real example is a bit more complicated, but this works me through the syntax enough that I can resolve the rest.  Thanks again.
    Thursday, October 13, 2011 3:39 AM
  • Quick follow up: I've coded this up now, but unfortunately I'm seeing this error:

    LINQ to Entities does not recognize the method 'Int32 Parse(System.String)' method, and this method cannot be translated into a store expression.

    It seems some people get around this by storing the conversion in a variable and using that in queries, but here I don't think that applies.  It looks like I'll have to do something like this: http://stackoverflow.com/questions/5971521/linq-to-entities-does-not-recognize-the-method-double-parsesystem-string-meth

    Just curious if you or anyone has an idea for a workaround that is different from that link?

    Friday, October 14, 2011 2:42 AM