How to dispose data context after usage

Question

Hi fellow programmer

I have a member class that returned IQueryable from a data context

    public static IQueryable<TB_Country> GetCountriesQ()
    {
        IQueryable<TB_Country> country;

        Bn_Master_DataDataContext db = new Bn_Master_DataDataContext();

        country = db.TB_Countries
            .OrderBy(o => o.CountryName);

        return country;
    }

As you can see I don't delete the data context after usage. Because if I delete it, the code that call this method cannot use the IQueryable (perhaps because of deferred execution?). How to force immediate execution to this method? So I can dispose the data context..

Thank you :D

Answer 1

You can convert the queryable to a list, like so:

public static List<TB_Country> GetCountriesQ()
{
    using(var db = new Bn_Master_DataDataContext())
    {
        return db.TB_Countries
            .OrderBy(o => o.CountryName).ToList();
    }
}

Answer 2

The example given by Codeka is correct, and I would advice writing your code with this when the method is called by the presentation layer. However, disposing DataContext classes is a bit tricky, so I like to add something about this.

The domain objects generated by LINQ to SQL (in your case the TB_Countries class) often contain a reference to the DataContext class. This internal reference is needed for lazy loading. When you access for instance list of referenced objects (say for instance: TB_Country.States) LINQ to SQL will query the database for you. This will also happen with lazy loaded columns.

When you dispose the DataContext, you prevent it from being used again. Therefore, when you return a set of objects as you've done in your example, it is impossible to call the States property on a TB_Country instance, because it will throw a ObjectDisposedException.

This does not mean that you shouldn't dispose the DataContext, because I believe you should. How you should solve this depends a bit on the architecture you choose, but IMO you basically got two options:

Option 1. Supply a DataContext to the GetCountriesQ method. You normally want to do this when your method is an internal method in your business layer and it is part of a bigger (business) transaction. When you supply a DataContext from the outside, it is created outside of the scope of the method and it shouldn't dispose it. You can dispose it at a higher layer. In that situation your method basically looks like this:

public static IQueryable<TB_Country> GetCountriesQ(
    Bn_Master_DataDataContext db)
{
    return db.TB_Countries.OrderBy(o => o.CountryName);
}

Option 2. Don't return any domain objects from the GetCountriesQ method. This solution is useful when the method is a public in your business layer and will be called by the presentation layer. You can wrap the data in a specially crafted object (a DTO) that contains only data and no hidden references to the DataContext. This way you have full control over the communication with the database and you can dispose the DataContext as you should. I've written more about his on SO here. In that situation your method basically looks like this:

public static CountryDTO[] GetCountriesQ()
{
    using (var db = new Bn_Master_DataDataContext())
    {
        var countries;
            from country in db.TB_Countries
            orderby country.CountryName
            select new CountryDTO()
            {
                Name = country.CountryName,
                States = (
                    from state in country.States
                    order by state.Name
                    select state.Name).ToList();
            };

        return countries.ToArray();
    }
}

public class CountryDTO
{
    public string Name { get; set; }
    public List<StateDTO> States { get; set; }
}

As you will read here there are some smart things you can do that make using DTOs less painful.

I hope this helps.