Linq To SQL equivalent group by with multiple table columns in the output

I have just started on a project that uses Linq To SQL (there are various reasons why this is so, but for the moment, that is what is being used, not EF or ANOther ORM). I have been tasked with migrating old (and I'm talking VB6 here) legacy code. I come from a predominantly T-SQL background, so I knocked up a query that would do what I want, but I have to use LINQ to SQL (c# 3.5), which I don't have much experience with. Note that the database will be SQL Server 2008 R2 and/or SQL Azure

Here is the T-SQL (simplified)

SELECT TBS.ServiceAvailID, sum(Places) as TakenPlaces,MAX(SA.TakenPlaces)
FROM TourBookService TBS
JOIN TourBooking TB 
    ON TB.TourBookID=TBS.TourBookID 
JOIN ServiceAvail SA
    ON TBS.ServiceAvailID = SA.ServiceAvailID
WHERE TB.Status = 10
AND ServiceSvrCode='test' 
GROUP BY TBS.ServiceAvailID
HAVING sum(Places) <> MAX(SA.TakenPlaces)

So, there is a TourBooking table which has details of a customer's booking. This hangs off the TourBookService table which has details of the service they have booked. There is also a ServiceAvail table which links to the TourBookService table. Now, the sum of the Places should equal the Taken places amount in the ServiceAvail table, but sometimes this is not the case. This query gives back anything where this is not the case. I can create the Linq to just get the sum(places) details, but I am struggling to get the syntax to also get the TakenPlaces (note that this doesn't include the HAVING clause either)

var q = from tbs in TourBookServices
join tb in TourBookings on tbs.TourBookID equals tb.TourBookID
join sa in ServiceAvails on tbs.ServiceAvailID equals sa.ServiceAvailID
where (tb.Status == 10)
&& ( tbs.ServiceSvrCode =="test")
group tbs by tbs.ServiceAvailID
into g
select new {g.Key, TotalPlaces = g.Sum(p => p.Places)};

I need to somehow get the sa table into the group so that I can add g.Max(p=>p.PlacesTaken) to the select.

Am I trying to force T-SQL thinking into LINQ ? I could just have another query that gets all the appropriate details from the ServiceAvail table, then loop through both result sets and match on the key, which would be easy to do, but feels wrong (but that may just be me!) Any comments would be appreciated.

UPDATE: As per the accepted answer below, this is what Linqer gave me. I will have a play and see what SQL it actually creates.

from tbs in db.TourBookService
join sa in db.ServiceAvail on tbs.ServiceAvailID equals sa.ServiceAvailID
where
  tbs.TourBooking.Status == 10
  tbs.ServiceSvrCode == "test")
group new {tbs, sa} by new {
  tbs.ServiceAvailID
} into g
where   g.Sum(p => p.tbs.Places) != g.Max(p => p.sa.TakenPlaces)
select new {
  ServiceAvailID = (System.Int32?)g.Key.ServiceAvailID,
  TakenPlaces = (System.Int32?)g.Sum(p => p.tbs.Places),
  Column1 = (System.Int32?)g.Max(p => p.sa.TakenPlaces)
}

2012-04-04 00:17
by PabloInNZ

Are you able to use a micro orm like dapper or massive? That would be pretty easy for you to convert - Derek Beattie 2012-04-04 00:41

Unfortunately my hands are tied with Linq to SQL for the momen - PabloInNZ 2012-04-04 01:31

In your case I would try to use some kind of converter in my personal experience I used this program http://sqltolinq.com/ it often works very well in convertitng sql to linq.

2012-04-04 01:36
by COLD TOLD

Aha. I'd not come across that. Gives me a good place to start and get an idea of the linq and see what sql it produces. I'll add the output in another answer as it won't fit in comments. Many thank - PabloInNZ 2012-04-04 02:11