none
Nested Join with sum in linq RRS feed

  • Question

  •  

    please any one have  idea,  how to convert following sql query into Linq ?

    query Like :
    -------------

    select
    SUM(a.Total)

    from

           

           


             SELECT SUM(INVENTORY) as Total  FROM  table1 c inner join table2  cp on cp.CODE = c.ITEMTYPECODE where cp.B2CBLOCKED = '0' and c.ITEMNO like 'a00%'

             group by c.ITEMTYPECODE,cp.DESCRIPTION ) 

    a


    Thanks.

     

     

     

     

     

     

     

    Wednesday, June 3, 2009 2:50 PM

Answers

  • I think Joe's point is that if you are going to sum the sums, and that's all you are getting out of the querying, then the 'group by' serves no purpose and can be removed.  Once the group-by is removed, you don't need the outer layer of the query anymore, since the inner sum is done over the whole set.
    Wayward LINQ Lacky
    • Marked as answer by Sanjay Patel Saturday, June 6, 2009 9:11 AM
    Friday, June 5, 2009 4:23 PM
    Moderator

All replies

  • Your query doesn't seem to fully make sense. The following:

       SELECT SUM ( INVENTORY ) as Total  FROM   table1 c inner join table2  cp on cp . CODE = c . ITEMTYPECODE where cp . B2CBLOCKED = '0' and c . ITEMNO like 'a00%'
       group by c . ITEMTYPECODE , cp . DESCRIPTION

    is equivalent to

       SELECT SUM ( INVENTORY ) as Total  FROM   table1 c inner join table2  cp on cp . CODE = c . ITEMTYPECODE where cp . B2CBLOCKED = '0' and c . ITEMNO like 'a00%'
       group by cp.CODE , cp . DESCRIPTION

    which can be reduced to

       SELECT SUM ( INVENTORY ) as Total  FROM   table1 c inner join table2  cp on cp . CODE = c . ITEMTYPECODE where cp . B2CBLOCKED = '0' and c . ITEMNO like 'a00%'
       group by cp.CODE

    (assuming CODE is your primary key). This means the whole query can be reduced to this:

       select sum (inventory) FROM   table1 c inner join table2  cp on cp . CODE = c . ITEMTYPECODE where cp . B2CBLOCKED = '0' and c . ITEMNO like 'a00%'

    which can be reproduced in LINQ as follows:

       int answer = db.Table1s.Where (c => c.ItemNo.StartsWith ("a00") && c.Table2.B2CBlocked == '0').Sum (c => c.Inventory);

    If this isn't what you're looking for, can you please say in more detail what you're trying to achieve - and in which table inventory is defined. Also, if you give more meangingful names than table1 and table2, it will help us relate better to your problem.

    Joe


    Write LINQ queries interactively - www.linqpad.net
    Thursday, June 4, 2009 4:55 AM
    Answerer
  • hello Joe Albahari,

    thanx for reply, but i actuall queary look like this

    in Inner queary i already converted into linq,  it's works fine, but i didn't find upper sum(Total) from inner level result.

    i want sum of total inventory from group of inventory.


    query Like :
    -------------

    select
    SUM(a.Total)

    from    

     


             SELECT SUM(INVENTORY) as Total  FROM  INVENTORY_Table  c
             
    inner join Invetorycode_table  cp on cp.CODE =  c.ITEMTYPECODE
             where cp.B2CBLOCKED = '0' and c.ITEMNO like 'a00%'
             group by c.ITEMTYPECODE,cp.DESCRIPTION

    ) 

    a
    thanxs.


    • Edited by Sanjay Patel Thursday, June 4, 2009 5:53 AM
    • Marked as answer by Sanjay Patel Saturday, June 6, 2009 9:11 AM
    • Unmarked as answer by Sanjay Patel Saturday, June 6, 2009 9:11 AM
    Thursday, June 4, 2009 5:15 AM
  • I think Joe's point is that if you are going to sum the sums, and that's all you are getting out of the querying, then the 'group by' serves no purpose and can be removed.  Once the group-by is removed, you don't need the outer layer of the query anymore, since the inner sum is done over the whole set.
    Wayward LINQ Lacky
    • Marked as answer by Sanjay Patel Saturday, June 6, 2009 9:11 AM
    Friday, June 5, 2009 4:23 PM
    Moderator


  • hello Matt warren,

    Thanks for reply.

    yes  you r right. i  don't need of "group by'.

    it's working fine.
    Saturday, June 6, 2009 9:11 AM