Selecting the most recent entry using TimeStamp column

Question

I have a simple web page used for data entry (built with .NET 4.5 and EF5). Each time a user updates an entry, a new row is created with a TimeStamp value bigger than the previous row that was inserted (SQL Server guarantees that).

In the DB, the raw data looks like the following. In brief, the data mean that the TransactionNumber = 1 was added once and then updated two times.

+---------------+-------------------+-----------+--------------------+
| TransactionId | TransactionNumber |  Comment  |     TimeStamp      |
+---------------+-------------------+-----------+--------------------+
|             1 |                 1 | Bla       | 0x00000000000007D1 |
|             2 |                 1 | BlaBla    | 0x00000000000007D2 |
|             3 |                 1 | BlaBlaBla | 0x00000000000007D3 |
|             4 |                 2 | Hello     | 0x00000000000007D4 |
+---------------+-------------------+-----------+--------------------+

I'd like to write a Linq query that get only the latest value for each TransactionNumber. Considering the data above, my query would return the following result.

+---------------+-------------------+-----------+--------------------+
| TransactionId | TransactionNumber |  Comment  |     TimeStamp      |
+---------------+-------------------+-----------+--------------------+
|             3 |                 1 | BlaBlaBla | 0x00000000000007D3 |
|             4 |                 2 | Hello     | 0x00000000000007D4 |
+---------------+-------------------+-----------+--------------------+

The biggest issue I am facing so far is that the TimeStamp column is of type byte[] and I am not sure how to make the comparison of bytes possible. Any idea how to do this?

public class Transaction
{
    // ...

    [Timestamp]
    public Byte[] TimeStamp { get; set; }
}

U could convert the bytes to UInt64: BitConverter.ToUInt64(byteArray, 0), i don't know what type it is, but doesn't look like a double, if the values aren't correct, you should try to reorder the bytes (Big and Little Endian) — Jeroen van Langen, Aug 08 '13 at 21:07

Jozef Benikovský · Answer 1 · 2013-08-08T21:28:35.670

0

You can get transaction number with latest transaction ID using query below. You can still order by Timestamp column even if .NET alternative is byte[].

from transaction in db.Transactions
group transaction by transaction.TransactionNumber into g
select new
{
    TransactionNumber = g.Key,
    TransactionId = g.OrderByDescending(t => t.TimeStamp).Select(t => t.TransactionId).FirstOrDefault()
}

Then, you can select remaining columns by using result of this query.

edited Aug 08 '13 at 21:28

answered Aug 08 '13 at 21:21

Jozef Benikovský

1,121
10
9

Hum. That's going to be an expensive query. I was hopping I could do it in one pass. – Martin Aug 08 '13 at 21:25
I guess there could be only one query executed over DB as long as you use IQueryable variables. Let's say you store result of my query in variable IQueryable x. The you can get the result by db.Transactions.Where(t => x.Any(group => group.TransactionId == t.TransactionId)). – Jozef Benikovský Aug 08 '13 at 21:33

Selecting the most recent entry using TimeStamp column

1 Answers1