Optimize Linq to SQL query, Group By multiple fields

Question

My LINQ query contains the following Group By statement:

Group p By Key = New With { _
.Latitude = p.Address.GeoLocations.FirstOrDefault(Function(g) New String() {"ADDRESS", "POINT"}.Contains(g.Granularity)).Latitude, _
.Longitude = p.Address.GeoLocations.FirstOrDefault(Function(g) New String() {"ADDRESS", "POINT"}.Contains(g.Granularity)).Longitude}

The query works, but here is the SQL that the clause above produces

SELECT [t6].[Latitude]
            FROM (
                SELECT TOP (1) [t5].[Latitude]
                FROM [dbo].[GeoLocations] AS [t5]
                WHERE ([t5].[Granularity] IN (@p0, @p1)) AND ([t5].[AddressId] = [t2].[Addr_AddressId])
                ) AS [t6]
            ) AS [value], (
            SELECT [t8].[Longitude]
            FROM (
                SELECT TOP (1) [t7].[Longitude]
                FROM [dbo].[GeoLocations] AS [t7]
                WHERE ([t7].[Granularity] IN (@p2, @p3)) AND ([t7].[AddressId] = [t2].[Addr_AddressId])
                ) AS [t8]
            ) AS [value2]

I am not a SQL expert, but it looks to me that this is rather suboptimal translation. This should really be one query that selects Latitide and Longitude from the first record. Perhaps SQL Server Optimizer will take care of this. But is there a way to nudge Linq to generate a leaner SQL statement to begin with?

I tried the following, too..

Group p By Key = p.Address.GeoLocations.Where(Function(g) New String() {"ADDRESS", "POINT"}.Contains(g.Granularity)). _
Select(Function(g) New With {.Latitude = g.Latitude, .Longitude = g.Longitude}).FirstOrDefault

but this produced an error: "A group by expression can only contain non-constant scalars that are comparable by the server."

Answer 1

Sorry to reply in c#...

Here's what you have, translated to c#:

List<string> params = new List<string>()
{ "Address", "Point" };

from p in people
group p by new {
  Latitude = p.Address.GeoLocations
    .FirstOrDefault(g => params.Contains(g.Granularity)).Latitude,
  Longitude = p.Address.GeoLocations
    .FirstOrDefault(g => params.Contains(g.Granularity)).Longitude
};

Here's a rewrite, using the let keyword.

from p in people
let loc = p.Address.GeoLocations
   .FirstOrDefault(g => params.Contains(g.Granularity))
group p by new
{
  Latitude = loc.Latitude,
  Longitude = loc.Longitude
};