FirstorDefault() causes lazy loading or eager loading for linq to sql

Question

What is default behavior of FirstOrDefault() when used with Linq to SQL?

For e.g

  int value =   (from p in context.tableX         
                select p.Id).FirstOrDefault()      // Value will initialized here or

   if(value > 0)                      // query will be executed here????
   {
    //do something
   }

Thanks

Answer 1

What is default behavior of FirstOrDefault() when used with Linq to SQL?

It eagerly computes the result of the query. The easiest way to reckon about this is to realize that the return type is int, not IEnumerable<int> which can be deferred until GetEnumerator is called, but int has no such mechanism.

The phrasing of your question suggests that you're also asking if there is a way to change this behavior. There is, but not directly through FirstOrDefault or any mechanisms within LINQ. But you can defer using Lazy<T>. No compiler handy, so forgive me if this doesn't compile but it should get you very close.

Lazy<int> value = new Lazy<int>(
    () => {
        var query =
            from p in context.tableX
            select p.Id;
        var result = query.FirstOrDefault();
        return result;
    }
);

if(value.Value > 0) { // execution will be deferred until here
    //
}