How to remove data context after use
I have a member class that returned an 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 am not deleting the data context after use. Because if I remove it, the code calling this method cannot use the IQueryable (possibly due to deferred execution?). How do I enforce this method? So I can get rid of the data context.
Thanks: D
a source to share
The example given by Codeka is correct and I would suggest writing code with this when the method is called by the presentation layer. Recycling the classes is a DataContext
bit tricky, however, so I would like to add something about that.
Domain objects created by LINQ to SQL (in your case a class TB_Countries
) often contain a reference to the class DataContext
. This internal link is required for lazy loading. When you access a list of referenced object instances (for example, for example:) TB_Country.States
, LINQ to SQL will query the database for you. This will also happen with lazy loaded columns.
When you post DataContext
, you won't let him use it again. So when you return a set of objects as you did in your example, it is not possible to call the property States
on the instance TB_Country
because it will throw ObjectDisposedException
.
This does not mean that you should not dispose DataContext
, because I believe that you should. How you should solve this depends a little on the architecture you choose, but IMO basically has two options:
Option 1. Put a DataContext
in the method GetCountriesQ
. You usually want to do this when your method is an internal method in your business layer and it is part of a larger (business) transaction. When you supply DataContext
externally, it is created outside the scope of the method and should not be disposed of. You can use it at a higher level. In this 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 domain objects from the method GetCountriesQ
. This solution is useful when the method is public in the business layer and will be called by the presentation layer. You can wrap data in a specially crafted object ( DTO ) that contains only data and no hidden references to DataContext
. Thus, you have complete control over the connection with the database, and you can dispose DataContext
as you should. I wrote more about it on SO here . In this 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 read here , there are some clever things you can do that make using DTOs less painful.
Hope this helps.
a source to share