I have been doing SQL development for at least 15 years now but with the advent of Object Relational Mapping (ORM) frameworks like ADO.NET Entity Framework, I have had to write fewer complicated queries in recent years.
I have an application which is capturing data every 5 minutes and storing it in a table. The challenge was that for records that shared the same Access Code and Location Id, to retrieve the record with the greatest value in the Timestamp column. See figure 1 for the table definition and figure 2 for some sample data.
| ID | Data Type |
|---|---|
| ID | int |
| Timestamp | datetime |
| AccessCode | varchar(64) |
| LocationId | int |
| BandwidthAllocated | float |
| DownloadBandwidth | float |
| UploadBandwidth | float |
| Column Name | Timestamp | Access Code | Location Id | Bandwidth Allocated (Mb) | Download Bandwidth (Mb) | Upload Bandwidth (Mb) |
|---|---|---|---|---|---|---|
| 1 | 2012-08-17 10:54:18.433 | abcdef | 555 | 5 | 0.75 | 0.25 |
| 195 | 2012-08-17 10:59:04.930 | abcdef | 555 | 5 | 0.60 | 0.15 |
| 389 | 2012-08-17 11:04:06.920 | abcdef | 555 | 5 | 1.05 | 0.25 |
| 583 | 2012-08-17 11:09:09.713 | abcdef | 555 | 5 | 2.35 | 1.20 |
| 777 | 2012-08-17 11:14:02.340 | abcdef | 555 | 5 | 1.15 | 0.60 |
| 971 | 2012-08-17 11:19:00.140 | abcdef | 555 | 5 | 0.55 | 0.15 |
For a single record, this query is pretty straight forward. Just order the records by the Timestamp field in descending order and get the Top 1 record.
See the following t-sql code example:
SELECT TOP 1 * FROM AccessCodeUsage WHERE AccessCode = 'abcdef' AND LocationId = 12345 ORDER BY Timestamp DESC
T-SQL Single Record Example
However, to get the latest record for every Access Code / Location Id combination, it becomes a little more complicated. A quick search on the Internet yielded 2 solutions. The first solution uses a nested statement with the NOT EXISTS syntax to find a record that does not have any other record with a greater Timestamp. The second approach also uses a nested statement with the GROUP BY syntax by to get the MAX Timestamp for the Access Code / Location combinations and then retrieves the record for that Timestamp value.
See the following t-sql code example:
SELECT * FROM AccessCodeUsage a WHERE NOT EXISTS (SELECT 1 FROM AccessCodeUsage WHERE LocationId = a.LocationId AND Timestamp > a.Timestamp)
T-SQL Multiple Record Example 1
SELECT * FROM AccessCodeUsage a WHERE a.Timestamp = (SELECT MAX(Timestamp) FROM AccessCodeUsage WHERE a.LocationId = LocationId AND a.AccessCode = AccessCode GROUP BY LocationId, AccessCode)
T-SQL Multiple Record Example 2
Now that I have a couple of examples how to create my query in SQL, how to create the same query using ADO.NET Entity Framework object model? And specifically, using C# Lambda functions. The solution that I found is basically the same approach that is used in the first T-SQL example.
var last = AccessCodeUsages.Where(u1 => !mrm.AccessCodeUsages
.Any(u2 => u2.LocationId == u1.LocationId &&
u2.AccessCode == u1.AccessCode &&
u2.Timestamp > u1.Timestamp));
C# Lambda Function Example
I know that this example is not rocket science but it was something that had me stumped for a little while and hopefully it might help someone else out in the future.
