How does LINQ defer execution when in a using statement

Question

Imagine I have the following:

private IEnumerable MyFunc(parameter a)
{
   using(MyDataContext dc = new MyDataContext)
   {
      return dc.tablename.Select(row => row.parameter == a);
   }
}

private void UsingFunc()
{
   var result = MyFunc(new a());

   foreach(var row in result)
   {
      //Do something
   }
}

According to the documentation the linq execution will defer till I actual enumerate the result, which occurs in the line at the foreach. However the using statement should force the object to be collected reliably at the end of the call to MyFunct().

What actually happens, when will the disposer run and/or the result run?

Only thing I can think of is the deferred execution is computed at compile time, so the actual call is moved by the compiler to the first line of the foreach, causing the using to perform correctly, but not run until the foreach line? Is there a guru out there who can help?

EDIT: ~~NOTE: This code does work, I just don't understand how.~~

I did some reading and I realised in my code that I had called the ToList() extension method which of course enumerates the result. The ticked answer's behaviour is perfectly correct for the actual question answered.

Sorry for any confusion.

nice question. I have been bombed by this.. a Using(DataContext) blows up spectacularly — Gishu, Jan 19 '09 at 06:52
I get an ObjectDisposedException "DataContext accessed after Dispose." when I try this. Is your MyDataContext the autogenerated DataContext from the LINQ designer? Does it inherit from DataContext? — Cameron MacFarland, Jan 19 '09 at 07:44
I third the notion: if you dispose your datacontext by using "Using", you -will- get a runtime exception if you try to use the datacontext later. I know I've done it. — Amy B, Jan 19 '09 at 23:22
Apologies to all, see my updated question, you are perfectly correct. — Spence, Jan 20 '09 at 05:20

Marc Gravell · Accepted Answer · 2009-01-19T06:57:55.157

14

I would expect that to simply not work; the Select is deferred, so no data has been consumed at this point. However, since you have disposed the data-context (before leaving MyFunc), it will never be able to get data. A better option is to pass the data-context into the method, so that the consumer can choose the lifetime. Also, I would recommend returning IQueryable<T> so that the consumer can "compose" the result (i.e. add OrderBy / Skip / Take / Where etc, and have it impact the final query):

// this could also be an instance method on the data-context
internal static IQueryable<SomeType> MyFunc(
    this MyDataContext dc, parameter a)
{
   return dc.tablename.Where(row => row.parameter == a);
}

private void UsingFunc()
{
    using(MyDataContext dc = new MyDataContext()) {
       var result = dc.MyFunc(new a());

       foreach(var row in result)
       {
           //Do something
       }
    }
}

Update: if you (comments) don't want to defer execution (i.e. you don't want the caller dealing with the data-context), then you need to evaluate the results. You can do this by calling .ToList() or .ToArray() on the result to buffer the values.

private IEnumerable<SomeType> MyFunc(parameter a)
{
   using(MyDataContext dc = new MyDataContext)
   {
      // or ToList() etc
      return dc.tablename.Where(row => row.parameter == a).ToArray();
   }
}

If you want to keep it deferred in this case, then you need to use an "iterator block":

private IEnumerable<SomeType> MyFunc(parameter a)
{
   using(MyDataContext dc = new MyDataContext)
   {
      foreach(SomeType row in dc
          .tablename.Where(row => row.parameter == a))
      {
        yield return row;
      }
   }
}

This is now deferred without passing the data-context around.

edited Jan 19 '09 at 06:57

answered Jan 19 '09 at 06:22

Marc Gravell

1,026,079
266
2,566
2,900

I am trying to keep database operations out of business logic, so I am trying to not pass around the datacontext. All I want is lists, enumerable's and scalars from these functions. – Spence Jan 19 '09 at 06:45
Would that hit the database each time? Or will it do the query and then yield return each value? – Spence Jan 19 '09 at 07:03
It hits the DB once the first result is returned. – Cameron MacFarland Jan 19 '09 at 07:05
does it hit the db each yeild? or hit the db once, then yeild out the results holding them in memory? – Spence Jan 19 '09 at 08:40
It hits the db once per **caller's** "foreach" - then has a live connection *during* the caller's foreach; the iterator block guarantees that the data-context is closed (and thus connection closed) when the caller's foreach is exited. Nothing is buffered. – Marc Gravell Jan 19 '09 at 09:38
@Marc: Wouldn't the SQL be executed once, then buffered for each yield statement? – Cameron MacFarland Jan 19 '09 at 23:01
@Cameron - no, it really won't; it isn't all buffered; the live data-context/sql-connection is referenced by the compiler-generated state machine. One of the main tricks of LINQ is that it is fully deferred: as you "MoveNext()" (foreach), it reads data from the incoming TDS (SQL) stream. – Marc Gravell Jan 20 '09 at 06:27
Even if the DataReader is held for iterating over - the SQL is still executed once. – Amy B Jan 20 '09 at 17:40
If I call MyFunc(1).Take(1).ToList() and there's two rows, is the DataContext not disposed properly? – Amy B Oct 21 '10 at 18:27
@David - that is orthogonal to disposal; bit it wont affect it - simply only one record is consumed. The *iterators* are still all disposed properly, thanks (in part) to how "foreach" is defined. – Marc Gravell Oct 21 '10 at 18:36
I think there's a gap in my understanding of using/finally in these iterator methods. Thanks for helping me close it. – Amy B Oct 21 '10 at 18:42

dahlbyk · Answer 2 · 2023-06-16T14:03:03.913

9

I just posted another deferred-execution solution to this problem here, including this sample code:

IQueryable<MyType> MyFunc(string myValue)
{
    return from dc in new MyDataContext().Use()
           from row in dc.MyTable
           where row.MyField == myValue
           select row;
}

void UsingFunc()
{
    var result = MyFunc("MyValue").OrderBy(row => row.SortOrder);
    foreach(var row in result)
    {
        //Do something
    }
}

The Use() extension method essentially acts like a deferred using block:

public static IEnumerable<T> Use<T>(this T obj) where T : IDisposable
{
    try
    {
        yield return obj;
    }
    finally
    {
        if (obj != null)
            obj.Dispose();
    }
}

edited Jun 16 '23 at 14:03

answered Jul 23 '09 at 08:38

dahlbyk

75,175
8
100
122

I've already accepted the answer but that is a really nifty way of thinking. .Use() extension method. You deserve more than a +1 for an answer like that. – Spence Jul 23 '09 at 22:44
An additional website for those of you who are like what's "MyDataContext().Use()"?! https://lostechies.com/keithdahlby/2009/07/23/using-idisposables-with-linq/ – Meadock Aug 23 '19 at 15:16

How does LINQ defer execution when in a using statement

2 Answers2

Linked