Simple linq challenge: how to compare in query with the last of another table

Question

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

So:

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

Should be: NO

So:

• Table 1 - Column userid - value: 1000
• Table 2 . Column userID - value: 1000 // Column rowID: 1
• Table 2 . Column userID - value: 2000 // Column 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.