Sunday, March 2, 2008

Creating a Data Access Layer with LINQ to SQL

Here's an interesting article regarding the challenges of creating a true data access layer using LINQ to SQL.
It's an older article; however, it covers some interesting questions. I'd recommend in addition to reading the article actually reading through the responses as well at the end of the article.
"... it is absolutely not clear where a developer should draw the line between what’s business logic, and what belongs in the DAL ..."
It seems the primary concerns are what exactly the data access layer should be returning and where exactly the actual query should be executed.
"That is, the data access layer should be returning just arrays of entities (T[] or IEnumerable). It should [not] be returning IQueryable. Returning IQueryable gives you more rope to hang yourself with because (i) the caller (business logic layer) can redefine the query that ultimately gets sent to the database (ii) the caller (business logic layer) now executes the query because of deferred query execution (iii) the distinction between the caller (business logic layer) and the DAL blurs."
If you return IQueryable(Of T) objects from the data access layer the queries are not actually getting 'executed' until you use them later, for example when you enumerate over the IQueryable(Of T) data in your business or UI. So by returning List(Of T), IEnumerable(Of T) or an Array of entities you draw a fine line with what the data access layer handles vs. what the business layer or UI handles.
I've played around with returning IQueryable(Of T), List(Of T), IEnumerable(Of T) and an Array of entities. Each has their own advantages or caveats. My research so far leads me to believe that IEnumerable(Of T) is the preferred approach.
"The main design pattern that arises from LINQ is to prefer IEnumerable(Of T). This is because LINQ operates on IEnumerable(Of T), and if you design your application around IEnumerable(Of T), you will find many places where a LINQ query will provide an elegant solution to your problem."
Here's a couple articles that discuss IEnumerable(Of T) in more detail:
It will be interesting to see how each developer decides to implement LINQ into their projects and how consistent it will be with what others are doing.
"I hate it when developers have to make choices like that during routine development. Choosing takes time, and that’s not likely to improve productivity. But much worse is the fact that different developers will make different choices. Even a single developer may make different choices from one day to the next. That leads to inconsistencies in the code. Developers will spend more time trying to understand the code they’re reading, because it doesn’t always follow the same pattern. That’s bad for productivity. In the worst case scenario, developers start rewriting each other’s code, just so it matches their choice of the day. That kills productivity."
I think having a nice set of standards before fully integrating LINQ into a project is going to be a smart move, especially in team environments.