How do I return an IQueryable from Linq to SQL query when the dbContext is with a 'using' block?

Question

I've been coding with 'using' blocks but I am wondering if I can return an IQueryable from the following without the object being disposed before I access it.

public IQueryable<Contact> GetContacts(string clientID)
{
    using (dbDataContext db = new dbDataContext())
    {
        var contacts = from _contacts in db.Contacts
                        where _contacts.ClientID == clientID
                        orderby _contacts.LastName ascending
                        select _contacts;

        return contacts;
    }
}

Do I simply remove the 'using' block and let .Net manage the objects, or can I get Linq to run the query early and return the populated object.

Answer 1

If you don't expect to further compose the data (at the db-server), then:

return contacts.ToList().AsQueryable();

although in that case I'd prefer to return IEnumerable<Contact> or IList<Contact> to make the non-composable nature obvious. With the AsQueryable approach, it'll still be composable, but it'll compose via LINQ-to-Objects (so after it has fetched the records from the database).

If you do expect to further compose it, then you should pass the data-context (or, if possible, an upstream IQueryable<something>) into the method, and let the caller handle the lifetime:

public IQueryable<Contact> GetContacts(dbDataContext db, string clientID)
{
    return from _contacts in db.Contacts
           where _contacts.ClientID == clientID
           orderby _contacts.LastName ascending
           select _contacts;
}

Answer 2

you could do something like this

public IQueryable<Contact> GetContacts(string clientID)
{
    IQueryable contacts;
    using (dbDataContext db = new dbDataContext())
    {
        contacts = from _contacts in db.Contacts
                        where _contacts.ClientID == clientID
                        orderby _contacts.LastName ascending
                        select _contacts;


    }

    return contacts;
}

Answer 3

Hi IckleMonkey,

Can you make the context object a member instance of your class? If you can you'll defer the call to run the query until you actually touch the enumerator underlying the IQueryable instance you're returning. It depends on what you want to do. Do you need to return IQueryable from this method, or can you make do with IEnumerable ?

Answer 4

The contact object instance in IQueryable result set will retain the reference of datacontext used within the using block and will work in the client code much as expected. You will be able to perform deferred SQL operations on the resultant IQueryable instance and do other IQueryable operationas normally.