I've never really worked with C# before but I assume that query is about as efficient as a similar SQL statement?
Exactly.
LINQ allows you to use the same syntax for both local arrays and in an ORM, or any data source really, if you implement its IQueryable interface. You implementation can "parse" the AST and generate an SQL query, like a regular ORM.
He saw DB.Users.Where(a => a.Id == 1) on the screen and launched a tirade of "Are you retarded? You're downloading the whole Users table when you do that!"
It was fun watching our boss explain how LINQ worked and seeing the shame in the engineer's face.