I have the following query:
Dim roommates = From p In dbroom.Residents _ Where p.room = queryStudent.First.r.id _ Where p.building = queryStudent.First.b.id _ Where p.year = year _ Where p.semester = term _ Select p.person_name
It currently returns no results. I was getting an error when attempting to bind this resultset (since it was empty) to a repeater in ASP.NET, so I attempted to escape it:
If roommates.Count() = 0 Then Dim nomates As String = "No current roommates." rptrRoommates.DataSource = nomates rptrRoommates.DataBind() Else rptrRoommates.DataSource = roommates rptrRoommates.DataBind() End If
But I still get the same error:
The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead.
You can't use subquery this way. Either execute subqueries separately and use only received ids in the main query or rewrite the query to use joins.
In a LINQ to Entities query, you can use either FirstOrDefault() or SingleOrDefault as the final query operation.
var right = Context.Foos .Where(f => f.SomeBoolean) .FirstOrDefault(); // works var works = Context.Foos .Where(f => f.SomeBoolean) .SingleOrDefault(); // works
However, inside the query, you cannot use SingleOrDefault:
var wrong = Context.Foos .Where(f => f.SomeBoolean) .Select(f => f.Bars.SingleOrDefault()) .SingleOrDefault(); // Runtime error var right = Context.Foos .Where(f => f.SomeBoolean) .Select(f => f.Bars.FirstOrDefault()) .SingleOrDefault(); // works
Note that the final SingleOrDefault is always OK, but we had to change the one inside the query to FirstOrDefault. The reason for this is how SingleOrDefault is implemented. It tells the DB server to return the first two records and then throws an exception if more than one is returned. The LINQ to Entities query -- everything up until the final SingleOrDefault -- will be converted to SQL. That error behavior is not straightforward to express in a pure SQL query.
Ref : here