Linq2Sql GroupBy

I’m currently working on a taxi-type system that manages journeys for customers. All journeys need authorised before they can be carried out. There is a page that shows all journeys that require authorisation but this required changing to show 1 row per customer rather than 1 row per journey.

I got some help for this and used the great LINQPad for constructing the query before porting it into my main project.

The Query

Below is the query that I used to get the data in the required format. There are 3 tables used: Journey, JourneyBooking (optional parent of Journey) and Customer.

from j in Journeys
group j by j.CustomerID into so
select new {
  so.Key,
  Name = (from j in Journeys
    where j.CustomerID == so.Key
    select string.Concat(j.Customer.Surname, ", ", j.Customer.Forename)).Take(1).Single(),
  OneOff = (from j in Journeys
    where j.CustomerID == so.Key && j.JourneyBooking != null
    select true).Count() > 0
}

Key Points

1. I make all further joins using ‘so.Key’ as this will return CustomerID because this is what we’ve grouped on.

2. from j in Journeys
   group j by j.CustomerID into so
   

   Use GroupBy() and group all journeys by CustomerID since we want 1 row per customer.

3. .Take(1).Single() Due to the GroupBy the customers’ name is returned as an IOrderedQueryable. Using [Take(1)](http://msdn.microsoft.com/en-us/library/bb503062.aspx) returns the top row from these results, which is fine as they’ll all be the same, and using [Single()](http://msdn.microsoft.com/en-us/library/system.linq.enumerable.single.aspx) returns a single String result.

4. OneOff = (from j in Journeys
   where j.CustomerID == so.Key && j.JourneyBooking == null
   select true).Count() > 0
   

This returns True in a StandingOrder column if the journey has no associated JourneyBooking record. Again this query returns an IOrderedQueryable object. I used the [Count()](http://msdn.microsoft.com/en-us/library/bb338038(v=VS.100).aspx) method to check if results are returned.

Update

After running some tests I have realised that using the Any() method is quicker than using

.Count() > 0 so the query would then become:

from j in Journeys
group j by j.CustomerID into so
select new {
  so.Key,
  Name = (from j in Journeys
    where j.CustomerID == so.Key
    select string.Concat(j.Customer.Surname, ", ", j.Customer.Forename)).Take(1).Single(),
  OneOff = (from j in Journeys
    where j.CustomerID == so.Key && j.JourneyBooking != null
    select true).Any()
}