Select MIN In LINQ
June 28, 2011 Leave a comment
I recently had to take a set of swim time data for an entire season and determine the lowest time for each swimmer. The TSQL in me thought “this is a snap – just do this:”
Select SwimmerID, MIN(Time) from dbo.tblTimesIndividual where MeetID > 74 and RaceStrokeID = 4 Group By SwimmerID
However, when I tried to implement in LINQ, things went bad pretty quickly. I stumbled around with the language extensions for a bit before raising the white flag with MSDN. The easiest implementation that I found was this:
var minQuery = from times in entities.tblTimesIndividuals where times.MeetID > 74 && times.RaceStrokeID == 4 group times by times.SwimmerID into grouping select new { grouping.Key, LowestFreeStyleTime = from times2 in grouping where times2.Time == grouping.Min(times3 => times3.Time) select times2 };
Yikes! Since it worked, I kept it – however, I do wonder if there is an easier implementation. I wonder if there is a tool to reverse engineer TSQL to LINQ – like you can look at the TSQL generated from EF when you send using SQL Profiler (which I can’t use on my remote server) or the ToTraceString() method like so:
string sql = ((ObjectQuery)freeMinQuery).ToTraceString();
The key thing is to remember that ObjectQuery is in System.Data.Objects so I had to add a using statement for that. In any event, check out the TSQL:
SELECT [Project1].[SwimmerID] AS [SwimmerID], [Project1].[C1] AS [C1], [Project1].[TimesIndividualID] AS [TimesIndividualID], [Project1].[SwimmerID1] AS [SwimmerID1], [Project1].[MeetID] AS [MeetID], [Project1].[AgeGroupID] AS [AgeGroupID], [Project1].[RaceStrokeID] AS [RaceStrokeID], [Project1].[RaceTypeID] AS [RaceTypeID], [Project1].[RaceLengthID] AS [RaceLengthID], [Project1].[Lane] AS [Lane], [Project1].[Place] AS [Place], [Project1].[Time] AS [Time], [Project1].[TimerID] AS [TimerID] FROM ( SELECT [GroupBy1].[K1] AS [SwimmerID], [Extent2].[TimesIndividualID] AS [TimesIndividualID], [Extent2].[SwimmerID] AS [SwimmerID1], [Extent2].[MeetID] AS [MeetID], [Extent2].[AgeGroupID] AS [AgeGroupID], [Extent2].[RaceStrokeID] AS [RaceStrokeID], [Extent2].[RaceTypeID] AS [RaceTypeID], [Extent2].[RaceLengthID] AS [RaceLengthID], [Extent2].[Lane] AS [Lane], [Extent2].[Place] AS [Place], [Extent2].[Time] AS [Time], [Extent2].[TimerID] AS [TimerID], CASE WHEN ([Extent2].[TimesIndividualID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]FROM (SELECT [Extent1].[SwimmerID] AS [K1], MIN([Extent1].[Time]) AS [A1]FROM [dbo].[tblTimesIndividual] AS [Extent1]WHERE ([Extent1].[MeetID] > 74) AND (4 = [Extent1].[RaceStrokeID])GROUP BY [Extent1].[SwimmerID] ) AS [GroupBy1]LEFT OUTER JOIN [dbo].[tblTimesIndividual] AS [Extent2] ON ([Extent2].[MeetID] > 74) AND (4 = [Extent2].[RaceStrokeID]) AND ([GroupBy1].[K1] = [Extent2].[SwimmerID]) AND ([Extent2].[Time] = [GroupBy1].[A1])) AS [Project1]ORDER BY [Project1].[SwimmerID] ASC, [Project1].[C1] ASC
Yikes! I’ll continue to search for a tool that can give hints to make my LINQ more clear and have it generate better TSQL.
Jamie Dixon's Home 