CODEWITHSUNDEEP
asp.netvb.netentity-frameworklinq-to-entities
Dave Mackey
Dave Mackey

Reputation: 4432

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

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.

Upvotes: 2

Views: 11133

Answers (2)

Aamol
Aamol

Reputation: 1199

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

Upvotes: 7

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364359

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.

Upvotes: 2

Related Questions