ORM – peril of the first time users

My team has recently got to grips with Linq-to-Sql and in the whole its all gone smoothly but there was one issue that the team had to overcome.

Our team is normally very good about data access, up to this point we have used stored procedures for all data access but only when needed. However, with Linq-to-sql that started to go out the window with calls to the database being made all over the code.

One of the worst examples that killed performance was calling the database to retrieve data for each record that is being displayed in a grid, now this is something that normally the team would have never done.

We had been using linq-to-objects for a while and so were used to working with objects in memory with no performance problems.  Now linq-to-sql syntax looks like linq-to-objects and you can believe you are simply calling a method on an object rather than the database and it is this reason that I believe we had the a problem, simply we forgot we were calling across the network to the database.

So the cautionary tale is this – when using an ORM don’t forget all your previous database knowledge and if you are not containing the data access to its own layer then be mindful of where you are using it.