Hey I like Linq…really I do!!

We’re trying to retrieve a distinct list of pay check dates from a table that contains multiple records per pay check…like so

employee_hour
employee_Id some_column some_other_column check_date
00001 foo bar 01/01/2010
00001 foo bar 01/01/2010
00001 foo bar 02/01/2010
00001 foo bar 02/01/2010

But sometimes it seems like you’ve got to go around your elbow to get to yer…..

In t-sql we would write:

select distinct check_date from employee_hour where employee_Id = anEmployeeId order by check_date desc

but in Linq, we came up with this:

var empHr = employee_hour.Where(z => z.employee_Id == anEmployeeId )
                         .Select(eh => eh)
                         .GroupBy(e =>e.check_date)
                         .Select(x => x.FirstOrDefault())
                         .ToList()
                         .OrderByDescending(y => y.check_date);

According to LinqPad, awesome product BTW, the sql emitted by Linq looks like this:

SELECT 
[Limit1].[employee_Id] AS [employee_Id], 
[Limit1].[some_column] AS [some_column], 
[Limit1].[some_other_column] AS [some_other_column], 
[Limit1].[check_date] AS [check_date]
FROM   (SELECT DISTINCT 
	[Extent1].[check_date] AS [check_date]
	FROM employee_hour AS [Extent1]
	WHERE N'anEmployeeId' = [Extent1].[employee_Id] ) AS [Distinct1]
OUTER APPLY  (SELECT TOP (1) 	
	[Extent2].[employee_Id] AS [employee_Id], 
	[Extent2].[some_column] AS [some_column], 
	[Extent2].[check_date] AS [check_date], 
	[Extent2].[some_other_column] AS [some_other_column]	
	FROM employee_hour AS [Extent2]
	WHERE (N'anEmployeeId' = [Extent2].[employee_Id]) AND (([Extent2].[check_date] = [Distinct1].[check_date]) OR (([Extent2].[check_date] IS NULL) AND ([Distinct1].[check_date] IS NULL))) ) AS [Limit1]

Bottom line…Linq’s not always easier…but the advantages of using Linq…especially the ability to query objects…still outweighs the disadvantages…despite it’s occasional verboseness.

Advertisements

About w4ik

software engineer...in the trenches
This entry was posted in ASP.Net, linq. Bookmark the permalink.

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