none
Linq query for left outer join with group by clause. RRS feed

  • Question

  • Hello,
    can you give equivalent LINQ query for this SQL statement , provided T1 and T2 are tables in database are now are local Collection of types mapped to tables:

    SELECT T1.Col1 ,
    T1.Col2,
    T11.Col3,
    T2.COL1,
    SUM(CASE WHEN T2.YEAR = '2009'
    THEN 1
    ELSE 0 END) TL,      
    FROM  T1 LEFT OUTER JOIN
    T2
    ON T1.Col1 = T2.Col2   
    GROUP BY T.Col1,
    T.Col2,
    T.Col3

    Tuesday, October 16, 2012 10:38 AM

Answers

  • Try something like this:

    Dim q = From T1 In _db.T1
    Join T2 In _db.T2 On T1.Col1 Equals T2.Col2
    Group T1 By T1.Col1, T1.Col2, T1.Col3 Into Group
    Select Group

    • Marked as answer by Shyam Kr Monday, October 22, 2012 7:57 PM
    Wednesday, October 17, 2012 2:31 PM
  • Hi bootstrap2,

    There're something differenct between vb.net syntax and c# syntax.

    Please try to replace the "group" statement as below.

    Group T1 By New { T1.Col1, T1.Col2, T1.Col3 } Into Group

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Shyam Kr Monday, October 22, 2012 7:57 PM
    Monday, October 22, 2012 2:40 AM
    Moderator

All replies

  • Hi,

    is this not the right Forum for this question . Should i post it to a different Forum ?

    I am not clear about this .Can anyone at least suggest this ?

    Tuesday, October 16, 2012 7:04 PM
  • Try something like this:

    Dim q = From T1 In _db.T1
    Join T2 In _db.T2 On T1.Col1 Equals T2.Col2
    Group T1 By T1.Col1, T1.Col2, T1.Col3 Into Group
    Select Group

    • Marked as answer by Shyam Kr Monday, October 22, 2012 7:57 PM
    Wednesday, October 17, 2012 2:31 PM
  • Hi,

    thanks for your reply but what about the SUM function in my SQL query ? Where and how can i add in the LINQ query ?

    Wednesday, October 17, 2012 2:56 PM
  • My apologies. Try replacing the Select statement with:

    Select New With {Group, .TL = Group.Sum(Function(T2) If(T2.Year = "2009", 1, 0))}


    • Proposed as answer by Adam Lonsdale Friday, October 19, 2012 3:20 PM
    Wednesday, October 17, 2012 4:13 PM
  • Hi,
    can you provide the same code in C#.
    Because "Group T1 By T1.Col1, T1.Col2, T1.Col3 Into Group" this statement is giving error in C# .group T1 by takes only one data.As you have provided comma separated data, the same way i am doing then VS is gving error . May be C# has different syntax for the same.

    Thanks.

    Friday, October 19, 2012 3:53 PM
  • Hi bootstrap2,

    There're something differenct between vb.net syntax and c# syntax.

    Please try to replace the "group" statement as below.

    Group T1 By New { T1.Col1, T1.Col2, T1.Col3 } Into Group

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Shyam Kr Monday, October 22, 2012 7:57 PM
    Monday, October 22, 2012 2:40 AM
    Moderator