Perform Join and Group with Linq to SQL?

Question

Hey all. I'm not sure how I could express the following query in C# using Linq to SQL. Here is a short snippet of the pure SQL:

select t1.WholesalerID, t1.RetailerID,
    sum(t1.Col1) as 'Column 1',
    sum(t2.Col1) as 'Other Column 1',
    (sum(t1.Col1) - sum(t2.Col1)) as 'Column 1 Difference',
    sum(t1.Col2) as 'Column 2',
    sum(t2.Col2) as 'Other Column 2',
    (sum(t1.Col2) - sum(t2.Col2)) as 'Column 2 Difference'
    from Table1 t1
    inner join Table2 t2 on t1.WholesalerID = t2.WholesalerID
group by t1.WholesalerID, t1.RetailerID

Now, I've done Linq to SQL joins and group by's before, but I'm sure how to go about doing these together. I run into the problem when I'm attempting to sum the values from the joined tables. Thanks.

Sorry, this may be naive but why do you actually want to use LINQ if you already have a ready SQL query. It should be easy enough to just use it. See: codinghorror.com/blog/archives/001281.html — mfloryan
– mfloryan, Commented Jul 1, 2009 at 13:28
I would like to stick with Linq for the sheer fact that it is what I have been using all along, and if at all possible, I would like to not deviate from it. Thanks. — blparker
– blparker, Commented Jul 1, 2009 at 13:33
hm... so you would like to shoot yourself in the left foot, in the right foot or in both? — Remus Rusanu
– Remus Rusanu, Commented Jul 1, 2009 at 13:42
For the most part, the queries have been relatively trivial queries, so, it wasn't until I encountered this that I became a little stumped. — blparker
– blparker, Commented Jul 1, 2009 at 13:44
I've yet to be convinced of the point of Linq2SQL (Linq2Other may be great). Why learn a whole new syntax that doesn't seem to add any actual new functionality, especially when it's usually preferable to embed the complicated stuff in the database? — RolandTumble
– RolandTumble, Commented Jul 1, 2009 at 14:56

bruno conde · Accepted Answer · 2009-07-01 14:12:02Z

I've reached this solution: (didn't tested it though)

        var qry = from t in
                      (from t1 in Table1
                       join t2 in Table2 on t1.WholesalerID equals t2.WholesalerID
                       select new { t1, t2 })
                  group t by new { t.t1.WholesalerID, t.t1.RetailerID } into g
                  select new MyTypeWithDifferenceProp
                  {
                      WholesalerID = g.Key.WholesalerID,
                      RetailerID = g.Key.RetailerID,
                      Column1 = g.Sum(e => e.t1.Col1),
                      OtherColumn1 = g.Sum(e => e.t2.Col1),
                      Column2 = g.Sum(e => e.t1.Col2),
                      OtherColumn2 = g.Sum(e => e.t2.Col2),
                  };

This MyTypeWithDifferenceProp would have the Column1Difference and Column2Difference already defined.

Collectives™ on Stack Overflow

Perform Join and Group with Linq to SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related