Web developers wiki ASP.NET Sitecore Sharepoint Kentico by Evident Interactive

LINQ SQL group join

Modified: 2008/08/20 09:55 by admin - Categorized as: Csharp, SQL Server


Edit

Code snippet to group and join using LINQ to SQL

Return all first with an empty Lat field, group them on the first 4 digits of the zipcode, and use only the first row in the group.

SQL:
from loc in Locations
where loc.Lat == null && loc.Zipcode.Substring(3, 1) != "0"
group loc by loc.Zipcode.Substring(0, 4) into g
join loc2 in Locations on g.Min(x => x.Zipcode) equals loc2.Zipcode
select loc2

This is the generated SQL statement:

SQL:
SELECT [t3].[Zipcode], [t3].[Lat], [t3].[Lon], [t3].[Alt]
FROM (
   SELECT MIN([t1].[Zipcode]) AS [value]
   FROM (
      SELECT SUBSTRING([t0].[Zipcode], 1, 4) AS [value], [t0].[Lat], [t0].[Zipcode]
      FROM [Location] AS [t0]
   ) AS [t1]
   WHERE [t1].[Lat] IS NOT NULL
   GROUP BY [t1].[value]
) AS [t2]
INNER JOIN [Location] AS [t3] ON [t2].[value] = [t3].[Zipcode]

 © Evident Interactive BV