Max(x=>x.Time) vs OrderByDescending(x=>x.Time).First().Time

I raised a related question about:

'Invalid column name [ColumnName]' on a nested linq query

I'm trying to get the top row of a group as asked here:

Linq - Top value from each group

However, I approached it different to the accepted answer using Max. I did it this way:

ATable
    .GroupBy (t => t.ID)
    .Select ( t => t.OrderByDescending(x=>x.Timestamp).FirstOrDefault().Timestamp)

This results in the error SqlException: Invalid column name 'ID'

Now when I use the Max function:

ATable
    .GroupBy (t => t.ID)
    .Select ( t => t.Max(x=>x.Timestamp))

It returns the list of times that I wanted.

My understanding is that they are different but equivalent calls. Why am I getting the sql error on the first call?

Update

The sql query generated for the first query looks like this:

SELECT (
    SELECT [t3].1676434127
    FROM (
        SELECT TOP 1 [t2].1676434127
        FROM [ATable] AS [t2]
        WHERE (([t1].[ID] IS NULL) AND ([t2].[ID] IS NULL)) OR (([t1].[ID] IS NOT NULL) AND ([t2].[ID] IS NOT NULL) AND ([t1].[ID] = [t2].[ID]))
        ORDER BY [t2].1676434127 DESC
        ) AS [t3]
    ) AS [value]
FROM (
    SELECT [t0].[ID]
    FROM [ATable] AS [t0]
    GROUP BY [t0].[ID]
    ) AS [t1]

I managed to reduce it down but keeping the same error:

SELECT (
    SELECT [t3].1676434127
    FROM (
        SELECT TOP 1 1676434127
        FROM [ATable]
        WHERE [t1].[ID] = [ID]
        ) AS [t3]
    ) AS [value]
FROM (
    SELECT [ID]
    FROM [ATable]
    GROUP BY [ID]
    ) AS [t1]

Update2

Some answers have said that these queries are logically different, however, if you do something like this on the northwind table, it will produce the same result:

OrderDetails
    .GroupBy(x=>x.ProductID)
    .Select(x=>x.OrderByDesce开发者_JAVA百科nding(y=>y.UnitPrice).FirstOrDefault().UnitPrice).Dump();

OrderDetails
    .GroupBy(x=>x.ProductID)
    .Select(x=>x.Max(y=>y.UnitPrice)).Dump();

So I cannot accept any of the 3 answers that say the queries are different or that Max is giving a Sum instead of the max value of a list or that you have to convert the IQueriable to a list before proceeding.

---

Update 2: I just want to respond to this:

however, if you do something like this on the northwind table, it will produce the same result [...] So I cannot accept any of the 3 answers that say the queries are different

I did not mean to dispute that they should produce the same result. To illustrate my point better, let me provide an analogy.

Let's say I give you a shuffled pile of index cards numbered from 1 to 50 and I say, "Put these all in order." So, you go through and sort them out, making sure the pile goes from 1 to 50. This takes you a minute or two. Then I say, "Now give me the card on the top of the pile." This card would happen to be the card numbered 50.

On the other hand, suppose in the first place all I said was, "Give me the card with the highest number from this pile." Then all you would do is go through the cards and find the one with the highest number, without needing to do any sorting whatsoever. (I mean, you could sort the pile first, but that would clearly be doing more than I asked.)

So what I'm saying is that the results of the above two sets of instructions would be the same; however, they are clearly different instructions.

Now, it would not surprise me at all if certain implementations were intelligent enough to translate these into identical (optimized) SQL queries; all I'm saying is that, since they are different instructions, it should not be so surprising if the resulting SQL is different (and though it may be unfortunate, it's therefore also not too surprising that one version could cause an error when the other doesn't).

---

Update: Again, I can't really give you a detailed response in terms of Linq-to-SQL; but I will point out that while Max should in theory produce the same result as OrderByDescending together with FirstOrDefault, it is not really the same from a conceptual standpoint.

Consider Linq-to-objects. Calling Max would iterate over the sequence and accumulate a maximum value. Calling OrderByDescending would sort the entire sequence and then take the top.

Obviously, Linq-to-SQL is another animal; however, it makes sense that since these queries represent conceptually different approaches to obtain a certain result, the SQL they produce could very well be (and, as you have seen, is) different.

---

Original Answer

I can't really give you a detailed response in terms of Linq-to-SQL, but I will point out that there is a very serious difference between your two alternatives (they are not equivalent):

Max(x => x.Timestamp)

The above should return the greatest timestamp.

OrderByDescending(x => x.Timestamp()).FirstOrDefault()

The above should return the record with the greatest timestamp.

These are very different beasts.