Simple linq question: How to compare in query with the last of antoher table

Question

Hi guys,

I want to compare a table coloum with the last entry of another (by foregn key given) table. And then fobit the entry, when it's the last.

So:

• Table 1 - Coloum userid - value: 1000
• Table 2 . Coloum userID - value: 1000 // Coloum rowID: 1
• Table 2 . Coloum userID - value: 2000 // Coloum rowID: 2
• Table 2 . Coloum userID - value: 1000 // Coloum rowID: 3

Should be: NO

So:

• Table 1 - Coloum userid - value: 1000
• Table 2 . Coloum userID - value: 1000 // Coloum rowID: 1
• Table 2 . Coloum userID - value: 2000 // Coloum rowID: 2

Should be: YES

My try:

(from a in dc.table1
where a.UserID != a.table2.Last().UserID)

But it don't work. Error is "The query operator 'Last' is not supported."

Answer 1

Last on a database query doesn't make sense unless you also have an order by:

a.table2.OrderBy(x => x.id).Last()

It might also perform better if you order by descending and use First instead, although I haven't tested it.