Why using(){} block is repeatedly called in a function containing yield return?

I have following code. The query take some 30 seconds to execute. I found that the top using block is called every time for each try to enumerate the iterator. Which mean query is executed for each call to MoveNext in enumerator.

So why lazy evaluation reintialize using over and over again. I know the work around but i need to understand the purpose of this.

   private IEnumerable<FriendProfile> GetProfiles() {
        var query = @"
            SELECT VirtualNumber, City, Gender, Nick, Age
            FROM Profiles
            WHERE Not VirtualNumber  In ( SELECT VirtualNumber FROM Messages)
            ORDER BY Id DESC;";

        using (var con = GetConnection()) {
            using (var cmd = dbconnection.CreateCommand()) {
                cmd.CommandText = query;
                var reader = cmd.ExecuteReader();
                while (reader.Read()) {
            开发者_如何转开发        var fp = new FriendProfile();
                    fp.VirtualNumber = reader.GetString(0);
                    fp.City = reader.GetString(1);
                    fp.Gender = reader.GetString(2);
                    fp.Nick = reader.GetString(3);
                    fp.Age = reader.GetInt16(4).ToString();
                    yield return fp;

                }
            }
        }
    }


    foreach(var fp in GetProfiles()){
    .... //foreach item using(){} in GetProfile() is reinitializes. All usings blocks
    }

I'm not 100% sure which using block you are referring to, but if I have understood correctly, what you have said should not be happening. The yield will return control of execution from the GetProfiles() method, but the code after the yield (ie. the end of the using blocks) will not be executed until the while condition is false.

Here is a simple example that should demonstrate this behaviour:

Using this class to show when the end of the using block executes:

public class Disposable : IDisposable
{
    private readonly string name;

    public Disposable(string name)
    {
        this.name = name;
    }

    public void Dispose()
    {
        Console.WriteLine("Disposing of {0}", name);
    }
}

and this code:

private IEnumerable<int> Test()
{
    using (new Disposable("outer"))
    {
        using (new Disposable("inner"))
        {
            for (int i = 0; i < 10; i++)
            {
                yield return i;
            }
        }
    }
}

...

foreach (int i in Test())
{
    Console.WriteLine("item {0}", i);
}

The output is:

item 0
item 1
item 2
item 3
item 4
item 5
item 6
item 7
item 8
item 9
disposing of inner
disposing of outer

This shows that the using blocks do not exit until the for loop exits.

It is because of the way yield gets compiled. It's actually an iterative call to a class created just to implement the collection defined by your GetProfiles() method. MoveNext() in that is called once per step through your foreach. This is a decent intro to what's going on behind the scenes.