First, let's consider what is happening to you.
You are:
- Opening a data context.
- Getting some data back.
- Closing the data context.
- Trying to read the data you got back from the data context.
That scenario is designed to work. However, for that to always work, all data would have needed to be eager loaded.
By default, LinqToSql will lazy (aka defer) load things as it sees fit. In this case, it appears that Request.Branch or at least Request.Branch.Name is being lazy loaded. For something to lazy load properly, the data context must still be around, which in your scenario, it no longer is.
Here are some options to get your scenario working:
Option 1:
Somehow "force" Request.Branch.Name to be loaded before disposing your data context. That is possible with LinqToSql, although not necessarily as easy or convenient as you'd want it to be. This should allow you to continue using your "single data context per atomic operation" pattern, although there are other downsides with that approach. The data context enables important features around change tracking, caching, and other scenarios and while you want to dispose of the data context as soon as possible, there are significant advantages to keeping it open so you should be open minded in regards to keeping your data context open longer than you currently are keeping it open. One way to implement option 1 is to use DTOs as you allude to when you say "another Request class", but that may be overkill for you here in this scenario.
Option 2:
Open the data context when your user starts their action and dispose it when the user ends their action. This is called the UnitOfWork pattern. This allows lazy loading to work properly as well as giving you change tracking and other nice ORM features. It can be a little more challenging to implement, but it's the most likely to work in the most scenarios.
Tvanfosson's answer lies somewhere between Options 1 & 2 and there are plenty of middle ground opportunities to solve your problem. All ORMs have a learning curve and LinqToSql is no different. Most ORMs have something similar to LinqToSql's data context and switching away from LinqToSql won't absolve you from having to understand how and why the data context works as it does.
You never want to open a data context without disposing it. That is a resource leak and it's very bad.
I'm pretty sure the NerdDinner MVC sample application uses both LinqToSql and the repository pattern, but I can't remember if it implements UnitOfWork for data context management.
Best Answer
The first doesn't work because when the method returns the data context instantiated in the
using
block is disposed. However, theIEnumerable<BatchHeader>
returned is lazily evaluated and needs this data context to be alive to enumerate its results.You could do something like this:
The second block works because the query results are enumerated over and stored in memory before the data context is disposed of. After that happens, the data context isn't needed anymore. However, be careful when using code like your second block; if the
BatchHeaders
table is large you just pulled it all into memory.Now, and here is the most serious part of my answer: I absolutely can't stand seeing queries that instantiate data contexts to execute. I want to know and control when my data contexts are being used.