How do you abstract out your persistence code when using LINQ to SQL?

Question

I love LINQ to SQL but it has been bugging me that in using it, my repository code becomes generated by the LINQ to SQL framework and hence tightly coupled to an SQL Server database.

Are any of you using LINQ to SQL in an abstracted, loosely coupled fashion and if so, how did you approach the problem of keeping your code database-independent?

Answer 1

You can use a similar approach like the one used in NerdsDinner. This project uses the dat a context as a gateway to the database and builds a repository around it.

This kind of repository pattern adds some conditions, filters, sorting command ... etc according to the called method and then returns an IQuerable to the caller leaving the door open for further modifications.

Actually you do almost the same way when building a repository around a NHibernate ISession.

If you decided to replace LinqtoSql with NHibernate for example, you have just to query a Linq session inside you repository instead of the datacontext one. Of course you will need although to complete your partial classes with the properties, which LinqtoSql adds automatically.

Answer 2

For myself; I'm content to re-use the object model that LINQ/dbml generates, since in reality the attributes don't hurt me, and any other implementation could provide a similar-enough model - but I don't use my data-context outside the DAL. So I have something like:

• IFooRepository - defines the methods available using both the generated dbml objects and some POCO model classes
• FooRepository - the implementation that knows about the data-context

My repository methods don't expose LINQ concepts like IQueryable<T> and Expression<...>, since they are leaky abstractions; other implementations will work differently (EF supports different aspects of both, for example).

Further - I've taken to marking most association properties as internal, and only using them during DAL queries (not so much during the OO work).

I could map to pure POCOs, but I don't see the benefit. I have some more thoughts on this here: Pragmatic LINQ.

Answer 3

Mouk above mentions the repository pattern in the NerdsDinner, there's also a tutorial of how to quickly set it up here:

http://www.asp.net/learn/mvc/tutorial-10-cs.aspx

This a good little series of tutorials that were released before the NerdsDinner. The concept here can be set up in a traditional asp.net web forms app as well.

Also, I would suggest using Linq To Entities (newer, and more support for other DBs) instead, some basics on that are here: http://www.asp.net/learn/mvc/tutorial-16-cs.aspx

Answer 4

Hey nathan, good question.

As others have said, the best thing to do is abstract all your GET/WRITE methods, and hide away the DataContext completely from your Business Logic.

My approach to this has been to write a DAL with a bunch of Generic methods which use reflection to do to whatever is necessary.

In these instances, once the method receives an object, say "Product", it can do whatever it wants internally independent of you ORM/Data Access technology. If you want it could literally just write a SQL string based on a few parameters, and reflection of the object.

HOWEVER, doing this alone won't completely de-couple you from LINQ to SQL.

The problem really are the entities themselves. Even if you abstract the method for retrieving data, you'll still be using those Entites all the way up your business logic.

I feel like, at the moment, thats just something I'm prepared to live with, because re-writing my own disconnected Entities seems like a bridge I'm not prepared to cross yet, just because it seems like a lot of unnecessary work...

I'd be very interested to see how others tackle this though.

cheers!