Error: method 'First' can only be used as a final query operation

asp.net entity-framework linq-to-entities vb.net

Question

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.

1
0
3/16/2011 2:44:40 PM

Accepted Answer

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.

2
3/16/2011 2:52:51 PM

Popular Answer

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow