LINQ TO SQL am I missing something obvious here?

Question

I have this method, that will be called against from a WCF Client, but for my testing, I'm uisng a local "add project reference." I'm getting the error that I cannot call the DataContext after it's disposed.

    public IEnumerable<Server> GetServers()
    {
        // initialze to null
        ServersDataContext sdc = null;

        try
        {
            // get connected
            using (sdc = GetDataContext())
            {
                // disable this deferred loading
                sdc.DeferredLoadingEnabled = false;

                var relations = from svr in sdc.Servers; // complex linq here

                // return
                return relations;
            }
        }
        catch (Exception ex)
        {
            LogError(ex, "fwizs.Internal");
            throw;
        }
        finally
        {
            if (sdc != null)
            {
                sdc.Dispose();
            }
        }
    }

And here is how I'm using the method, which gives this error: "Cannot access a disposed object."

    if (da.GetServers()
        .Select(sv => sv.ServerID == s.ServerID).Count() == 0)
    {

        // do work since we found it
    }

Using the .Select() method on this returned IEnumerable objects trys to run back to the database to make the select. After being serialized for WCF I don't think it'll be an issue, but I would like my local tests to work.

Answer 1

Materialize your query inside the method to ensure that the database call is actually performed before you dispose of the data context.

var relations = from svr in sdc.Servers;

return relations.ToList();

Answer 2

You already dispose your datacontext with the using So you can not dispose it another time in the finally

Answer 3

"using" keyword when used in that way is syntactic sugar for guaranteed disposal once its out of scope - which occurs just after "return relations".

Answer 4

LINQ is lazy, so the query is only defined here, not iterated trough.

So, what happens is that you define the query, then you close the datacontext. A short while after, you try to iterate trough, meaning that you try to query trough the SQL connection that has been closed previously.

From MSDN:

This method is implemented using deferred execution. The immediate return value is an object that stores all the information required to perform the action. The query represented by this method is not executed until the object is enumerated either by calling its GetEnumerator method directly or by using foreach in Visual C# or For Each in Visual Basic.

Answer 5

The DeferredLoadingEnabled property controls how relationships are loaded, not how the primary data is loaded.

You can simply use the ToList method to make sure that the data is loaded into memory:

return relations.ToList();

Answer 6

In addition to what others have said about using .ToList, this should also work, and with lazy execution:

public IEnumerable<Server> GetServers() {
    // initialze to null
    ServersDataContext sdc = null;
    try {
        // get connected
        using (sdc = GetDataContext()) {
            // disable this deferred loading
            sdc.DeferredLoadingEnabled = false;
            foreach (var relation in from svr in sdc.Servers) // complex linq here
                yield return relations;
        }
    }
    catch (Exception ex) {
        LogError(ex, "fwizs.Internal");
        throw;
    }
    finally {
        if (sdc != null) {
            sdc.Dispose();
        }
    }
}