Select MIN In LINQ

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: