none
group by - multiple columns in different tables RRS feed

  • Question

  • I can't figure out how to translate the sql into linq for this sub query I am working on.
    All the examples I see for group by use one table. The sql I got groups by columns in multiple tables.

    SELECT  sbu_glac_seq, bsc_rid, sbu_bsc_rid, SUM (sbu_amount) AS amount
    FROM   bscenario, sbudget
    WHERE  bsc_name = pScenario AND bsc_rid = sbu_bsc_rid and bsc_year = pYear
    GROUP BY  sbu_glac_seq, bsc_rid, sbu_bsc_rid

    the linq I came up with was

    var fcur =
                   from b in tableSBUDGET.AsEnumerable()
                   join s in tableBSCENARIO.AsEnumerable()
                   on b.Field<int>("SBU_BSC_RID") equals s.Field<int>("BSC_RID")
                   where s.Field<string>("BSC_NAME") == scenario &&
                         s.Field<int>("BSC_YEAR") == year 
                   group b by new {b.Field<int>("SBU_GLAC_SEQ"), b.Field<int>("SBU_RID")} into g
    
                   select new
                   {
                      accoutID   = g.Key,
                      stuff      = g,   /// I don't get this?? How do I get fields from the result of the grouping?
                      amount     = g.Sum(b => b.Field<int>("SBU_AMOUNT")),
                   };
    It doesn't compile.
    Can anyone explain the steps?
    • Moved by Yichun_Feng Monday, November 30, 2009 3:05 AM LINQ to Dataset (From:LINQ to SQL)
    Saturday, November 28, 2009 12:05 PM

Answers

  • After more googling I came up with this:

    var fcur =
                   from b in tableSBUDGET.AsEnumerable()
                   join s in tableBSCENARIO.AsEnumerable()
                   on b.Field<int>("SBU_BSC_RID") equals s.Field<int>("BSC_RID")
                   where s.Field<string>("BSC_NAME") == scenario &&
                         s.Field<int>("BSC_YEAR") == year 
                   group b by new { accountID = b["SBU_GLAC_SEQ"], scenarioID = s["SBU_BSC_RID"]} into g
    
                   select new
                   {
                      accoutID   = g.Key.accountID,
                      scenarioID = g.Key.scenarioID,
                      amount     = g.Sum(b => b.Field<int>("SBU_AMOUNT")),
                   };
    Does that look equivalent to the original sql?
    Saturday, November 28, 2009 12:36 PM

All replies

  • After more googling I came up with this:

    var fcur =
                   from b in tableSBUDGET.AsEnumerable()
                   join s in tableBSCENARIO.AsEnumerable()
                   on b.Field<int>("SBU_BSC_RID") equals s.Field<int>("BSC_RID")
                   where s.Field<string>("BSC_NAME") == scenario &&
                         s.Field<int>("BSC_YEAR") == year 
                   group b by new { accountID = b["SBU_GLAC_SEQ"], scenarioID = s["SBU_BSC_RID"]} into g
    
                   select new
                   {
                      accoutID   = g.Key.accountID,
                      scenarioID = g.Key.scenarioID,
                      amount     = g.Sum(b => b.Field<int>("SBU_AMOUNT")),
                   };
    Does that look equivalent to the original sql?
    Saturday, November 28, 2009 12:36 PM
  • This will not use the above SQL because you are calling AsEnumerable on the tables, which effectively brings all the records in those tables into memory and does the query using LINQ to Objects.

    Also, where does the Field method come from? I only know it as a DataSet-specific operator, which, if that's the case, means you aren't using LINQ to SQL at all.
    Blog - http://blogs.rev-net.com/ddewinter/ Twitter - @ddewinter
    Sunday, November 29, 2009 9:46 PM
  • This will not use the above SQL because you are calling AsEnumerable on the tables, which effectively brings all the records in those tables into memory and does the query using LINQ to Objects.

    Also, where does the Field method come from? I only know it as a DataSet-specific operator, which, if that's the case, means you aren't using LINQ to SQL at all.
    Blog - http://blogs.rev-net.com/ddewinter/ Twitter - @ddewinter

    I am using LINQ to DataSet.
    Is there a more appropriate forum? If so, can you move the post?
    Monday, November 30, 2009 1:45 AM
  • Hi Christopher,

    The query you post looks correct. Do you still have any problem?


    Best Regards
    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, December 2, 2009 8:50 AM
  • Hi Christopher,

    The query you post looks correct. Do you still have any problem?


    Best Regards
    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

    I think I got it figured out. It just took a while.




    Thursday, December 3, 2009 5:35 AM
  • I'm having a similar problem.  could you post the solution please?

    thanks

    -wg

    Wednesday, April 21, 2010 5:40 PM
  • Hi Christopher,

    This is not working, Did you check this before you using it ???

    It is not getting compilation...

    It is showing Error as follows "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

    Is there any other way to achieve this (Group By with Multiple Tables in LINQ)

    Thanks, L.Bala

    Monday, July 12, 2010 9:32 AM